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 |
See Also
- Why some PDF’s won’t work in Word 2013
- Font embedding problems in Office
- Fixing PDF Preview in Windows 7
- Converting a PDF to Word
- More on saving to PDF in Access 2007
