Access VBA Reports – Print, Export, Filter

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on July 9, 2022

This Access VBA Tutorial will discuss how to interact with Access Reports using VBA.

Print Report

This VBA function will print an Access report:

Public Function Print_Report(ReportName As String)
    On Error GoTo SubError
    DoCmd.OpenReport ReportName, acViewPreview, , , , acHiden
    DoCmd.SelectObject acReport, ReportName
    DoCmd.RunCommand acCmdPrint

    Exit Function
    MsgBox "Print_Report error: " & vbCrLf & Err.Number & ": " & Err.Description
End Function

You can call the function like this:

Private Sub Print_Report_Example()
    Call Print_Report("Report1")
End Sub

Notice that the function uses the following line of code to print the report:

DoCmd.RunCommand acCmdPrint

after Opening and Selecting the report (see next section).

Open / Select Report

The above function used DoCmd.OpenReport to open the report:

DoCmd.OpenReport "Report1", acViewPreview, , , , acHiden

Note: for this example we’ve replaced the variable ReportName with table name “Report1”

Next it selects the report:

DoCmd.SelectObject acReport, "Report1"

Filter Report

You can also Filter a Report by opening the report with specific criteria:

DoCmd.OpenReport "Report1", acViewPreview, , "num=0"

Export Report to Excel

You can use the DoCmd.OutputTo to output the report to Excel:

DoCmd.OutputTo acOutputReport, "Rpt1", acFormatXLS, "C:\examples\report1.xls"

This VBA function will export a report to Excel:

Public Function Export_Report(ReportName As String, FilePath As String)
    On Error GoTo SubError
    DoCmd.OutputTo acOutputReport, ReportName, acFormatXLS, FilePath
    Exit Function
    Call Export_Report("Report1", "c:\temp\ExportedReport.xls")
End Function

The function will save to the entered FilePath, but if no file path is chosen, it will save to the Temp Folder on the C drive.

You can call the function like this:

Private Sub Export_Report_Example()
    Call Export_Report("Rpt1", "C:\examples\report1.xls")
End Sub

The DoCmd.OutputTo outputs to a new file. Instead you can output to an existing file or use DoCmd.OutputTo to output other Excel objects. You can learn more in our article about Importing / Exporting in Access VBA.


VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples