Multiple reports in access being export at same time

I have a Access database that has a form in it that shows 6 reports. Form this form a user can print, view, or export to excel any of the 6 reports. However, the question is how do I set it up that the user can hit one button and have all 6 reports export to an excel spreadsheet and be different sheets within the same workbook?

I have attached the database to this question for viewing and help. The form in question is the Reports menu.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can simple call your export routines as needed. If the user selects to export all your files, then do something like this:

etc etc

As to different worksheets ... you would simply add a New worksheet to your Excel workbook, and then export your file directly to this. Sorry, I don't download items from here so can't be more explicit, but in general it's best if you actually do the coding yourself, so you'll understand what's going on and can fix problems in the future, if they occur.

Can you post an example of your Export codes? All 6 of them, if possible.
kejak28Author Commented:
I am sorry, I wasn't looking for someone to fix my database, I had jsut included it in case someone needed to see it to clarify what I needed. I need some help to export all the reports together on one excel spreadsheet as different pages. I understand that I could have the user export all then copy the sheets together, but I was hoping to save that step.

Here is the VBA code used on the report form to export the highlighted report:
Private Sub cmdExportToExcell_Click()
    On Error GoTo Err_cmdExportToExcell_Click

    Dim stDocName As String

    stDocName = Me.listReport
    DoCmd.OutputTo acReport, stDocName, acFormatXLS, , True

    Exit Sub

    MsgBox Err.Description
    Resume Exit_cmdExportToExcell_Click
End Sub
Jeffrey CoachmanMIS LiasonCommented:

First, I will as if you are aware that Access reports may not end up looking the way you think they should in Excel?
For this reason, most perople export the Report's underlying "RecordSource", not the Report itself.
This makes the code to do what you are asking very simple:
DoCmd.TransferSpreadsheet acExport,, "Table1OrQuery1Name", "C:\YourFolder\ExcelFile.xls", True, "Sheet1Name"
DoCmd.TransferSpreadsheet acExport,, "Table2OrQuery2Name", "C:\YourFolder\ExcelFile.xls", True, "Sheet2Name"
...Repeat for all six Reports

If you Must Export the Report itself, then things get fairly complex.
See this link:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

kejak28Author Commented:
This worked to get the different tables and such into excel as one workbook:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Tax", "c:\Master.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Spend", "c:\Master.xls", True

However, thanks for the help, but in the reports there are graphs that I was hoping would transfer and export to excel. Any thoughts on this?

kejak28Author Commented:
Sorry I meant this worked:
DoCmd.TransferSpreadsheet acExport,, "Table1OrQuery1Name", "C:\YourFolder\ExcelFile.xls", True, "Sheet1Name"
DoCmd.TransferSpreadsheet acExport,, "Table2OrQuery2Name", "C:\YourFolder\ExcelFile.xls", True, "Sheet2Name"

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A report in Access is an Access-specific object ... even though Excel almost certainly shares the same charting engine as Access, each platform would handle this differently. And as Jeff said, Excel won't be able to accurately render an Access report (i.e. the layout/formats, headings, footings etc).

It would seem to me that you'd have to build your worksheet and chart in Excel, and use data from Access (i.e. link your Access tables in your Excel worksheet) and then call this Excel worksheet from your Access code, if you wanted your users to view this data in Excel. However, if you simply want them to view the report, then just "print" it to PDF and be done with it.
kejak28Author Commented:
Would it be possible to have different pages show up in the pdf file as we have built the VBA to make different sheets with a single workbook? I am not very familiar with PDF files so that is why I am asking.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you have the necessary PDF software. Adobe can do this, but you'd have to insure that all end users have the full Adobe suite, and Adobe can be somewhat daunting to automate. There are plenty of other PDF utilities out there which are automatable; use your search engine to review them and see what you can come up with.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.