TransferSpreadsheet code with date?

[TRob]

version: Access 97
I have placed a command button on a form for archiving
data that exports to the user's hard drive. Code is:

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel97, "qryArchive", "C:\CyberImport\Arc
hived.xls", True

Users are not techie savy, so it's difficult to explain
they'll need to change the name of the file on the hard
drive before clicking the command button again since it
will overwrite the original file. They will not be doing
this often and I will not be around to support this when
they want to do it again.

Is there any way to alter the code so that the current
date is stored in the filename? For example:

C:\CyberImport\Archived_061604.xls
C:\CyberImport\Archived_073104.xls
C:\CyberImport\Archived_093004.xls

I tried:
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel97, "qryArchive", "C:\CyberImport\Arc
hived&Now().xls", True

But, of course, all that did is create a file
named "C:\CyberImport\Archived&Now().xls". I know, I
know. I had to just try it.

Thanks!

 

[George Nicholson]

Try

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel97, "qryArchive",
"C:\CyberImport\Archived " & Format(Now(),"mm-dd-yy") & ".xls", True

returns: "Archived 06-16-04.xls"

My local date/time settings uses \ which is an illegal character in a
filename, so I couldn't use Now() without reformatting.

HTH,

 

[TRob]

IT WORKED!! Fantastic.
I'll be applying this method to numerous other things now!
Thanks a lot.