[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Multiple reports in access being export at same time

Posted on 2008-11-17
Medium Priority
Last Modified: 2013-11-28
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.
Question by:kejak28
  • 4
  • 3
LVL 85
ID: 22983558
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.

Author Comment

ID: 22991618
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
LVL 74

Accepted Solution

Jeffrey Coachman earned 1200 total points
ID: 22991746

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:


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 22992887
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?


Author Comment

ID: 22992892
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"

LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 22993190
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.

Author Comment

ID: 23038661
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.
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 23040855
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.

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question