Office Watch

Office 2013

Office Mobile / iPad

Office 2010

Office 2007

Office 2003

Office XP

Office for Mere Mortals

Access

Email

Buying Office

Office 365

Winks

Office News Wire

Join us!

Our Ebooks

Mobile | PDA

RSS


Search

Command Finder


Microsoft Office Bookshop

About

Home




Saving to PDF in Access 2007

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

by Access Watch

Bookmark and Share

  | Mobile | click for more article services     


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

 

Article posted: Friday, 23 February 2007

there's more ...

If you liked this article you'll LOVE our new ebooks.

Office 2013: the real startup guide

OFFICE 2013: the real startup guide Everything you need to know about Office 2013 but Microsoft won't tell you.

How to save money, install, configure and use the new features in Office 2013.  Get it today - click here.

Windows 8 for Microsoft Office users

Windows 8 for Microsoft Office users A practical guide the new, changed and unfamiliar in Windows 8

A focused and unvarnished look at Windows 8, especially written for the many people who use Microsoft Office  Get it today - click here.

ORGANIZING OUTLOOK EMAIL - tame your Outlook 2010 Inbox

100+ pages of practical tips and help to streamline, automate and search your Inbox.  Get more than you ever thought possible from Outlook.  Read it today - click here.

More from Office Watch:



Article Services sponsored by: Office Watch Ebooks - available now to download and read today.
RSS feed for this category Subscribe

Translate | Mobile | Links
 Add to: Bookmarks | | DiggThis | Yahoo! My Web


New & Popular
» Using Conversations in Outlook
» OneDrive for Business alters files
» About Outlook Conversations
» Keep using your device on the plane
» Questionable Outlook holidays
» Office 365 Personal arrives


Office Watch, Office for Mere Mortals, Access Watch and all titles used within the publications are Copyright © 1996-2014 Office Watch.
Microsoft Office, Microsoft Word, Microsoft Excel, Microsoft Outlook, Microsoft Powerpoint and doubtless many other names are registered trademarks of Microsoft Corporation.

Search  |  Sitemap |  Popular Topics | Privacy Statement |  Advertising |  Twitter |  Feedback / Contact Us
Office Watch is definitely not affiliated with Microsoft - and that's just one reason why we are so useful to Microsoft Office users around the world J (Erko).