How to Export Your Access Report to a PDF File via VBA

Learn how you can create a VBA function that will allow you to save your Access Report as PDF with just a click of a button. No need to go to the Access Ribbon every time!

Access Reports can be manually saved as PDF file. All you have to do is:Manually Saved As PDF File

  1. Open the report.
  2. Go to the Access Ribbon: External Data > PDF or XPS.
  3. Select the filename and folder path where you would like it to be saved.
  4. Click Publish and you’re done.

Easy, right? However, if there is a need for you to repeatedly export reports, say on a daily basis, this can be a bit taxing.

Don’t worry. There’s an easier way. With just one click of a button, your report can be immediately exported to your designated folder. This can be done with the help of VBA. Just follow the steps below.

Download Now

If you want to start to use the feature as soon as possible, then you can:

Download the Sample Database with VBA Codes Now

Otherwise, if you want to DIY, you can read the contents below.

Steps to Creating a VBA Function that Saves Access Report as PDF File

1. Add a command button inside your report

This article assumes that you already have an Access Report created. Open it in design view and add a button in the Report header (as shown below).Add A Command Button Inside Your Report

Rename the button as cmd_exportPDF. To do this:

  1. While in design view, select the button.
  2. Open the Property Sheet (ALT + Enter).
  3. Go to the Other tab > Name.
  4. Change the Name value to cmd_exportPDF.

2. Add code for the command button

To add the code, you have to first add an On Click event to the button.

  1. With the command button selected, go again to the Property Sheet.
  2. Click on the Event tab.
  3. Look for On Click and select …
  4. When the Choose Builder menu pops up, select Code Builder and click OK.
  5. This will open the VBA Editor.

Now, copy the code below.

Don’t forget to update values for filename and fldrPath. They each have asterisk (*) in the comment section.

Function FileExist(FileFullPath As String) As Boolean
  Dim value As Boolean
  value = False
  If Dir(FileFullPath) <> "" Then
    value = True
  End If
  FileExist = value
End Function

Private Sub cmd_exportPDF_Click()
  Dim fileName As String, fldrPath As String, filePath As String
  Dim answer As Integer
  fileName = "Member Contact Details" 'filename for PDF file*
  fldrPath = "C:\Users\Jessica\Desktop\PDF Exports" 'folder path where pdf file will be saved *
  filePath = fldrPath & "\" & fileName & ".pdf"
  'check if file already exists
  If FileExist(filePath) Then
    answer = MsgBox(prompt:="PDF file already exists: " & vbNewLine & filePath & vbNewLine & vbNewLine & _
      "Would you like to replace existing file?", buttons:=vbYesNo, Title:="Existing PDF File")
    If answer = vbNo Then Exit Sub
  End If
  On Error GoTo invalidFolderPath
  DoCmd.OutputTo objecttype:=acOutputReport, objectName:=Me.Name, outputformat:=acFormatPDF, outputFile:=filePath
  MsgBox prompt:="PDF File exported to: " & vbNewLine & filePath, buttons:=vbInformation, Title:="Report Exported as PDF"
  Exit Sub
  MsgBox prompt:="Error: Invalid folder path. Please update code.", buttons:=vbCritical
End Sub

These lines of code will:

  1. First check if a PDF file with the same filename already exists in the specified folder. If it does, a prompt will appear asking if you would like to replace it with a new one.

If you select yes, the report will continue with the export process. If not, the file will not be converted to PDF.

  1. If you entered a folder path that does not exist, a prompt will appear to ask you to update the folder path specified in the code. Export process will not continue until this is corrected.
  2. Once the report has been successfully exported, a prompt will appear indicating where the PDF file was saved.

That’s it! Now it’s time to test your button.

Unable to Open Access Database

The most terrifying thing that can happen to any Access user is to wake up one day and realize that the database could no longer be opened. It has been corrupted. What’s even worse is that you realize that you were not able to back up your data. Don’t worry. DataNumen Inc. created a software that can fix Access database. Your database can still be brought back to life.

Author Introduction:

Jayme Stack is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and excel recovery software products. For more information visit

9 responses to “How to Export Your Access Report to a PDF File via VBA”

  1. Wonderful blog! Do you have any recommendations for aspiring writers? I’m planning to start my own website soon but I’m a little lost on everything. Would you advise starting with a free platform like WordPress or go for a paid option? There are so many options out there that I’m completely confused .. Any ideas? Thanks!

  2. This was very useful for exporting a full file. Do you have sample code that would allow you export an report to multiple PDFs based on a grouping?

  3. Hello I need this help, I can’t run using the query(qryCliente) to export only the list of customers. Only the nameCliente field. In the .docx file I have the Bookmark of the customer nameCliente field.

    Error: Undefined variable

    Public Sub ExportToWord()
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim rs As DAO.Recordset

    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open(CurrentProject.Path & “\DBClientes\listCliente.docx”)
    Set rs = CurrentDb.OpenRecordset(CST_CLIENTE, “nameCliente”)

    If Not rs.EOF Then rs.MoveFirst
    Do Until rs.EOF
    wDoc.Bookmarks(“nameCliente”).Range.Text = Nz(rs!nameCliente, “”)

    wDoc.SaveAs2 CurrentProject.Path & “” & rs!ID & “_listCliente2.docx”

    Set wDoc = wApp.Documents.Open(CurrentProject.Path & “\listCliente2.docx”)
    If Not wDoc Is Nothing Then
    MsgBox “open file!”
    MsgBox “file not open!”
    End If

    wDoc.Bookmarks(“nameCliente”).Range.Delete wdCharacter, Len(Nz(rs!nameCliente, “”))


    wDoc.Close False

    Set wDoc = Nothing
    Set wApp = Nothing
    Set rs = Nothing

    End Sub

  4. Thanks for posting this. I’ve tried this code, making the asterisked changes, but I keep getting the message saying the file path is invalid. It’s definitely correct. Can you help me understand what I’m missing? Thanks very much.

  5. Another question: my report has definitely only one page. But the export (with the commands as shown above) has 3-4 pages, only the first page shows the page from MSAcess, the rest is empty. How comes? How can I avoid the empty pages?

  6. This is very helpful. However, I do have another issue. The report I have contains multiple records. Is there something I can add that will tell it to cycle through a particular table and look for a “Plan number” and publish each of the records to separate pdf files. and name it according to a field in the same table? Instead of having one report that contains all the records.

Leave a Reply

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