How do I export a query from Access to Excel?

How To Create a Microsoft Access Macro to Export Information to Excel or Word:

End-users occasionally find it easier to work with Microsoft Excel or Word than Access if they need to perform calculations or add formatting. They may receive the data in raw format; however, once it is organized in Microsoft Access, they may need to perform additional data analysis in Excel or insert formatting available in Word.

Table of Contents Show

How To Create a Microsoft Access Macro to Export Information to Excel or Word:
Microsoft Access Export specific Queries to Excel
Microsoft Access Export all Queries to other formats
Outbound References
Can you export a query in Access?
Can queries be imported from Access into Excel?
How do I export SQL query results to Excel automatically?

Prior to exporting the information, it is best to isolate it in a database query or a report. Create a Microsoft Access Select Query or a report to isolate the information. By isolating the information, it is easier to incorporate it into the macro. The macro that will be created below will have a message box that appears prior to the information export.

Click on the "Macros" option in the "Objects" menu.
Click "New" in the upper-panel of the database window to create a new macro.
Click in the "Action" field.
Click on the drop-down arrow and go to "MsgBox".
Go to the "Action Arguments" pane by clicking in the "Message" field or pressing "F6". To toggle to and from the "Action Arguments" pane, use "F6".
In the "Message" field, type the message that the end-user needs. A suggestion is to let the end-user know where the file will reside once it is exported.
For example, "The file will be exported to your desktop in Excel." In order to have room to type and view the entire message, right-click in the "Message" field and go to "Zoom". The "Zoom" dialog box will open and this will provide the opportunity to type the message.
The font can also be changed by clicking "Font…".
Click "Ok" once the message has been typed.
Click in the "Beep" field. If a beep is to sound when the message box is displayed, click "Yes". Click "No" if a beep is not needed.
Click in the "Type" field.
Click on the drop-down arrow. There are five types of messages that can be displayed. The difference between the message types is the icon that will be displayed with the message.
If "None" is selected, an icon will not be displayed.

Select "Critical" to display a red circle with an "x" beside the message.
"Warning?" will display a speech balloon with a question mark in the middle. "Warning!" will display a yellow triangle with an exclamation point in the middle.
Select "Information" to display a speech balloon with an "i" in the middle.
Click in the "Title" box. Use this to give the message a title, if desired. The title will appear in the title area of the dialog box. If a title is not given, "Microsoft Office Access" will be the title of the dialog box.
Click on the second line for the next action.
Click on the drop-down arrow to select "OutputTo".
Click in the "Object Type" field or press "F6".
Select "Query" to export query results. Any type of object can be exported from a table to a module. In this example, a query will be used.
Click in the "Object Name" field, select the name of the object that will be exported by clicking on the drop-down arrow. If "Query" is selected as the "Object Type", the "Object Name" field will display the queries in the database.
Click in the "Output Format" field. If the data is to be exported to Excel, select an Excel (*.xls) format. If it will be exported to Word, select "Rich Text Format (*.rtf)". The format that is selected at this stage will be ending format when it is exported.
Click in the "Output File" field.
Right-click in the "Output File" field and select "Zoom".
Type the path where the file will reside once it is exported, the filename, and the file extension. For example, c:\documents and settings\all users\desktop\contact list.xls.
Click on "Ok."
Click in the "Auto Start" field. Select "Yes" if the file is to open once it is exported. Select "No" to have the end-user open the file once it is exported.

Do not forget to save the macro. To preview the macro’s operation, click the exclamation point in the icon menu. To step through each action of the macro, select the icon to the right of the exclamation point (indented block paragraph with the arrow).

This Access tutorial explains how to export all Queries to Excel.

You may also want to read:

Access VBA auto generate mass report by group to Excel

Assume that you have created three Queries in Access database, all you want to export them to Excel.

Press ALT+F11 > insert a Module > paste the below code

Public Sub export_Excel()

  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef

  Set db = CurrentDb()
  For Each qdf In db.QueryDefs
    DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLSX, "C:\test\" & qdf.Name & ".xlsx", False
   ' Or use DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qdf.Name, "C:\test\" & qdf.Name, True
  Next qdf   Set qdf = Nothing   Set db = Nothing   End Sub

Go to C:\test\ folder, you will find three new files.

Instead of using DoCmd.OutputTo Method to export Query, you can also use DoCmd.TransferSpreadSheet Method.

DoCmd.OutputTo Method gives you formatting such as column width that you set in Query, while DoCmd.TransferSpreadSheet Method only gives a non-formatted raw data.

Microsoft Access Export specific Queries to Excel

Add an If condition in the For Each Loop to control which name should be or should not be exported using INSTR Function.

The below example only exports Query name that contains “2” (Query2)

Public Sub export_Excel()

  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef

  Set db = CurrentDb()
  For Each qdf In db.QueryDefs
    If InStr(qdf.Name, "2") <> 0 Then  'If query name contains 2
        DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLSX, "C:\test\" & qdf.Name & ".xlsx", False
    End If
  Next qdf
  Set qdf = Nothing
  Set db = Nothing
  
End Sub

Microsoft Access Export all Queries to other formats

DoCmd.OutputTo Method can be used to export some file types as below.

acFormatHTML

acFormatPDF

acFormatRTF

acFormatSNP

acFormatTXT

acFormatXLS

acFormatXLSB

acFormatXLSX

acFormatPS

Other than the parameter, make sure you change the file extension as well. The below example shows how to export all Queries to PDF.

Public Sub export_Excel()

  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef

  Set db = CurrentDb()
  For Each qdf In db.QueryDefs
    DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatPDF, "C:\test\" & qdf.Name & ".pdf", False
  Next qdf
  Set qdf = Nothing
  Set db = Nothing
  
End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

Can you export a query in Access?

Tip: You can also start the export process by right-clicking the object in the Navigation Pane and then clicking Export > Access. Access opens the Export - Access Database dialog box. In the File name box on the Export - Access Database dialog box, specify the name of the destination database and then click OK.

Can queries be imported from Access into Excel?

Go to Data tab, in Get & Transform group, click New Query > From Database > From Microsoft Access Database. Select your Access database and click Import.

How do I export SQL query results to Excel automatically?

Go to "Object Explorer", find the server database you want to export to Excel. Right-click on it and choose "Tasks" > "Export Data" to export table data in SQL. Then, the SQL Server Import and Export Wizard welcome window pop up.