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

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

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


"Perry" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> 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
>
>
> "Perry" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

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


"Perry" wrote:

> 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
>
>

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