Opening file when file name is always different

Using Excel 97 on WINNT

Each fortnight I receive a report which is then formatted and saved
using a dated filename for the next Wednesday e.g. "570 Report
28-07-2004".

Next period, this report will be automatically saved as 570 Report
11-08-2004". I use the code:
"G:\ER\570 report " & Format(date+4-weekday(date), "dd-mm-yyyy") &
".xls"
which was kindly supplied by Dave Peterson.

I am now trying to compare fortnightly reports and was wondering if
there is a way to call the current report and the previous fortnightly
report using VBA as part of a macro? If so, during the comparison of
the two workbooks, I would need to differentiate between them by using
code such as: CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("570 Report 28-07-2004.xls").Worksheets("Sheet1")
although this will only work for the named report. Is there a way that
VBA can call the previous report without knowing its full name?

These reports will only be called on the Monday or Tuesday immediately
prior to the Wednesday date appearing in the most recent file name and
will only want to call the current report and the previous fortnightly
report so there is no need to go any further back in time.

Can anyone help, please?

TIA.

Lee


---
Message posted from http://www.ExcelForum.com/

Well, if this portion worked ok to get the next report:
Format(date+4-weekday(date), "dd-mm-yyyy")

Then if we take that date and subtract 14 (fortnight is 14 days, right???), that
should work, too:

Format(date+4-weekday(date)-14, "dd-mm-yyyy")

Personally, I'd leave it that way. Kind of self-documenting code, but you could
simplify it to:
Format(date-10-weekday(date), "dd-mm-yyyy")
(just arithmetic)


And I'd set up a couple of worksheet variables and use them when I open the
workbooks.

dim NextWks as worksheet
dim PrevWks as worksheet

set nextwks = nothing
on error resume next
set nextwks = workbooks.open(filename:="G:\ER\570 report " & _
Format(date+4-weekday(date), "dd-mm-yyyy") & ".xls") _
.worksheets("sheet1")
on error goto 0
if nextwks is nothing then
msgbox "Next week's report isn't available"
exit sub
end if

'and the same thing for PrevWks
set Prevwks = nothing
on error resume next
set prevwks = workbooks.open(filename:="G:\ER\570 report " & _
Format(date+4-weekday(date)-14, "dd-mm-yyyy") & ".xls") _
.worksheets("sheet1")
on error goto 0
if Prevwks is nothing then
msgbox "Previous fortnight's report isn't available"
exit sub
end if

(watch out for typos--I composed in the post.)
"Lee Jeffery " wrote:

Using Excel 97 on WINNT

Each fortnight I receive a report which is then formatted and saved
using a dated filename for the next Wednesday e.g. "570 Report
28-07-2004".

Next period, this report will be automatically saved as 570 Report
11-08-2004". I use the code:
"G:\ER\570 report " & Format(date+4-weekday(date), "dd-mm-yyyy") &
".xls"
which was kindly supplied by Dave Peterson.

I am now trying to compare fortnightly reports and was wondering if
there is a way to call the current report and the previous fortnightly
report using VBA as part of a macro? If so, during the comparison of
the two workbooks, I would need to differentiate between them by using
code such as: CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("570 Report 28-07-2004.xls").Worksheets("Sheet1")
although this will only work for the named report. Is there a way that
VBA can call the previous report without knowing its full name?

These reports will only be called on the Monday or Tuesday immediately
prior to the Wednesday date appearing in the most recent file name and
will only want to call the current report and the previous fortnightly
report so there is no need to go any further back in time.

Can anyone help, please?

TIA.

Lee

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

And then if I wanted to compare cells, I could use:

if nextwks.range("a1").value = prevwks.range("a1").value then
'same
else
'different
end if

or even:

dim myCell as range
for each mycell in nextwks.range("a1:c9").cells
if mycell.value = prevwks.range(mycell.address).value then
'same
else
'different
end if
next mycell


Dave Peterson wrote:

Well, if this portion worked ok to get the next report:
Format(date+4-weekday(date), "dd-mm-yyyy")

Then if we take that date and subtract 14 (fortnight is 14 days, right???), that
should work, too:

Format(date+4-weekday(date)-14, "dd-mm-yyyy")

