Access VBA Reports – Print, Export, Filter

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

SubExit:
    Exit Function
SubError:
    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
    
SubExit:
    Exit Function
SubError:
    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.