Print excel sheets to PDF using PDF Creator

[abhay_547]

Hi All,

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:
Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub PrintToPDF_MultiSheetToOne_Early()


    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 + _
                vbOKOnly, "Error!"
        Exit Sub
    End If

    'Set all defaults
    With pdfjob
        .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
    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 0
    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 PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    MsgBox ("The PDF has been successfully created as " & sPDFName)
    pdfjob.cClose
    Sleep 1000
    Set pdfjob = Nothing
End Sub

Attached is my macro file.

Thanks a lot for your help in advance.

[abhay_547]

Hi All,

Did anyone get the chance to look into the above post. ?

Thanks a lot for your help in advance.

[davesexcel]

Check this out
http://www.j-walk.com/ss/excel/tips/tip48.htm

[abhay_547]

Hi Dave,

Thanks a lot for your reply, I have gone through the link provided by you but this are things which have observed.
1) The macro present on the link is for printing the sheets using printer and not to pdf.
2) I have a created a userform where I can easily select the multiple sheets

It seems to be useful but can you please help me to incorporate on the same in my macro so that it meets my requirement. Did you look into the excel attachment which I had attached in my last post. I would suggest you to look into that file to get a better idea of my requirement.

Thanks a lot for your help in advance.

[davesexcel]

I am not sure what you are asking for, when I click the button I do notice that your "end if" is placed after the error handler
You have not indicated what the problem is.
The code in your post is not in the userform

[abhay_547]

Hi Dave,

Thanks a lot for your reply, I have placed Print to Pdf code in module3. I want an help to incorporate the code in the background of my code. For example someting like.

For each sheet selected in "lstProcess" listbox of my userform the macro should run the macro "PrintToPDF_MultiSheetToOne_Early" present in Module3 when user will click on the "Print to PDF" command button on userform.

Here is the sample code :

Sub test ()
Dim wsht As Worksheet
  For Each wsht In lstProcess ' Listbox
    Application.Run "PrintToPDF_MultiSheetToOne_Early"
  Next wsht

Note : All Worksheets should be printed to one pdf if "Checkbox1" .i.e. "Combine to One file" is checked using "PrintToPDF_MultiSheetToOne_Early" if the checkbox1 is unchecked then it should run the "PrintToPDF_MultiSheet_Early" macro to print each worksheet selected in lstProcess listbox to a separate pdf and save it on the path selected in the Textbox1 on the userform.



Thanks a lot for your help in advance.