Saving to PDF in Access 2007

How to save a report to PDF file in Access 2007.

Access Archon #156

 


Introduction

For many years now, Access users have wanted the option of saving a report to the PDF format.  An Access report saved as a PDF file can be opened by anyone (unlike Access snapshots, which are only viewable by recipients who have Access, or the Access Snapshot Viewer, installed).  Saving to PDF didn’t make it into the release version of Access 2007, but Microsoft has provided a Save to PDF utility (downloadable from the link below) that adds a PDF selection to the Save As submenu of the new Office menu that replaces the old File menu.


Downloading the Utility

The Save as PDF utility can be downloaded from here.

(If the link is broken, just search for “Save as PDF” on the Downloads page of the Microsoft site.)

Once you have downloaded the SaveAsPDF.exe file, just run it – there is no setup program, not even a success message, but Access 2007 now has PDF support.


Using the Save as PDF selection

Saving to PDF is primarily useful for reports, though you might occasionally want to save a form as a PDF to send it to someone for review of the form’s appearance.  To save a report as a PDF file, select the report, then open the Office menu and select the Save As command; select the PDF choice, as shown in Figure A, to save the report to a PDF file.

Figure A.  Saving a report to a PDF File

The PDF selection opens a dialog where you can browse for the folder where you want the PDF file to be saved:

Figure B.  Selecting a file name and folder for the PDF file

The PDF file looks just like the report:

Figure C.  A PDF file created from an Access report


VBA Code

With the Save as PDF utility installed, you can export a report to a PDF file using code like the following (each function can be run from a macro in the sample database, ExportToPDF.accdb):

Public Function ReorderInventory()

 

This error handler goes to the CreateSnapshot section if saving to PDF fails

On Error GoTo CreateSnapshot

 

   strCurrentPath = Application.CurrentProject.Path

   strReport = “rptProductsToReorder”

  

First try to export to PDF (this will only work if you have installed the Save to PDF utility)

   strReportFile = strCurrentPath & “Products To Reorder.pdf”

   Debug.Print “Report and path: ” & strReportFile

   DoCmd.OutputTo objecttype:=acOutputReport, _

      objectname:=strReport, _

      outputformat:=acFormatPDF, _ _

      outputfile:=strReportFile

 

If the PDF file creation succeeds, go directly to the CreateEmail section, skipping the CreateSnapshot section

   GoTo CreateEmail

  

On Error GoTo ErrorHandler

 

CreateSnapshot:

 

Export report to snapshot format

   strReportFile = strCurrentPath & “Products To Reorder.snp”

   Debug.Print “Report and path: ” & strReportFile

   DoCmd.OutputTo objecttype:=acOutputReport, _

      objectname:=strReport, _

      outputformat:=acFormatSNP, _

      outputfile:=strReportFile

  

CreateEmail:

   Set msg = appOutlook.CreateItem(olMailItem)

   msg.Attachments.Add strReportFile

   msg.Subject = “Products to reorder for ” _

      & Format(Date, “dd-mmm-yyyy”)

   msg.Display

     

ErrorHandlerExit:t:

   Exit Function

 

ErrorHandler:

   MsgBox “Error No: ” & Err.Number _

      & “; Description: ” & Err.Description

   Resume ErrorHandlerExit

 

End Function

 

Public Function SendShippingReports()

 

This error handler goes to the CreateSnapshot section if saving to PDF fails

On Error GoTo CreateSnapshot

 

   strCurrentPath = Application.CurrentProject.Path

   strReport = “rptProductsShipped”

  

First try to export to PDF (this will only work if you have installed the Save to PDF utility)

   strReportFile = strCurrentPath & “Products Shipped.pdf”

   Debug.Print “Report and path: ” & strReportFile

   DoCmd.OutputTo objecttype:=acOutputReport, _ _

      objectname:=strReport, _

      outputformat:=acFormatPDF, _

      outputfile:=strReportFile

 

If the PDF file creation succeeds, go directly to the CreateEmail section, skipping the CreateSnapshot section

   GoTo CreateEmail

  

On Error GoTo ErrorHandler

  

CreateSnapshot:

 

Export report to snapshot format

   strReportFile = strCurrentPath & “Products Shipped.snp”

   Debug.Print “Report and path: ” & strReportFile

   DoCmd.OutputTo objecttype:=acOutputReport, _

      objectname:=strReport, _

      outputformat:=acFormatSNP, _

      outputfile:=strReportFile

  

CreateEmail:l:

   Set msg = appOutlook.CreateItem(olMailItem)

   msg.Attachments.Add strReportFile

   msg.Subject = “Shipping Report for ” _

      & Format(Date, “dd-mmm-yyyy”)

   msg.Save

   msg.Display

  

ErrorHandlerExit:

   Set appOutlook = Nothing

   Exit Function

 

ErrorHandler:

   ‘Outlook is not running; open Outlook with CreateObject

   If Err.Number = 429 Then

      Set appOutlook = CreateObject(“Outlook.Application”)

      Resume Next

   Else

      MsgBox “Error No: ” & Err.Number _

         & “; Description: ” & Err.Description

      Resume ErrorHandlerExit

   End If

 

End Function

 

Each procedure first creates a PDF file from a report, then creates a new Outlook email message with the PDF file as an attachment; one of these messages is shown in Figure D:

Figure D.  An Outlook mail message with a PDF file attachment


References

The code in the sample database needs the following references (in addition to the default references):

Microsoft Scripting Runtime

Microsoft Outlook 12.0 Object Library

If you import code or objects into a database of your own, you may need to set one or more of these references.  References are set in the References dialog, opened from the VBA window.  For more information on working with references, see Access Archon #107, Working with References.


Supporting Files

The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site.  It is accarch156.zip, which is the last entry in the table of Access Archon columns for Access Watch.











Document Name

Document Type

Place in

SaveToPDF.accdb

Access 2007 database

Wherever you want