Export to excel (File Name issues)

[fpmsi]

I am looking for a way to modify this code to name the export file differently.



DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCombined_forexport", "export.xls"

I am looking for the export file to be export(date).xls

where (date) is the current date of export.


Or can i create a save as dialog box for the user to name the file themselves?

Thanks
Ryan.

[Robeen]

Try this:

 
Dim ExportFile as String
ExportFile = "export_" & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCombined_forexport", ExportFile

I like to put the yyyy first, because when you sort over a period of more than one year the files still stay in chronological order. Otherwise, they get sorted by whatever you have first. If mm is first - all your January files will be first . . .

I hope this helps!

[fpmsi]

Thank you that worked great but always goes to "my documents". Can I specify where it goes? I thought it was always going to the same folder the database was located in before.

Thanks

[Robeen]

Try some variation of this to get it to where you need it:

Dim MytPath, ExportFile as String
 
MyPath = "C:\MyFolder\"
ExportFile = Mypath & "export_" & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCombined_forexport", ExportFile

Let me know if that works.

[fpmsi]

Thanks. Because the database will be used by multiple people would like to not specify a folder and instead use the current db folder

Trying to incorporate this:
GetDBPath = CurrentProject.Path & "\"

so ExportFile would = GetDBPath & ExportFile

Not having any luck though.

[alansidman]

Here is what I use in a multi user environment where users may want to save the exported query to different locations. This line of code is used to place the file on the users desktop and then they can move it to where ever they wish.

MyPath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\"

Alan

[Robeen]

fpmsi,
Why does
GetPath = CurrentProject.Path & "\"
not work for you?

I just tried this:

 
MyPath = CurrentProject.Path & "\"
ExportFile = Mypath & "export_" & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCombined_forexport", ExportFile

and it did what I think you are attempting.

What happens when you try it?