Personally, I'd leave it that way. Kind of self-documenting code, but you could
simplify it to:
Format(date-10-weekday(date), "dd-mm-yyyy")
(just arithmetic)

And I'd set up a couple of worksheet variables and use them when I open the
workbooks.

dim NextWks as worksheet
dim PrevWks as worksheet

set nextwks = nothing
on error resume next
set nextwks = workbooks.open(filename:="G:\ER\570 report " & _
Format(date+4-weekday(date), "dd-mm-yyyy") & ".xls") _
.worksheets("sheet1")
on error goto 0
if nextwks is nothing then
msgbox "Next week's report isn't available"
exit sub
end if

'and the same thing for PrevWks
set Prevwks = nothing
on error resume next
set prevwks = workbooks.open(filename:="G:\ER\570 report " & _
Format(date+4-weekday(date)-14, "dd-mm-yyyy") & ".xls") _
.worksheets("sheet1")
on error goto 0
if Prevwks is nothing then
msgbox "Previous fortnight's report isn't available"
exit sub
end if

(watch out for typos--I composed in the post.)
"Lee Jeffery " wrote:

Using Excel 97 on WINNT

Each fortnight I receive a report which is then formatted and saved
using a dated filename for the next Wednesday e.g. "570 Report
28-07-2004".

Next period, this report will be automatically saved as 570 Report
11-08-2004". I use the code:
"G:\ER\570 report " & Format(date+4-weekday(date), "dd-mm-yyyy") &
".xls"
which was kindly supplied by Dave Peterson.

I am now trying to compare fortnightly reports and was wondering if
there is a way to call the current report and the previous fortnightly
report using VBA as part of a macro? If so, during the comparison of
the two workbooks, I would need to differentiate between them by using
code such as: CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("570 Report 28-07-2004.xls").Worksheets("Sheet1")
although this will only work for the named report. Is there a way that
VBA can call the previous report without knowing its full name?

These reports will only be called on the Monday or Tuesday immediately
prior to the Wednesday date appearing in the most recent file name and
will only want to call the current report and the previous fortnightly
report so there is no need to go any further back in time.

Can anyone help, please?

TIA.

Lee

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson


--

Dave Peterson

Hi Lee,
I really would suggest that you use 2004-04-07
in the naming instead of 07-04-2004. That is
yyyy-mm-dd instead of dd-mm-yyyy. So that you
can put the filenames in a meaningful alphabetical order,
same if you were naming worksheet names based on dates.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Dave P,

Thank you for the great response. I'll keep you posted as to how it
works as soon as I get a chance to try it.

David Mc,

Many thanks for your suggestion on the naming convention. However,
there is an existing history of these reports using this Australian
date format and those who access the reports manually from time to time
would be very confused if I changed this mid-stream. I will keep your
suggestion in mind for my next new project to automate functions within
my business area.

Thanks again, Guys!

Lee


---
Message posted from http://www.ExcelForum.com/

Lee Jeffery wrote in message ...
Dave P,

Thank you for the great response. I'll keep you posted as to how it
works as soon as I get a chance to try it.

David Mc,

Many thanks for your suggestion on the naming convention. However,
there is an existing history of these reports using this Australian
date format and those who access the reports manually from time to time
would be very confused if I changed this mid-stream. I will keep your
suggestion in mind for my next new project to automate functions within
my business area.

Thanks again, Guys!

Lee


---
Message posted from http://www.ExcelForum.com/

Anything you can do manually, you can automate with AutoIt

http://www.autoitscript.com/autoit3/

The forum is magnificent for help...

Lar.

Dave Peterson,

The calling of closed dated worksheets works like a dream but I am not
getting your suggestion for cell comparison right somewhere. I
attempted to use:
Dim NextWks As Worksheet
Dim PrevWks As Worksheet
Dim myCell As Range
For Each myCell In NextWks.Range("B2500").Cells
If myCell.Value = PrevWks.Range(myCell.Address).Value Then
Selection.EntireRow.Delete
Else
'different
End If
Next myCell

but I get a run-time 91 error on line:
For Each myCell In NextWks.Range("B2500").Cells

I have tried substituting .Value for .Cells but get the same error.
Any ideas where I'm going wrong, please?

Lee


---
Message posted from http://www.ExcelForum.com/

The workbooks with the worksheets to compare have to still be open.

