arimax926
02-18-2009, 08:34 AM
Hello,
I am trying to print multiple worksheets to a single PDF file using the VBA code by Ken Puls at www dot excelguru dot ca slash node slash 21 (I am running Excel 2007 using a test workbook with 2 worksheets with few words in them). This is the code:
VBA
Sub PrintToPDF_MultiSheetToOne_Early()
'Author : Ken Puls (site)
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from site)
' Designed for early bind, set reference to PDFCreator
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
[BREAKPOINT] 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
pdfjob.cClose
Set pdfjob = Nothing
End Sub
The main problem is that when the jobs are sent to the PDF printer, it doesn't seem to do the work expected. So, after some seconds I get the alert message "Microsoft Excel is waiting for another application to do an OLE function" and it repeats regularly, entering a never ending loop. PDFcreator is launched by the code (I can see it in the running applications) but I can't understand why it doesn't print the jobs and save the file. If I insert a breakpoint in the code (just before the first DO...LOOP cicle) and proceed step by step everything works fine! Any explanation?
Then a second question arises. PDFcreator seems not to use the page layouts I define for each worksheet. I read a previous post about this problem here (Page set up in PDF-Creator software), but I don't know if it applies to my problem and how to use the function "SetupPrint": what I have to pass to it? How to combine this code with the previous routine?
Sorry if my questions seem quite naive, but I am not a VBA expert, so I will appreciate very much any suggestion.
Thank you!
I am trying to print multiple worksheets to a single PDF file using the VBA code by Ken Puls at www dot excelguru dot ca slash node slash 21 (I am running Excel 2007 using a test workbook with 2 worksheets with few words in them). This is the code:
VBA
Sub PrintToPDF_MultiSheetToOne_Early()
'Author : Ken Puls (site)
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from site)
' Designed for early bind, set reference to PDFCreator
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
[BREAKPOINT] 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
pdfjob.cClose
Set pdfjob = Nothing
End Sub
The main problem is that when the jobs are sent to the PDF printer, it doesn't seem to do the work expected. So, after some seconds I get the alert message "Microsoft Excel is waiting for another application to do an OLE function" and it repeats regularly, entering a never ending loop. PDFcreator is launched by the code (I can see it in the running applications) but I can't understand why it doesn't print the jobs and save the file. If I insert a breakpoint in the code (just before the first DO...LOOP cicle) and proceed step by step everything works fine! Any explanation?
Then a second question arises. PDFcreator seems not to use the page layouts I define for each worksheet. I read a previous post about this problem here (Page set up in PDF-Creator software), but I don't know if it applies to my problem and how to use the function "SetupPrint": what I have to pass to it? How to combine this code with the previous routine?
Sorry if my questions seem quite naive, but I am not a VBA expert, so I will appreciate very much any suggestion.
Thank you!