Auto save date in Excel file

[TallMan]

Hey Guys,

I am trying to write code which transfers a table into an excel file and then saves it on my departments share drive with a name and date.

A file save name example would be:

TesT_File 12-6-2010.xls

I am not sure how to do this.

I have tried

Dim dt as string 
dt = date 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_AOtmp", "S:\tallman\Test_File '" & dt & "'", True

Any ideas?

 

[John Big Booty]

Try;

Dim dt as string 
dt = Format(Date, "dd-mm-yyyy") 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_AOtmp", "S:\tallman\Test_File " & dt , True

 

[TallMan]

ah ha! Perfect! Thank you !