Printing Worksheets To A PDF File (Using Early Binding)

Rate this article

Excellent
Good
Average
Bad
Terrible

Published on 2006-04-11 04:48 AM Number of Views: 165868

28 Comments

Categories:

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 https://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 https://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 https://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 https://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 https://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!

28 Comments

Anbarasan Gopal - 2013-05-27, 05:30 PM
- Reply
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

Ken Puls - 2013-07-04, 08:51 PM
- Reply
Sounds like you might need to set a reference to the PDFCreator library.

vince47 - 2013-08-24, 01:41 PM
- Reply
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.

WahWahWeeks - 2013-08-30, 12:04 PM
- Reply
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

Question_and_Answer? - 2013-12-31, 08:07 PM
- Reply
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...!

Question_and_Answer? - 2014-01-01, 09:08 AM
- Reply
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!

AndyC - 2014-01-13, 11:43 AM
- Reply
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.

Not a guru - 2014-03-05, 01:27 PM
- Reply
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

Rocio - 2014-04-04, 01:15 PM
- Reply
(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.

matrix808 - 2014-08-18, 10:16 AM
- Reply
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

Jim Viau - 2014-11-21, 09:29 PM
- Reply
My problem is new (I think, at least I haven't read anything about it here).
I am using PDFcreator within VBA for years now and lately, after starting afresh a new win7 system and reinstalling every Apps that were installed in my previous installation, I launched my EXCEL application and sent a request to print via PDFcreator.

My Excel App, as always, created ONE single PDF document with 3 pages from 3 Excel sheets.
Now, with the exact same code I get the document to open in my default Foxit Reader but the document does not include the file title as it used to, and it doesn't save at the set folder as it used to. The main difference is instead of using Foxit Reader version 2.3 (old, 2008 maybe) I installed Foxit Reader 6.1.5.0624 And, Yes, I did check the Autosave in the PDFcreator setup and programmed the default folder for Autosave.

I can't imagine that Foxit would produce this change in behavior but hey!, anything goes when you program stuff, i guess.
My VBA code has not changed. My EXCEL app has not changed, I installed the same PDFcreator 0.9.5.
And, after this unwanted result, I went back to the VBA Project.Reference and killed the reference to PDFcreator, saved the EXCEL file. Closed it.
And, reopened the file, reestablished the VBA Project.Reference to PDFcreator, recompiled. Just to make sure that I had a valid link to the PDFcreator.exe file.
Didn't change anything. the darned thing will NOT save my file like it used to.

Anyone has any idea as to where I should start looking ?
I am really puzzled. What Am I missing ?

Thanks for your help anyone...

hareesan - 2015-02-17, 01:23 PM

Hello Ken,

Tried to use this for printing multiple sheets to multiple PDF using PDF creator latest version & Excel 2013. It gives an error of "User Defined Type Not Defined" though in the references the PDF creator is selected. Can you help with this? Thanks!

Code:

Option Explicit
Sub PrintToPDF_MultiSheet_Early()
'Author       : Ken Puls
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from )
'   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

Fellwalker - 2015-02-25, 07:43 PM
- Reply
@Hareesan

You have
& Sheets(lSheet).Name &

The original has
& Sheets(sSheets(lSheet)).Name &

Hope that helps

hareesan - 2015-02-26, 02:11 PM
- Reply
Hello,

Thanks for the help. I tried that, but did not help. The error of "User Defined Type Not Defined" is highlighted in line "Dim pdfjob As PdfCreator.clsPDFCreator".

Bob Phillips - 2015-02-27, 10:13 AM
- Reply
Have you installed PDFCreator?

Bob Phillips - 2015-02-27, 10:14 AM
- Reply
BTW, why not use Excel's built-in PDF create?

hareesan - 2015-02-27, 11:35 AM
- Reply
Hello Bob,

Yes, I did install PDFCreator several times. Downloaded from the link given in the macro.

hareesan - 2015-02-27, 11:47 AM
- Reply
Originally Posted by Bob Phillips

BTW, why not use Excel's built-in PDF create?

I don't know whether using the in-built pdf create would work as good as the macro. I need to print multiple sheets in to multiple pdf files with files name as per the sheet name & also encrypt them with a pw provided it the respective sheet. Earlier this macro was working great in Win XP & Excel 2007. Since I have upgraded to Windows 8.1 with MS Office 2013 this macro has stopped working. So, then I revisited this site, adapted the new macro for Excel 13, installed new version of PDFCreator, but, somehow its not working. I have a macro which will print multiple excel sheets to multiple pdf files, however, I will have to encrypt each Pdf manually. Hope to find a solution soon.

Jim Viau - 2015-04-08, 05:15 AM
- Reply
Hello Hareesan,
My problem is similar and been puzzling for a long time until recently where I discovered that perhaps this VBA code is no longer valid for the new PDFcreator version 2 and up. I newly installed the version 2.1 and within the installation folder file "PDFCreator_english.chm" which is the user manual, is included some directives about the new COM for PDFcreator. It appear that the Reference to the PDFcreator object is quite different then is used to be. I will have to go through it in depth to understand it and adapt it to VBA, but one thing is sure, the PdfCreator.clsPDFCreator is no longer the correct call. From VBA I was able to find the new call that would look like this Dim pdfjob As PdfCreator.PdfCreatorObj. Other parts of the code are no longer valid as well. It would be great to get someone to supply a new working version of this great VBA PDF printer. It used to work great. I may put some time on it and post my findings later. In the mean time if someone else could bring their help it would certainly be welcome.

roll4ever - 2015-04-08, 10:57 AM
- Reply
Hello Ken,

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.

I have the same question. Could someone please give me the answer?

Thank you very much in advance.

Knowledge Base Articles By Category

Printing Worksheets To A PDF File (Using Early Binding)

Rate this article

vBulletin Message

Recent Forum Posts

Sorting Causes Worksheet to End at 16,000 or So

Helps with Excel or Programming! Need to move rows to a separate tab

Move column to header/consolidate rows

Move column to header/consolidate rows

Search Table With Formulas Or Arrays?