Export Multiple Queries to Single Excel Sreadsheet with Current Date

Question

Hello all. I have an Access 2010 database with mutiple queries I would like to export to a single Excel Spreadsheet. I have accomplished this with the following code on an "Export Button" in a form:

Private Sub Command0_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryStaffReconDualFilled", "I:\Staffing\StaffingDatabaseExport.xls", True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryStaffReconFilledInactive", "I:\Staffing\StaffingDatabaseExport.xls", True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryStaffReconFilled", "I:\Staffing\StaffingDatabaseExport.xls", True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryStaffReconUnderFilled", "I:\Staffing\StaffingDatabaseExportTest.xls", True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryStaffReconVacant", "I:\Staffing\StaffingDatabaseExport.xls", True

End Sub

What I would like to do is add code to create a copy of the Excel file with the Current Date as opposed to overwriting the exisitng Excel file. So the Excel file name would be "StaffingDatabaseExport-[CurrentDate].xls".

Thank you in advance...

Answer 1

Command0 is not a great name; I renamed it to cmdExportToExcel.

Private Sub cmdExportToExcel_Click()

Const FileNameBase as string = "I:\Staffing\StaffingDatabaseExport-[CurrentDate].xls"

dim strFileName as string

strFileName = replace(FileNameBase, "[CurrentDate]", "Format$(Date(), "yyyymmdd")  'Or use whatever date format you like; this one sorts nicely alphabetically

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryStaffReconDualFilled", strFileName, True

'etc.

Answer 2

This works, but when I open the exported file, it gives a message that the file is a different type than the extention suggests. It will allow me to open it only after accepting the risks. I tried changing the extention to ".xlsx" but then I was not able to open the file at all.

Is there another spreadsheet type I should use?

Answer 3

I created this in a module to run at the end of my macro, which builds a table for export. But, the macro completes without the module running. It only opens the module in VBA and continues to the end.

What am I doing wrong?

Answer 4

Thank you Tom. I added what you stated here but uI get a Syntax Error. here is what I added...

Private Sub cmdExportToExcel_Click()

Const FileNameBase As String = "I:\Staffing\StaffingDatabaseExport-[CurrentDate].xls"

Dim strFileName As String

strFileName = replace(FileNameBase, "[CurrentDate]", "Format$(Date(), "yyyymmdd")

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryStaffReconDualFilled", strFileName, True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryStaffReconFilledInactive", strFileName, True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryStaffReconFilled", strFileName, True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryStaffReconUnderFilled", strFileName, True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryStaffReconVacant", strFileName, True

End Sub

Thank you again.

Answer 5

Hmmm, well...  not with TransferSpreadsheet.  Have a look at...

http://www.btabdevelopment.com/ts/default.aspx?PageId=10

This one might be the closest to what you need...

http://www.btabdevelopment.com/ts/default.aspx?PageId=110