Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009

    Unanswered: Print to PDF using PDFCreator as Printer

    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
        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"
                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
        'Combine all PDFs into a single file and stop the printer
        With pdfjob
            .cPrinterStop = False
        End With
        'Wait until PDF creator is finished then release the objects
        Do Until pdfjob.cCountOfPrintjobs = 0
        MsgBox ("The PDF has been successfully created as " & sPDFName)
        Sleep 1000
        Set pdfjob = Nothing
    End Sub
    Attached is my macro file.

    Thanks a lot for your help in advance.
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2009

    Print to PDF using PDFCreator as Printer

    Hi All,

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

    Thanks a lot for your help in advance.

  3. #3
    Join Date
    Nov 2010
    thank you for sharing! this would be very helpful with my office works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts