Microsoft Access Export all Queries to Excel

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

Microsoft Access Export all Queries to Excel

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

Microsoft Access Export all Queries to Excel and Txt 01

 

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.

Microsoft Access Export all Queries to Excel and Txt 02

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

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *