I am trying to export Access 97 reports to a excel spreadsheet. The catch is I need to export more than 1 table to a excel spreadsheet. I know that using TransferSpreedsheet I can export multiple tables and queries to a excel spreadsheets, but how do I export reports? I have tried creating a Macro and using Output to, but that will create a seperate excel spreadsheet for every report.
I would never export a report to excel, you lose all your formating. What you will get will look like a query anyway, but probably not in the order you want.
So what you want to do is create one query with all the information you want included to export that. As far as the process on exporting you could use a macro to do so, or when view the query in datasheet view just click on File, Export adn specify the file type as excel
Yes I realize the formatting gets messed up, but thats acceptable. I have created a Macro that exports a query with everything in the report to Excel, BUT the report groups up the categories and totals up the catogries, and I cant do this in a query.
As stated in my original message I have already tried "Output to" action, it works, BUT it doenst allow me to export multiple reports to 1 excel file, it will only export each report to its only excel file.
K thanks for the idea, and its a good one, but it doenst solve my problem. Getting the queries into 1 excel file isnt my problem, I can easily do that with the "TransferSpreadsheet" action in a Macro. I need to get Reports into 1 excel file. My reports group by the fields and compute some calculations for me, so if I just using the query they are based on I lose that stuff.
So unless I just miunderstand your idea, it wont solve my problem.
Embedd all your 50 reports into One reports as sub reports. and Output this one report to excel
If you need each report on seperate sheet you could try (with VB) outputing each repoert seperately and with either VB in Access or Excel write a routine that combines all you individual excel worksheets into One master file (it is possible, but it has been a long time since I have done this)
Yeah thats what I ended up doing this morning. I just created another report with a bunch of subreports and added pagebreaks between the subreports. Then I just used a marco action "Output To" to export the report(s) to excel. Works great.