Open an Excel file with a date as today in a macro

P

Perry

Hello,

I'm creating a daily report with certain data.

One of the steps to create this report is the input of certain data
from one Excel file into another. The destination file for this data is
an Excel file which always has a name as follows:

---> Daily_Report_Perry_dd-mm-yyyy.xls

How do I create a macro which always opens the file above with the
"today" date? By example today, wednesday the 9th of November, the
following file should be opened:

---> Daily_Report_Perry_09-11-2005.xls
 
B

Bernie Deitrick

Perry,

When you use the file name, create it using the Format function:

Dim myFileName As String
myFileName = "Daily_Report_Perry_" & Format(Now, "dd-mm-yyyy") & ".xls"
MsgBox myFileName

Then use the variable myFileName wherever you were using the name string.

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

Perry,

I guess I should have included how to open the file:

Dim myFileName As String
myFileName = "Daily_Report_Perry_" & Format(Now, "dd-mm-yyyy") & ".xls"
Workbooks.Open "C:\Foldername\" & myFileName

HTH,
Bernie
MS Excel MVP
 
B

bpeltzer

Your open statement would be something like
Workbooks.Open Filename:="c:\temp\" & Format(Date, "mm-dd-yyyy") & ".xls"
 
P

Perry

Bernie,

Thank you! It works perfectly.

I'd already tried the "&Format(Now, "dd-mm-yyyy") & ".xls" function but
somehow it didn't work. Now it does!

I think it had to do with Dim myFileName As String.

Perry
 
Top