Exporting date in filename Access macro

[JKWyo]

I have an access database that I want to export out to excel workbook with the date as part of the file name. Googled and searched for several hours and of the suggestions I see, I can't get any of them to work. Keep getting the Access can't save the output data to the file you've selected error, but none of the reasons are valid.

Here are the output file paths and date part combination that seems to be the most recommended on the web:

C:\Fixed Assets\2013 Spreadsheets\CAPSAO & Format(Date(),"yyyymmdd") & .xlsx

I've tried many variants of the above line with no luck, but if I take out the " " in the command, the file saves as "CAPSAO & Format(Date(), yyyymmdd) &"

C:\Fixed Assets\2013 Spreadsheets \CAPSAO & (Date(), yyyymmdd) & .xlsx

Any other suggestions?

Thanks for your time.

 

[Joe4]

You would need to do this in VBA (cannot do it directly in a Macro - you cannot have variables in a Macro).

You would build your filename variable like this:

[COLOR=#333333]myFileName = "C:\Fixed Assets\2013 Spreadsheets\CAPSAO "& Format(Date(),"yyyymmdd") & ".xlsx"[/COLOR]

Note you can convert your Macros to VBA pretty easily using the "Convert Macros to Visual Basic" button on the Macro Toolbar.

 

[JKWyo]

Thanks Joe. Completely over looked that little bit.