Export Query Name

[AJOLSON]

I am exporting a query but want excel file name to stay the same but have a
date appended to the end. I would want the Date to change to reflect the
date exported. Here is what I have
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ExportQr", _
"\\bhihnbwfa01\Groups\HRR_WFA\ReportsEDM\CombinedDB\ArchivedWeeks\
Combined.XLS", , "Headcount"

How would I get this to save as Combined(currentdate).xls?

Thanks

 

[Daniel Pineault]

Try something like,

Dim sXLSDir As String
Dim sXLSFileName As String

sXLSDir = "\\bhihnbwfa01\Groups\HRR_WFA\ReportsEDM\CombinedDB\ArchivedWeeks\"
sXLSFileName = "Combined_" & Format(Date(), "yyyymmdd") & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ExportQr", _
sXLSDir & sXLSFileName, "Headcount"

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.