• Printing Access Reports To A PDF File

    This article contains code examples to print a Microsoft Access report to a PDF file.

    This code examples is built for PDFCreator, an open source PDF writer utility. Unlike Adobe Acrobat and CutePDF, which both require pro versions to create PDF's via code, PDFCreator is completely free! Download PDF Creator from Sourceforge here. Please note that this code will NOT work with Adobe Acrobat.

    It should also be noted that this code example uses an Early Bind. If you are not familiar with the difference between Early and Late Binding, please read our article on Early vs Late binding.

    Versions Tested:
    This routine was tested successfully using PDFCreator 0.9.1, GPLGhostscript.exe download package, on Windows XP Pro (SP2), using Microsoft Access 2003.

    NOTE: Before you "go it alone" with trying to adapt this routine, you may want to read this article, which shares some of the idiosyncrasies discovered in the development of the PDFCreator code samples.

    Code Required:
    This code goes in a standard module. You will need to set a reference to PDFCreator, and change the name of the report. You may also want to update the directory to save the file into (sPDFPath), as it will currently save the file into the database's directory.
    Code:
    Option Compare Database
    Option Explicit
    Sub PrintAccessReportToPDF_Early()
    'Author       : Ken Puls ( www.excelguru.ca)
    'Macro Purpose: Print to PDF file using PDFCreator
    '   (Download from http://sourceforge.net/projects/pdfcreator/ )
    '   Designed for early bind, set reference to PDFCreator
    
        Dim pdfjob As PDFCreator.clsPDFCreator
        Dim sPDFName As String
        Dim sPDFPath As String
        Dim sPrinterName As String
        Dim sReportName As String
        Dim lPrinters As Long
        Dim lPrinterCurrent As Long
        Dim lPrinterPDF As Long
        Dim prtDefault As Printer
    
        '/// Change the report and output file name here! ///
        sReportName = "Chart of Accounts"
        sPDFName = sReportName & ".pdf"
        sPDFPath = Application.CurrentProject.Path & "\"
    
        'Resolve index number of printers to allow changing and preserving
        sPrinterName = Application.Printer.DeviceName
        On Error Resume Next
        For lPrinters = 0 To Application.Printers.Count
            Set Application.Printer = Application.Printers(lPrinters)
            Set prtDefault = Application.Printer
            Select Case prtDefault.DeviceName
                Case Is = sPrinterName
                    lPrinterCurrent = lPrinters
                Case Is = "PDFCreator"
                    lPrinterPDF = lPrinters
                Case Else
                    'do nothing
            End Select
        Next lPrinters
        On Error GoTo 0
       
        'Change the default printer
        Set Application.Printer = Application.Printers(lPrinterPDF)
        Set prtDefault = Application.Printer
        'Start PFF Creator
        Set pdfjob = New PDFCreator.clsPDFCreator
        With pdfjob
            If .cStart("/NoProcessingAtStartup") = False Then
                MsgBox "Can't initialize PDFCreator.", vbCritical + _
                        vbOKOnly, "PrtPDFCreator"
                Exit Sub
            End If
            .cOption("UseAutosave") = 1
            .cOption("UseAutosaveDirectory") = 1
            .cOption("AutosaveDirectory") = sPDFPath
            .cOption("AutosaveFilename") = sPDFName
            .cOption("AutosaveFormat") = 0    ' 0 = PDF
            .cClearCache
        End With
    
        'Print the document to PDF
        DoCmd.OpenReport (sReportName)
       
        'Wait until the print job has entered the print queue
        Do Until pdfjob.cCountOfPrintjobs = 1
            DoEvents
        Loop
        pdfjob.cPrinterStop = False
    
        'Wait until PDF creator is finished then release the objects
        Do Until pdfjob.cCountOfPrintjobs = 0
            DoEvents
        Loop
        pdfjob.cClose
    
        'Reset the (original) default printer and release PDF Creator
        Set Application.Printer = Application.Printers(lPrinterCurrent)
        Set pdfjob = Nothing
    End Sub

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 3 Comments
    1. batman75's Avatar
      batman75 -
      Just wanted to say thanks for this piece of code. I have a modified version of this working with Access 10 on Windows 7, PDFCreator 1.2.2. It has really saved our bacon workwise this year. Thanks.
    1. EricMooiweer's Avatar
      EricMooiweer -
      Hi,Have tried running the exact code, but keep getting "fail to initialize error". running pdfcreator 1.6.2 and windows 7 access 2010. Never had problems in the past, but now not getting it to work. Any suggestions are appreciated.Thanks Eric
    1. EricMooiweer's Avatar
      EricMooiweer -
      Quote Originally Posted by EricMooiweer View Post
      Hi,Have tried running the exact code, but keep getting "fail to initialize error". running pdfcreator 1.6.2 and windows 7 access 2010. Never had problems in the past, but now not getting it to work. Any suggestions are appreciated.Thanks Eric
      . I am progressing slightly. Latest is that the access report opens, yet it stays in the Do Until pdfjob.cCountOfPrintjobs = 1
      DoEvents
      Loop endlessly. Any suggestions? it does not seem to add the printerjob to the printingqueu. What am I doing wrong? Suggestions are much appreciated. Thanks for your time, Eric