Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

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?

Check out our Excel VBA Resources

Re: Accessing file using macro

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.

Re: Accessing file using macro

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.