Accessing file using macro

Posted by Jonathan on March 06, 2001 2:56 PM

I am trying to access a file using a macro.
The folder is for the current month.
the file is named (example): Jonathan 03052001.xls and this file changes everyday to reflect yesterdays date.

How can I tell my macro to go and look for a for the current month folder and the file name with yesterdays date in that date format?

Posted by Carl B on March 06, 2001 10:41 PM

I am assumng you allready have what it takes to open files, you just need a way to open yesterdays file.
Have cell A1 = Now()-1
cell B1 = LEFT(TEXT(A6,"mm/dd/yyyy"),2)&MID(TEXT(A6,"mm/dd/yyyy"),4,2)&RIGHT(TEXT(A6,"mm/dd/yyyy"),4)
in macro concatenate filename to be opened with B1 cell reference.

Posted by Dave Hawley on March 06, 2001 11:37 PM

Hi Jonathan

Try this:

Sub OpenYesterday()
Dim YestDate As String
YestDate = Format(Date - 1, "ddmmyyyy") & ".xls"
Workbooks.Open Filename:="C:\My Documents\" & YestDate
End Sub

Any good ?

Dave

OzGrid Business Applications