Using Access to add datestamp to an Excel template

[Guest]

I am using a macro to export data into an Excel template for a daily report,
and the users have asked if there's any way to add a date to the end of the
xls filename. Is there anyway I can use access to add a datestamp to the
Excel file?

 

[Ken Snell \(MVP\)]

Use an expression for the File Name argument
in the macro action (include the = sign in the expression string for the
argument):

="C:\MyFolder\MyFileName" & Format(Date(), "mmddyyyy") & ".xls"

 

[Guest]

Thanks for your reply. This expression creates a brand new Excel file, but
I'm looking to use the same Excel template that sits in this directory.

 

[Ken Snell \(MVP\)]

If you want to name the file name during the export, then you cannot use an
existing EXCEL template and change its filename using a TransferSpreadsheet
action in a macro.

You'd have to do the export via TransferSpreadsheet the way you've done it
before (or you can do this in VBA code too), and then you'll need to use VBA
code to rename the resulting file (or copy the resulting file to a new file
with your desired filename).