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?
Jeffrey CoachmanConnect With a Mentor MIS 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:

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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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 )Connect With a Mentor 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 )Connect With a Mentor 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.