ExcelBanter - Finding Last Date Report

Before you go too far, you may want to look at opening the workbooks manually
and using a procedure written by Myrna Larson and Bill Manville.

You can find it at:

http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla

But if you want, I think that this does what you ask:

Option Explicit
Sub testme()

Dim myFile As String
Dim myPath As String
Dim prevWkbk As Workbook
Dim curWkbk As Workbook
Dim fDate As Date
Dim KeepThisFileName As String
Dim KeepThisDate As Date
Dim i As Long
Dim j As Long

'change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'Set curWkbk = ActiveWorkbook '?
'or
Set curWkbk = Workbooks("wip " & Format(Date, "dd-mm-yy") & ".xls")

'get the list of files
KeepThisFileName = ""
Do While myFile < ""
If LCase(myFile) Like "wip ##-##-##.xls" Then
fDate = DateSerial(2000 + Mid(myFile, 11, 2), _
Mid(myFile, 8, 2), _
Mid(myFile, 5, 2))
If fDate = Date Then
'skip today's file
Else
If KeepThisFileName = "" Then
KeepThisFileName = myFile
KeepThisDate = fDate
End If
If fDate KeepThisDate Then
KeepThisFileName = myFile
KeepThisDate = fDate
End If
End If
End If
myFile = Dir()
Loop

If KeepThisFileName = "" Then
MsgBox "no file found"
Exit Sub
End If

Set prevWkbk = Workbooks.Open(Filename:=myPath & KeepThisFileName)
For i = 1 To 100
For j = 1 To 100
If curWkbk.Worksheets("Jobs").Cells(i, j) _
< prevWkbk.Worksheets("Jobs").Cells(i, j) Then
'do what you will
Else
'do what you won't
End If
Next j
Next i

End Sub

=========
If you're really industrious, you could combine both routines.

teresa wrote:

Hi,

I save WIP Reports in a Hist Folder every few days,
So for example,my code will save today's report as
"WIP 16-12-04", it should then open the last WIP report
in the folder (e.g. WIP 13-12-04) and compare the 2 for changes,
I need code which will find and open the last report and insert the
workbook name in ******* below.

Thanks for any help

Sub desmond()
'Dim i, j
ThisWorkbook.SaveAs "c:\Hist\WIP " & Format(Date, "dd-mm-yy")
Worksheets.Add.Name = "Changes"
...........................................
For i = 1 To 100
For j = 1 To 100
If Worksheets("Jobs").Cells(i, j) <Workbooks("*****").
Worksheets("Jobs").Cells(i, j) Then ....................

--

Dave Peterson