How to Export Data from Access into Excel and PDF

Exporting data from Microsoft Access to other file formats like Excel and PDF is a process that offers versatility in sharing and presenting information. The process of Export Data from Access can be done by navigating to the External Data tab or from the VBA code embedded into your database.

Usually, tables and queries can be exported into Excel file format but the Reports contain a designed structure so they can be exported into PDF format.

Method 1 – Export Data Using Menu (External Data)

To export data into Excel, begin by opening the Access database and selecting the table or query containing the desired data. Then, navigate to the External Data tab and choose the Excel option. Follow the prompts to specify the Excel file’s location and format preferences, ensuring to select the appropriate options for data formatting and structure. To export data into a PDF, utilize the same process but select the PDF option instead, adjusting settings such as page orientation and layout as needed. This method ensures seamless transfer of Access data into both Excel and PDF formats, catering to diverse presentation and sharing requirements.

Method 2 – Export Data Using VBA Code

Create a folder in your E Drive with the name of “ExportFile” where the files will stored after exporting. In your form, create 2 buttons to export data to Excel and PDF. Open the VBA coding environment from the “On Click” Event of the first button related to Exporting Data into Excel. Here is the code:

Private Sub CmdExportExcel_Click()
    QExpoName = "BudgetReport" & ".xls"
    QExpoPath = "E:\ExportFiles\" & QExpoName
    DoCmd.OutputTo acOutputQuery, "BudgetReport", acFormatXLS, QExpoPath, True, , , acExportQualityPrint
End Sub

The code to Export the report into PDF file format:

Private Sub CmdExportPDF_Click()
Dim reportName As String
Dim fileName As String

reportName = "tbl_accounts"
fileName = "E:\ExportFiles\AccountsReport.pdf"

DoCmd.OpenReport reportName, acViewPreview, , , acHidden
DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
DoCmd.Close acForm, reportName, acSaveNo

End Sub