Unanswered: Print to PDF using PDFCreator as Printer
I have the below macro which prints all excel sheets to pdf using pdfcreator as printer, it works absolutely fine but I want to come up with a userform where all excel sheets of a workbook will be listed down and then the user will be able to select the sheets and create the pdf as per his requirement. The Userform will do the following things for user.
1) Userform will allow user to select the sheets which he want to print to pdf.
2) It will allow him to select a option wherein he will able to print multiple sheets to one pdf.
3) If he doesn't select that option it will print each sheet to a separate pdf and save it on a selected path.
I have come up with a userform but I need a help to incorporate the below macro to the same so that they work as per above requirements.
' Print Multiple Worksheets to a Single PDF File:
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Dim lSheet As Long
Dim lTtlSheets As Long
'/// Change the output file name here! ///
sPDFName = "Consolidated.pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
Set pdfjob = New PDFCreator.clsPDFCreator
'Make sure the PDF printer can start
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
'Set all defaults
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
'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"
lTtlSheets = lTtlSheets - 1
On Error GoTo 0
'Wait until all print jobs have entered the print queue
Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
'Combine all PDFs into a single file and stop the printer
.cPrinterStop = False
'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
MsgBox ("The PDF has been successfully created as " & sPDFName)
Set pdfjob = Nothing