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.

 

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