Save Report as PDF File

Ease of Use


Version tested with


Submitted by:



To save an Access report as a PDF file basically involves selecting "Acrobat PDFWriter" as your printer, then selecting the name and path for your PDF file. This code sets the name and path programmatically, so that you are not prompted for the filename. 


There is a lot of information floating around re saving Access reports as PDF files. If you are using Adobe Acrobat version 5 or earlier, this requires changing a registry setting in order to set the path and name of your PDF. However in most corporate environments, the majority of users are restricted from updating the registry, so creating the keys directly (even via code) is not an option. This code creates a registry file which is merged into the registry. It then prints the report as a PDF file, which automatically clears the registry setting that you have added. In order for this to work you must have a version of Adobe Acrobat installed. You also need to check the exact name of the printer used by Adobe, as this can differ depending on the version. 


instructions for use


Private Sub cmdPrintEmp_Click() Dim strSave As String strSave = "EmployeeList_" & Format(Date, "yyyymmdd") & ".PDF" 'Call the function to print it out If PrintReportToPDF("rpt_Employee", strSave) = True Then MsgBox "The report has been printed as " & vbCrLf & vbCrLf & _ Replace(strSave, "\\", "\") Else MsgBox "The report FAILED to print as a PDF file!", vbCritical, "PDF Failed" End If End Sub Public Function PrintReportToPDF(strReport As String, strSave As String) As Boolean '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ' Purpose: Print a report to a PDF file ' ' Inputs: strReport Name of report ' strSave Name of PDF file to create '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ On Error GoTo ErrHandler ' create the registry entry to set PDF path and filename WriteRegistryEntry strSave ' print the report - CHECK THE PRINTER NAME IS CORRECT Set Application.Printer = Application.Printers("Acrobat PDFWriter") DoCmd.OpenReport strReport, acViewNormal Application.Printer = Nothing PrintReportToPDF = True ExitHere: Exit Function ErrHandler: MsgBox Err.Description Resume ExitHere End Function Public Function WriteRegistryEntry(strPDF As String) '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ' Purpose: Create a registry file in order to set the name and path ' of the PDF file ' ' Reference: Concept developed from post at ' ' ' Assumptions: Registry file is created in same folder as current database, ' then deleted once it has been merged into the registry ' ' Inputs: strPDF Name of PDF file to create '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Dim strPath As String Dim x strPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\", , vbTextCompare)) ' make sure reports folder exists If Dir(strPath & "Reports\", vbDirectory) = "" Then MkDir strPath & "Reports\" End If ' registry key needs "\\" in file path strPDF = strPath & "Reports\" & strPDF strPDF = Replace(strPDF, "\", "\\") ' delete the registry file if it exists On Error Resume Next Kill strPath & "CreatePDF.reg" ' create the registry file On Error GoTo ErrHandler Open strPath & "CreatePDF.reg" For Append As #1 Print #1, "Windows Registry Editor Version 5.00" Print #1, "" Print #1, "[HKEY_CURRENT_USER\Software\Adobe\Acrobat PDFWriter]" Print #1, """PDFFilename""=" & Chr(34) & strPDF & Chr(34) Close #1 ' merge into registry x = Shell("regedit.exe /s " & strPath & "CreatePDF.reg", vbHide) ExitHere: On Error Resume Next Close #1 Kill strPath & "CreatePDF.reg" Exit Function ErrHandler: MsgBox Err.Description Resume ExitHere End Function

How to use:

  1. Save the attached database to a local folder
  2. Open PrintToPDF.mdb

Test the code:

  1. Open the Form "frm_Employee_Print"
  2. Click on "Print"
  3. In Windows Explorer, go to the folder in which the database resides
  4. Open the new "Reports" sub folder
  5. The report is saved as "EmployeeList_yyyymmdd.PDF" where "yyyymmdd" is today's date

Sample File: 26.18KB 

Approved by mdmackillop

This entry has been viewed 483 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express