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:
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
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
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
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
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
vBulletin Message