Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Last Date Report
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 .................... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
report date - date rec.ved=days late, but how to rid dates complet | Excel Worksheet Functions | |||
Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
finding a date/time in a list that is closest to an existing date/ | Excel Discussion (Misc queries) | |||
Finding the Monday date based on a different date in same week | Excel Worksheet Functions | |||
Finding last Date Report | Excel Programming |