• Printing Worksheets To A PDF File (Using Early Binding)

    Introduction:
    This article contains code examples to print worksheets to PDF files.

    These code examples are 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 each of the examples in this section use 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.

    Routines Included In This Article:
    • Print a Single Worksheet to a PDF File
    • Print Multiple Worksheets to Multiple PDF Files
    • Print Multiple Worksheets to a Single PDF File
    • Print Selected Worksheets to Multiple PDF Files
    • Print Specified Worksheets to a Single PDF File

    Versions Tested:
    These routines were originally developed using PDFCreator 0.9.1 (GPLGhostscript.exe download package) on Windows XP Pro (SP2). The current versions below contain numerous improvements and were fully tested using PDFCreator 1.2.0 on Windows 7 Ultimate x64 and PDFCreator 1.2.3 on Windows 8 Professional x64. Excel versions tested include:
    • Excel 2003
    • Excel 2007
    • Excel 2010 (32 bit)
    • Excel 2010 (64 bit)
    • Excel 2013 (32 bit)
    • Excel 2013 (64 bit)

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

    Print a Single Worksheet to a PDF File:
    Code:
    Option Explicit
    Sub PrintToPDF_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 bRestart As Boolean
    
        '/// Change the output file name here! ///
        sPDFName = "testPDF.pdf"
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    
        'Check if worksheet is empty and exit if so
        If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
    
        'Activate error handling and turn off screen updates
        On Error GoTo EarlyExit
        Application.ScreenUpdating = False
        Set pdfjob = New PDFCreator.clsPDFCreator
    
        'Check if PDFCreator is already running and attempt to kill the process if so
        Do
            bRestart = False
            Set pdfjob = New PDFCreator.clsPDFCreator
            If pdfjob.cStart("/NoProcessingAtStartup") = False Then
                'PDF Creator is already running.  Kill the existing process
                Shell "taskkill /f /im PDFCreator.exe", vbHide
                DoEvents
                Set pdfjob = Nothing
                bRestart = True
            End If
        Loop Until bRestart = False
    
        'Assign settings for PDF job
        With pdfjob
            .cOption("UseAutosave") = 1
            .cOption("UseAutosaveDirectory") = 1
            .cOption("AutosaveDirectory") = sPDFPath
            .cOption("AutosaveFilename") = sPDFName
            .cOption("AutosaveFormat") = 0    ' 0 = PDF
            .cClearCache
        End With
    
        'Delete the PDF if it already exists
        If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)
    
        'Print the document to PDF
        ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
    
        'Wait until the print job has entered the print queue
        Do Until pdfjob.cCountOfPrintjobs = 1
            DoEvents
        Loop
        pdfjob.cPrinterStop = False
    
        'Wait until the file shows up before closing PDF Creator
        Do
            DoEvents
        Loop Until Dir(sPDFPath & sPDFName) = sPDFName
    
    Cleanup:
        'Release objects and terminate PDFCreator
        Set pdfjob = Nothing
        Shell "taskkill /f /im PDFCreator.exe", vbHide
        On Error GoTo 0
        Application.ScreenUpdating = True
        Exit Sub
    
    EarlyExit:
        'Inform user of error, and go to cleanup section
        MsgBox "There was an error encountered.  PDFCreator has" & vbCrLf & _
               "has been terminated.  Please try again.", _
               vbCritical + vbOKOnly, "Error"
        Resume Cleanup
    End Sub
    Print Multiple Worksheets to Multiple PDF Files:
    Code:
    Option Explicit
    Sub PrintToPDF_MultiSheet_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 lSheet As Long
        Dim bRestart As Boolean
    
        'Activate error handling and turn off screen updates
        On Error GoTo EarlyExit
        Application.ScreenUpdating = False
        Set pdfjob = New PDFCreator.clsPDFCreator
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    
        'Check if PDFCreator is already running and attempt to kill the process if so
        Do
            bRestart = False
            Set pdfjob = New PDFCreator.clsPDFCreator
            If pdfjob.cStart("/NoProcessingAtStartup") = False Then
                'PDF Creator is already running.  Kill the existing process
                Shell "taskkill /f /im PDFCreator.exe", vbHide
                DoEvents
                Set pdfjob = Nothing
                bRestart = True
            End If
        Loop Until bRestart = False
    
        For lSheet = 1 To ActiveWorkbook.Sheets.Count
            'Check if worksheet is empty and skip if so
            If Not IsEmpty(Sheets(lSheet).UsedRange) Then
                With pdfjob
                    '/// Change the output file name here! ///
                    sPDFName = "testPDF" & Sheets(lSheet).Name & ".pdf"
                    .cOption("UseAutosave") = 1
                    .cOption("UseAutosaveDirectory") = 1
                    .cOption("AutosaveDirectory") = sPDFPath
                    .cOption("AutosaveFilename") = sPDFName
                    .cOption("AutosaveFormat") = 0    ' 0 = PDF
                    .cClearCache
                End With
        
                'Delete the PDF if it already exists
                If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)
        
                'Print the document to PDF
                Worksheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        
                'Wait until the print job has entered the print queue
                Do Until pdfjob.cCountOfPrintjobs = 1
                    DoEvents
                Loop
                pdfjob.cPrinterStop = False
        
                'Wait until the file shows up before moving on
                'Important:  Counter must reach zero or hangs on next iteration
                Do Until pdfjob.cCountOfPrintjobs = 0
                    DoEvents
                Loop
            End If
        Next lSheet
        
    Cleanup:
        'Release objects and terminate PDFCreator
        Set pdfjob = Nothing
        Shell "taskkill /f /im PDFCreator.exe", vbHide
        On Error GoTo 0
        Application.ScreenUpdating = True
        Exit Sub
    
    EarlyExit:
        'Inform user of error, and go to cleanup section
        MsgBox "There was an error encountered.  PDFCreator has" & vbCrLf & _
               "has been terminated.  Please try again.", _
               vbCritical + vbOKOnly, "Error"
        Resume Cleanup
    End Sub
    Print Multiple Worksheets to a Single PDF File:
    Code:
    Option Explicit
    Sub PrintToPDF_MultiSheetToOne_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 lSheet As Long
        Dim lTtlSheets As Long
        Dim bRestart As Boolean
    
        '/// Change the output file name here! ///
        sPDFName = "Consolidated.pdf"
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    
        'Activate error handling and turn off screen updates
        On Error GoTo EarlyExit
        Application.ScreenUpdating = False
        Set pdfjob = New PDFCreator.clsPDFCreator
    
        'Check if PDFCreator is already running and attempt to kill the process if so
        Do
            bRestart = False
            Set pdfjob = New PDFCreator.clsPDFCreator
            If pdfjob.cStart("/NoProcessingAtStartup") = False Then
                'PDF Creator is already running.  Kill the existing process
                Shell "taskkill /f /im PDFCreator.exe", vbHide
                DoEvents
                Set pdfjob = Nothing
                bRestart = True
            End If
        Loop Until bRestart = False
    
        'Assign settings for PDF job
        With pdfjob
            .cOption("UseAutosave") = 1
            .cOption("UseAutosaveDirectory") = 1
            .cOption("AutosaveDirectory") = sPDFPath
            .cOption("AutosaveFilename") = sPDFName
            .cOption("AutosaveFormat") = 0    ' 0 = PDF
            .cClearCache
        End With
        
        'Delete the PDF if it already exists
        If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)
    
        'Print the document to PDF
        lTtlSheets = Application.Sheets.Count
        For lSheet = 1 To Application.Sheets.Count
            On Error Resume Next 'To deal with chart sheets
            If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
                Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
            Else
                lTtlSheets = lTtlSheets - 1
            End If
            On Error GoTo EarlyExit
        Next lSheet
    
        'Wait until all print jobs have entered the print queue
        Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
            DoEvents
        Loop
    
        'Combine all PDFs into a single file and stop the printer
        With pdfjob
            .cCombineAll
            .cPrinterStop = False
        End With
    
        'Wait until the file shows up before closing PDF Creator
        Do
            DoEvents
        Loop Until Dir(sPDFPath & sPDFName) = sPDFName
    
    Cleanup:
        'Release objects and terminate PDFCreator
        Set pdfjob = Nothing
        Shell "taskkill /f /im PDFCreator.exe", vbHide
        On Error GoTo 0
        Application.ScreenUpdating = True
        Exit Sub
    
    EarlyExit:
        'Inform user of error, and go to cleanup section
        MsgBox "There was an error encountered.  PDFCreator has" & vbCrLf & _
               "has been terminated.  Please try again.", _
               vbCritical + vbOKOnly, "Error"
        Resume Cleanup
    End Sub
    Print Specified Worksheets to a Multiple PDF Files:
    Code:
    Option Explicit
    Sub PrintToPDF_SpecifiedSheetsToMulti_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 sPDFPreface As String
        Dim sPDFPath As String
        Dim sSheetsToPrint As String
        Dim sSheets() As String
        Dim lSheet As Long
        Dim bRestart As Boolean
    
        'Activate error handling and turn off screen updates
        On Error GoTo EarlyExit
        Application.ScreenUpdating = False
        Set pdfjob = New PDFCreator.clsPDFCreator
        
        '/// Set the preface for the PDF file name and the file path here! ///
        sPDFPreface = "Report-"
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    
        '/// Record the sheets you want to print here! ///
        '/// Use sheet names separated by commas only  ///
        sSheetsToPrint = "Sheet1,Sheet3"
    
        'Check if PDFCreator is already running and attempt to kill the process if so
        Do
            bRestart = False
            Set pdfjob = New PDFCreator.clsPDFCreator
            If pdfjob.cStart("/NoProcessingAtStartup") = False Then
                'PDF Creator is already running.  Kill the existing process
                Shell "taskkill /f /im PDFCreator.exe", vbHide
                DoEvents
                Set pdfjob = Nothing
                bRestart = True
            End If
        Loop Until bRestart = False
    
        'Split the sheets into an array
        sSheets() = Split(sSheetsToPrint, ",")
        
        For lSheet = LBound(sSheets) To UBound(sSheets)
            'Check if worksheet is empty and skip if so
            If Not IsEmpty(Application.Sheets(sSheets(lSheet)).UsedRange) Then
                With pdfjob
                    '/// Change the output file name here! ///
                    sPDFName = sPDFPreface & Sheets(sSheets(lSheet)).Name & ".pdf"
                    .cOption("UseAutosave") = 1
                    .cOption("UseAutosaveDirectory") = 1
                    .cOption("AutosaveDirectory") = sPDFPath
                    .cOption("AutosaveFilename") = sPDFName
                    .cOption("AutosaveFormat") = 0    ' 0 = PDF
                    .cClearCache
                End With
        
                'Delete the PDF if it already exists
                If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)
        
                'Print the document to PDF
                Application.Sheets(sSheets(lSheet)).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        
                'Wait until the print job has entered the print queue
                Do Until pdfjob.cCountOfPrintjobs = 1
                    DoEvents
                Loop
                pdfjob.cPrinterStop = False
        
                'Wait until the file shows up before moving on
                'Important:  Counter must reach zero or hangs on next iteration
                Do Until pdfjob.cCountOfPrintjobs = 0
                    DoEvents
                Loop
            End If
        Next lSheet
        
    Cleanup:
        'Release objects and terminate PDFCreator
        Set pdfjob = Nothing
        Shell "taskkill /f /im PDFCreator.exe", vbHide
        On Error GoTo 0
        Application.ScreenUpdating = True
        Exit Sub
    
    EarlyExit:
        'Inform user of error, and go to cleanup section
        MsgBox "There was an error encountered.  PDFCreator has" & vbCrLf & _
               "has been terminated.  Please try again.", _
               vbCritical + vbOKOnly, "Error"
        Resume Cleanup
    End Sub
    Print Specified Worksheets to a Single PDF File:
    Code:
    Option Explicit
    Sub PrintToPDF_SpecifiedSheetsToOne_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 sSheetsToPrint As String
        Dim sSheets() As String
        Dim lSheet As Long
        Dim lTtlSheets As Long
        Dim bRestart As Boolean
    
        '/// Change the output file name here! ///
        sPDFName = "Consolidated.pdf"
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
        
        '/// Record the sheets you want to print here! ///
        '/// Use sheet names separated by commas only  ///
        sSheetsToPrint = "Sheet1,Sheet3"
        
        'Activate error handling and turn off screen updates
        On Error GoTo EarlyExit
        Application.ScreenUpdating = False
        Set pdfjob = New PDFCreator.clsPDFCreator
    
        'Check if PDFCreator is already running and attempt to kill the process if so
        Do
            bRestart = False
            Set pdfjob = New PDFCreator.clsPDFCreator
            If pdfjob.cStart("/NoProcessingAtStartup") = False Then
                'PDF Creator is already running.  Kill the existing process
                Shell "taskkill /f /im PDFCreator.exe", vbHide
                DoEvents
                Set pdfjob = Nothing
                bRestart = True
            End If
        Loop Until bRestart = False
    
        'Assign settings for PDF job
        With pdfjob
            .cOption("UseAutosave") = 1
            .cOption("UseAutosaveDirectory") = 1
            .cOption("AutosaveDirectory") = sPDFPath
            .cOption("AutosaveFilename") = sPDFName
            .cOption("AutosaveFormat") = 0    ' 0 = PDF
            .cClearCache
        End With
        
        'Split the sheets into an array
        sSheets() = Split(sSheetsToPrint, ",")
    
        'Delete the PDF if it already exists
        If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)
    
        'Print the document to PDF
        For lSheet = LBound(sSheets) To UBound(sSheets)
            On Error Resume Next 'To deal with chart sheets
            If Not IsEmpty(Application.Sheets(sSheets(lSheet)).UsedRange) Then
                Application.Sheets(sSheets(lSheet)).PrintOut copies:=1, ActivePrinter:="PDFCreator"
                lTtlSheets = lTtlSheets + 1
            End If
            On Error GoTo EarlyExit
        Next lSheet
    
        'Wait until all print jobs have entered the print queue
        Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
            DoEvents
        Loop
    
        'Combine all PDFs into a single file and stop the printer
        With pdfjob
            .cCombineAll
            .cPrinterStop = False
        End With
    
        'Wait until the file shows up before closing PDF Creator
        Do
            DoEvents
        Loop Until Dir(sPDFPath & sPDFName) = sPDFName
    
    Cleanup:
        'Release objects and terminate PDFCreator
        Set pdfjob = Nothing
        Shell "taskkill /f /im PDFCreator.exe", vbHide
        On Error GoTo 0
        Application.ScreenUpdating = True
        Exit Sub
    
    EarlyExit:
        'Inform user of error, and go to cleanup section
        MsgBox "There was an error encountered.  PDFCreator has" & vbCrLf & _
               "has been terminated.  Please try again.", _
               vbCritical + vbOKOnly, "Error"
        Resume Cleanup
    End Sub
    This article was also published as the August 2006 Office Newsletter at Cimaware.com, makers of ExcelFIX. (Unfortunately, the history of these newsletters is no longer available on their site.)

     

    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 10 Comments
    1. Anbarasan Gopal's Avatar
      Anbarasan Gopal -
      i installed in pdf creator 0.9.1 and the macro show error in VBA Code
      Message " Dim pdfjob As PDFCreator.clsPDFCreator " script error

      iam using office 2003
      OS - windows xp3

      Please help

    1. Ken Puls's Avatar
      Ken Puls -
      Sounds like you might need to set a reference to the PDFCreator library.
    1. vince47's Avatar
      vince47 -
      Good code.
      Can someone please help me on these
      1. sometimes the file created gives an error when opened with Adobe.
      2. I cannot find settings so the file is open in PDF after it is saved.
    1. WahWahWeeks's Avatar
      WahWahWeeks -
      Thank you so much. This is clean reliable code that worked well immediately. Also good error checking etc. so should be easy to use. I had failed to find such a good solution in the past and ended up with a messy solution. Will now re-write to improve. Thanks again.

      It was easy to change from whole page to selected range...

      changed from ActiveSheet.Printout
      into Range("A14").Printout
    1. Question_and_Answer?'s Avatar
      Question_and_Answer? -
      Dear Ken,

      tried to use your script that day. It creates the pdf from the vb-editor in the specified folder but the macro won't stop to run. The whole Excel-application becomes blocked that way.
      If I run the macro from the Excel Interface, the file is not even created...
      I installed 1.2.0 on Windows 7. What coud be the cause here?

      Thank you...!
    1. Question_and_Answer?'s Avatar
      Question_and_Answer? -
      Happy new year!
      Sorry for my previous lines. I figured out how to run the brilliant code now. Obviously, my mistake has been, that I wanted to run the code in an existing Sub: Then it gets stuck in the Do...Loop?
      Whn the the code is called in a separate Sub handing over the file name and path variables it works great! Thank you!
    1. AndyC's Avatar
      AndyC -
      Thanks for the code. I currently have a spread sheet saving to a location with the name based on the content of a cell. I need to prevent the user from saving over any existing files with the same name in this location with a prompt stating "file already exists" and then for it to exit the macro without doing anything else. Could you please provide any assistance?

      Many Thanks.
    1. Not a guru's Avatar
      Not a guru -
      Hi.
      I found this wonderful code ( Print a Single Worksheet to a PDF File ) and it works brilljantly, but I have a small problem. When I change the line

      sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
      to sPDFPath = "E\Report" witch is the dir I want to use,

      the program gets stuck in a loop here....

      'Wait until the file shows up before closing PDF Creator Do DoEvents Loop Until Dir(sPDFPath & sPDFName) = sPDFName

      The file shows up in the folder and is readable but excel is stuck in this loop.
      Please help me. I am not a Excel guru
    1. Rocio's Avatar
      Rocio -
      (Print Specified Worksheets to a Single PDF File) super code! thanks a lot. I want to know how or where , I can activate the Option "OpenAfterPublish=True" or other Option that at the end open the merged pdf. Thanks in advance for your help.
    1. matrix808's Avatar
      matrix808 -
      Got the same problem as Not a Guru it hangs on
      'Wait until the file shows up before closing PDF Creator
      Do
      DoEvents
      Loop Until Dir(sPDFPath & sPDFName) = sPDFName

      Can't get it out of the loop any clues
  • MVP Logo
  • Recent Forum Posts

    Bob Phillips

    Pivot table error

    If Colour is a column in your pivot, you can setup a custom list and use that in your sort by right-clicking the field in your pivot table and select...

    Bob Phillips Today, 03:50 PM Go to last post
    Mike_Alex

    Help with dynamic dates

    If I am understanding you correctly, you can actually use the Year(date cell) or Month(date cell) ad accomplish that. Or, for week ending you could use...

    Mike_Alex Today, 03:31 PM Go to last post
    NBVC

    Help with dynamic dates

    When is it necessary to "change the month and year"?...

    NBVC Today, 03:30 PM Go to last post
    Mike_Alex

    Table calc with a slicer

    Not a pivot table, just a table. I was hoping not to have to create a table, then a pivot table on top of that, just to filter the data, but ended up...

    Mike_Alex Today, 03:19 PM Go to last post
    Hercules1946

    Table calc with a slicer

    Hello Mike
    Without seeing some sample data illustrating the problem, Im not sure. Is your table a pivot table, and if not have you tried one?...

    Hercules1946 Today, 02:54 PM Go to last post