Opening file when file name is always different

[Lee Jeffery]

Using Excel 97 on WINNT

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

Next period, this report will be automatically saved as 570 Repor
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 i
there is a way to call the current report and the previous fortnightl
report using VBA as part of a macro? If so, during the comparison o
the two workbooks, I would need to differentiate between them by usin
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 tha
VBA can call the previous report without knowing its full name?

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

Can anyone help, please?

TIA.

Lee :

 

[Dave Peterson]

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.)

 

[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

 

[David McRitchie]

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.

 

[Lee Jeffery]

Dave P,

Thank you for the great response. I'll keep you posted as to how i
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 Australia
date format and those who access the reports manually from time to tim
would be very confused if I changed this mid-stream. I will keep you
suggestion in mind for my next new project to automate functions withi
my business area.

Thanks again, Guys!

Lee :

 

[larrydalooza]

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

Anything you can do manually, you can automate with AutoIt

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

The forum is magnificent for help...

Lar.

 

[Lee Jeffery]

Dave Peterson,

The calling of closed dated worksheets works like a dream but I am no
getting your suggestion for cell comparison right somewhere.
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 :

 

[Dave Peterson]

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]

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 chec
if the value on the same row in column D NextWks = value in column
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 repor
equal each other, this is not an exception and I don't need to keep i
- therefore: delete entire row.
This report tracks $$ owed by clients at the end of a 2 week period.
client with the same identifying number (e.g. 12345678) can owe $$ i
one fortnight which may reduce in the next as payment is made. This i
okay. I just want to know when this client first appears on the repor
i.e. column B nextWks <> column B PrevWks. Or if the existing clien
creates a new debt column B and column D NextWks <> column B and colum
D PrevWks. These debts have their own identifying code which is store
in column D. This code is usually something like 91R1. If a second o
subsequent debt occurs, this code will appear as 91R2, 91R3, etc. Thi
means the same client can appear on the report but with more than
debt and its identifying code.
These reports can be different in length each fortnight so I amende
the range to cover at least 100 rows more than any expected numbers.
The end result through deleting rows which appear on both worksheet
should should show me a listing in NextWks of new client numbers an
associated debts or existing client numbers where a new debt ha
occurred. I then intend to save this as a 570 Exception Report for th
period. PrevWks will be closed without saving any changes. This wil
leave both original reports with no changes and a new report will b
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 :

 

[Lee Jeffery]

Dave P,

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

Many thanks. All help greatly appreciated.

Lee :

 

[Dave Peterson]

First, I'd bet that my sample code blew up real good because I never set these
variables:

Dim NextWks As Worksheet
Dim PrevWks As Worksheet

You need something like:

set Newwks= worksheets("sheet1")
set prevwks = worksheets("sheet2")

And I had a couple of typos in the version that worked(?) from the top down:

if delrng is nothing then
set myrng = mycell
else
set myrng = union(mycell,delrng)
end if

Should read as:

if delrng is nothing then
set delrng = mycell
else
set delrng = union(mycell,delrng)
end if

But I don't think you can use matching up by row numbers to find the records to
delete--especially since the number of entries changes (but I could be wrong).
I'd look for the values in column B and compare them, then if they match, slide
of to column D and compare them again:

If that's true:

Option Explicit
Sub testme03()

Dim NextWks As Worksheet
Dim PrevWks As Worksheet
Dim res As Variant
Dim nextColB As Range
Dim prevColB As Range
Dim myCell As Range
Dim delRng As Range

'make these what you want
Set NextWks = Worksheets("sheet1")
Set PrevWks = Worksheets("sheet2")

With PrevWks
Set prevColB = .Range("b1", .Cells(.Rows.Count, "B").End(xlUp))
End With

With NextWks
Set nextColB = .Range("b:b") 'whole column
End With

For Each myCell In prevColB.Cells
res = Application.Match(myCell.Value, nextColB, 0)
If IsError(res) Then
'not found, do nothing
Else
If nextColB(res).Offset(0, 2).Value _
= myCell.Offset(0, 2).Value Then
'we have a double match
If delRng Is Nothing Then
Set delRng = myCell
Else
Set delRng = Union(myCell, delRng)
End If
End If
End If
Next myCell

If delRng Is Nothing Then
MsgBox "nothing to delete"
Else
delRng.EntireRow.Delete
End If

End Sub

 

[Lee Jeffery]

Thanks, Dave.

Although I'm close to babbling now, I'd finally worked out the Se
command but the results were leaving more info than I want. For testin
purposes, I have created 2 workbooks which are almost identical but
have made manual adjustments to ten entries and coloured these so the
should be the only ones left in a successful exception report. Thes
entries are appearing but so is a large amount of info that should b
deleted.
I'll try your latest suggestion and see if I can be successful wit
this.
Many thanks for your incredible patience with this.

Lee Jeffery :