Results 1 to 6 of 6

Thread: PDFCreator printing pages out of order

  1. #1

    PDFCreator printing pages out of order



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Ken Puls,

    I use your code to print a multiple-page Excel file into one pdf file using PDFCreator. It works
    but the page number is not in the original order. The file is a financial report, the page order is important. Is there any way to keep pages in order?

    Thanks for your help.
    Richard

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,089
    Articles
    79
    Blog Entries
    14
    Hi Richard,

    So are you using this routine? http://www.excelguru.ca/node/21#MultiSingle

    Have you made any changes to it at all? I'm just trying to make sure we know exactly what we're working with before we start testing. (If you have modified the code at all, maybe post it here so we can see what you're working with.)

    PS: I've split this into a new thread, as it keeps things on focus for each person. (The other thread can stay on the original topic, and we can dedicate this one to your specific issue.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    Question

    Hi Ken,

    Yes, I used Code 21 #MultiSingle and moldified it a little just for the workbook and sheet names, all others keep as they are. The code is as below:


    Code:
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Print to PDF file using PDFCreator
    '   (Download from http://sourceforge.net/projects/pdfcreator/)
    '   Designed for early bind, set reference to PDFCreator
    Sub PDF_AutoCreate(ByVal Wbk As Workbook, RptOutName As String, sRptTime As String, iShts As Integer)
     
    Dim sPath As String
    Dim i As Integer, j As Integer, aSheets As String
    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim lSheet As Long
    Dim lTotlSheets As Long
    Dim bRestart As Boolean
     
    sPath = ThisWorkbook.Sheets("Parameter").[Full_Path_PDF].Value
    RptOutName = Replace(RptOutName, ".xlsx", ".pdf")       'Add Extention for pdf file
     
        '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") = sPath
            .cOption("AutosaveFilename") = RptOutName
            .cOption("AutosaveFormat") = 0    ' 0 = PDF
            .cClearCache
        End With
     
        'Delete the PDF if it already exists
        If Dir(sPath & RptOutName) = RptOutName Then Kill (sPath & RptOutName)
     
        'Print the document to PDF
        lTotlSheets = iShts - 1
        For lSheet = 1 To iShts - 1
            On Error Resume Next 'To deal with chart sheets
            If Not IsEmpty(Wbk.Sheets(lSheet).UsedRange) Then
                Wbk.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
            Else
                lTotlSheets = lTotlSheets - 1
            End If
            On Error GoTo EarlyExit
        Next lSheet
     
        'Wait until all print jobs have entered the print queue
        Do Until pdfjob.cCountOfPrintjobs = lTotlSheets
            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(sPath & RptOutName) = RptOutName
     
    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" & vbCrLf & _
               "has been terminated.  Please try again.", _
               vbCritical + vbOKOnly, "Error"
        Resume Cleanup
    End Sub

    The page 10, or 11, or 12 will go before page 7. Every time will have at leas one page in wrong order.

    Thanks.
    Richard

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,089
    Articles
    79
    Blog Entries
    14
    I haven't tested this, but I think it should work. I've added a loop to check the count of worksheets in the print queue before moving on to the next sheet. That should slow it down to ensure that things don't go out of order:

    Code:
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Print to PDF file using PDFCreator
    '   (Download from http://sourceforge.net/projects/pdfcreator/)
    '   Designed for early bind, set reference to PDFCreator
     
    Sub PDF_AutoCreate(ByVal Wbk As Workbook, RptOutName As String, sRptTime As String, iShts As Integer)
     
    Dim sPath As String
    Dim i As Integer, j As Integer, aSheets As String
    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim lSheet As Long
    Dim lTotlSheets As Long
    Dim bRestart As Boolean
     
    sPath = ThisWorkbook.Sheets("Parameter").[Full_Path_PDF].Value
    RptOutName = Replace(RptOutName, ".xlsx", ".pdf")       'Add Extention for pdf file
     
        '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") = sPath
            .cOption("AutosaveFilename") = RptOutName
            .cOption("AutosaveFormat") = 0    ' 0 = PDF
            .cClearCache
        End With
     
        'Delete the PDF if it already exists
        If Dir(sPath & RptOutName) = RptOutName Then Kill (sPath & RptOutName)
     
        'Print the document to PDF
        lTotlSheets = iShts - 1
        For lSheet = 1 To iShts - 1
            On Error Resume Next 'To deal with chart sheets
            If Not IsEmpty(Wbk.Sheets(lSheet).UsedRange) Then
                Wbk.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
                
    '<-- Code added to check if sheet has entered queue before moving on
                'Wait until job has entered PDF queue
                Do Until pdfjob.cCountOfPrintjobs = lSheet
                    DoEvents
                Loop
    '-- Code modification ends -->
                
            Else
                lTotlSheets = lTotlSheets - 1
            End If
            On Error GoTo EarlyExit
        Next lSheet
     
        'Wait until all print jobs have entered the print queue
        Do Until pdfjob.cCountOfPrintjobs = lTotlSheets
            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(sPath & RptOutName) = RptOutName
     
    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" & vbCrLf & _
               "has been terminated.  Please try again.", _
               vbCritical, vbOKOnly, "Error"
        Resume Cleanup
     
    End Sub
    Let me know if that helps,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5

    Red face Test result

    Hi Ken Plus,

    I insert the code and tested the module, it works perfectly. Every page is in order now though the printing lasts longer than before. Thank you very much!

    Regards,
    Richard

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,089
    Articles
    79
    Blog Entries
    14
    Great Richard, glad to hear it!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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