Export from Access Querry to Excel

Posted on 2014-08-19
Last Modified: 2014-09-04

I have a form with a button on it and I also have 3 Queries.  What I would like to have happen is—when the button is clicked, all 3 Queries get exported to 3 separate Excel Spreadsheets, as follows:

Querry1 goes to Spreadsheet1 located at C: /Matt/User/Desktop/Spreadsheet1
Querry2 goes to Spreadsheet2 C: /Matt/User/Desktop/Spreadsheet2
Querry3 goes to Spreadsheet3 C: /Matt/User/Desktop/Spreadsheet3

What I would like to happen is, when the button is clicked—each of these Queries get exported to their respective Spreadsheet.

Note:  I would like to overwrite any data within those spreadsheets every time an export is performed without receiving a Warning Dialog Box.

Also, once the export is complete, I would like the Access Database to display an 'Ok' Dialog Box that simply says "Report Has Been Exported"

Does anyone have a code I can place within my button?
Question by:mdstalla
    LVL 19

    Accepted Solution

    Try this ...

        Dim strFileName as String
        strFileName = "C: /Matt/User/Desktop/Spreadsheet1.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Querry1", strFileName, True
        strFileName = "C: /Matt/User/Desktop/Spreadsheet2"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Querry2", strFileName, True
        strFileName = "C: /Matt/User/Desktop/Spreadsheet3"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Querry3", strFileName, True
     MsgBox "Report Has Been Exported"


    Author Comment


    The code is producing an error.

    It has a problem with:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Querry1", strFileName, True


     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Querry2", strFileName, True

    I don't know if it makes a difference but I'm using Access and Excel 2013


    The spreadsheets are all .xlsx
    LVL 48

    Expert Comment

    by:Gustav Brock
    Try with:

        Const cstrFileName As String = "C: /Matt/User/Desktop/Spreadsheet{0}.xlsx"
        Const cstrQuery As String = "Querry{0}"

        Dim strFileName As String
        Dim strQuery As String
        Dim intFile As Integer

        For intFile = 1 To 3
             strFileName = Replace(cstrFileName, "{0}", CStr(intFile))
             strQuery = Replace(cstrQuery, "{0}", CStr(intFile))
             DoCmd.OutputTo acOutputQuery, strQuery, acFormatXlsx, strFileName, False, , , acExportQualityPrint
        MsgBox "Exported three reports.", vbInformation, "Export"

    LVL 19

    Expert Comment

    by:Eric Sherman
    What error are you getting??

    LVL 7

    Expert Comment

    Maybe "acSpreadsheetTypeExcel12"?

    Author Closing Comment

    Your code worked great.  Thanks E!
    LVL 19

    Expert Comment

    by:Eric Sherman
    Thanks for the points.


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    755 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

    25 Experts available now in Live!

    Get 1:1 Help Now