Dim NextWks As Worksheet
Dim PrevWks As Worksheet
Dim myCell As Range
For Each myCell In NextWks.Range("B2500").Cells
If myCell.Value = PrevWks.Range(myCell.Address).Value Then
mycell.EntireRow.Delete
'or
prevwks.range(mycell.address).entirerow.delete
Else
'different
End If
Next myCell

But this is gonna cause trouble. Once you delete that row, your addresses won't
match up anymore.

You could either build a range to delete:

Dim NextWks As Worksheet
Dim PrevWks As Worksheet
dim delRng as range
Dim myCell As Range
For Each myCell In NextWks.Range("B2500").Cells
If myCell.Value = PrevWks.Range(myCell.Address).Value Then
if delrng is nothing then
set myrng = mycell
else
set myrng = union(mycell,delrng)
end if
Else
'different
End If
Next myCell

if delrng is nothing then
msgbox "nothing to delete
else
delrng.entirerow.delete
end if

===========
Just a curiosity question:

You're deleting the entire row if any of the cells in B match?

You could start at the bottom and work your way up.

Dim NextWks As Worksheet
Dim PrevWks As Worksheet
dim iRow as long
dim myCell as range
for irow = 500 to 2 step -1
for each mycell in nextwks.cells(irow,"B").resize(1,3).cells
if mycell.value = prevwks.range(mycell.address).value then
mycell.entirerow.delete
exit for 'stop looking at that row!
end if
next mycell
next irow

You could actually start at the bottom and work your way down if you create that
delRng and delete the whole mess when you're done.



"Lee Jeffery " wrote:

Dave Peterson,

The calling of closed dated worksheets works like a dream but I am not
getting your suggestion for cell comparison right somewhere. I
attempted to use:
Dim NextWks As Worksheet
Dim PrevWks As Worksheet
Dim myCell As Range
For Each myCell In NextWks.Range("B2500").Cells
If myCell.Value = PrevWks.Range(myCell.Address).Value Then
Selection.EntireRow.Delete
Else
'different
End If
Next myCell

but I get a run-time 91 error on line:
For Each myCell In NextWks.Range("B2500").Cells

I have tried substituting .Value for .Cells but get the same error.
Any ideas where I'm going wrong, please?

Lee

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Hi Dave P,

Thanks for getting back to me. My brain stopped working some time ago -
I need either more sleep or more coffee ...
In relation to your queston about deleting rows, my scenario is this:
If a value in column B NextWks = value in column B PrevWks, then check
if the value on the same row in column D NextWks = value in column D
PrevWks. If this is true then delete entire row from NextWks only.
I am looking for exceptions to keep and if these values on each report
equal each other, this is not an exception and I don't need to keep it
- therefo delete entire row.
This report tracks $$ owed by clients at the end of a 2 week period. A
client with the same identifying number (e.g. 12345678) can owe $$ in
one fortnight which may reduce in the next as payment is made. This is
okay. I just want to know when this client first appears on the report
i.e. column B nextWks column B PrevWks. Or if the existing client
creates a new debt column B and column D NextWks column B and column
D PrevWks. These debts have their own identifying code which is stored
in column D. This code is usually something like 91R1. If a second or
subsequent debt occurs, this code will appear as 91R2, 91R3, etc. This
means the same client can appear on the report but with more than 1
debt and its identifying code.
These reports can be different in length each fortnight so I amended
the range to cover at least 100 rows more than any expected numbers.
The end result through deleting rows which appear on both worksheets
should should show me a listing in NextWks of new client numbers and
associated debts or existing client numbers where a new debt has
occurred. I then intend to save this as a 570 Exception Report for the
period. PrevWks will be closed without saving any changes. This will
leave both original reports with no changes and a new report will be
created for the exceptions.
I'll work on your latest suggestion. If you have any further thoughts,
I would love to hear them.

Many thanks.

Lee Jeffery


---
Message posted from http://www.ExcelForum.com/

Dave P,

I'm doing something very, very wrong with your code.
Each one of your suggestions gives me a run-time error 91: Object
variable or With block variable not set on the For Each line. I've
tried to use the set command but still no success. Which variable am I
missing and how should this be set, please?

Many thanks. All help greatly appreciated.

Lee


---
Message posted from http://www.ExcelForum.com/