Automate Exports

[leighms]

HI
Recently I set up a button to export a report automatically to Excel using the following code: DoCmd.outputTo acOutput Report, "Report1", acFormat.xls, "J:\Insurance Dept\Report1.xls", True
This works great, however I was wondering 2 things:
1. How do you set up to transfer multiple reports using the same button. EG I have reports named Report2, Report3, etc how do I add these to the code to export as well?
2. Is it possible to have them transfer to the one file but different sheets. EG Report1 would go to sheet1, Report2 to sheet 2, report3 to sheet 3 and so on?

Thanks for you help

 

[Pat Hartman]

You need an OutputTo for each report. If you output Report1 and then Report2 to the same .xls file, they should end up as separate tabs.

 

[leighms]

Hi
Thanks for your response, I have tried that but it doesn't work. I have found using Docmd. TransferSpreadsheet acExport works perfectly, but I have a number of reports that use date ranges, so I am also wondering if there is a way of entering the date range once

 

[Pat Hartman]

I always use TransferSpreadsheet and I assumed that OutputTo worked the same way. I guess there is a reason I never use OutputTo.

Change the queries for your reports to reference controls (they can be hidden) on a form. Then the TransferSpreadsheet will produce unique outputs based on the criteria on the form.