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 (http://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 (http://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! :)