Export snapshot of report to excel workbook multiple sheets

[cturner]

Here's my problem. I run a database every monday that has several (more than 15) reports as the outcome. Due to the massive amount of information we can't build the data up every week. We need to overwrite the tables and recreate the "Reports" every week. The department I am creating this for wants to keep a snapshot on the computer of the 15+ reports instead of printing them out. But instead of simply creating a snapshot of each individual report he would like to see them in the format of one file with multiple sheets. The one file would be labeled the date the reports were created and each individual sheet would be labeled the name of the report. For example, This past monday when we rant he Db the file would have been lbld 10-3-05, and one of the many sheets would have been labeled "Selects", or "Rejects" or "Cost" and so on. Does anyone have any suggestions?

 

[ghudson]

If you use the transfer spreadsheet method to output your data [query or table] you can have each output saved to a different worksheet as long as you output to the same file name. The sheet name will be based on the name of your query.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", "X:\Test\File.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query2", "X:\Test\File.xls", True