Multiple reports in access being export at same time

Posted on 2008-11-17
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
    LVL 84
    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

    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


    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:


    Author Comment

    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

    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 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    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

    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 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now