macro code to open a file dated yesterday

[Guest]

I need to code a macro that will open a file dated yesterday copy data and
paste it into todays file so I can compare changes from yesterday to today.
I save all the files with a date stamp, so I want to open only the most
recent file, but if it s a Monday, I need it to open Friday's data...any
suggestions?

 

[Guest]

The first thing that we would need to know is how do you express the date in
the file name? Is it yymmdd, yyyymmdd, mmddyyyy, ddmmyyyy? Personally I
would name them yyyymmdd so that they sort correctly. So assume the file is
called 20070905log.xls

Next where is the data that needs to be copied?

finally where does it need to go so you can compare it? A separate sheet or
a column on an existing sheet? Are the sheets named?

Answering these questions will get you on the way to a solutions. Try
recording what you want to do using the macro recorder for 1 day and then
post that code and we can help you generalize it.

Peter Richardson

 

[Guest]

The filesearch method should work.

Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.SearchSubFolders = True
.LastModified = msoLastModifiedYesterday
End With

 

[Guest]

Thank you for the quick reply...I am saving them as "filename 2007-9-8"...I
want to save them into today't file that has already been created by my macro
"filename 2007-9-9"...

The data is about 19000 row from colmuns A thru T and I want to paste it at
the bottom of todays data to run a pivot and compare yesterdays data to
today's...also, it is not a consistant # of rows, one day it might be 18000
raow, and the next it might be 20,000...

I will record the actions tomorrow when I am at the office and see what the
code looks like...

Thanks again for your help!

 

[Dave Peterson]

And if Friday is a holiday?

Or if the workbook isn't created for a week while you're on a business trip?

I'd just look through the last few days and look for the one that matches the
closest date.

Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim myDate As Date
Dim myPath As String
Dim myPfx As String

myPfx = "Filename " 'includes trailing space

myPath = "C:\my documents\excel\"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

Set wkbk = Nothing

'check about 100 dates
For myDate = Date - 1 To Date - 100 Step -1
On Error Resume Next
Set wkbk = Workbooks.Open(myPath & myPfx & _
Format(myDate, "yyyy-m-d") & ".xls")
On Error GoTo 0
If wkbk Is Nothing Then
'keep looking
Else
'found it
Exit For
End If
Next myDate

If wkbk Is Nothing Then
MsgBox "never found!"
Exit Sub
End If

'do the work against wkbk
wkbk.Worksheets("Someworksheetnamehere").Range("somerange").Copy _
ThisWorkbook.Worksheets("somesheet").Range("someotherrng")

wkbk.Close SaveChanges:=False

End Sub