PDFCreator printing pages out of order

[Rich6S]

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

[Ken Puls]

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.)

[Rich6S]

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:

'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

[Ken Puls]

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:

'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,

[Rich6S]

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

[Ken Puls]

Great Richard, glad to hear it!