I was printing specified sheets from Exce to pdf using PDFCreator automated with Excel's VBA. It worked flawlessly (thanks to the authors and community).
I've now added to the code the inclusion of an already-existing PDF file along with the sheets. When I print this external PDF along with the sheets, Adobe Reader opens (just the application, not the document), and Adobe Reader must be closed manually for the print job to continue. After closing Reader, it all works great
This ONLY happens when I include a pdf file to be printed along with the sheets in my VBA code.
My PDFCreator printer does NOT have options to set for "view results" or "View Adobe PDF results", or any of those options I've seen posed as solutions to this.
Below is the VBA code I'm using on a Windows XP machine with the latest version of PDFCreator. Any help on this would be GREATLY appreciated. I love how easy PDFCreator has been to automate via Excel and VBA, but this new twist has the users demanding I move to a different solution if I can't resolve it. Your time and assistance is very much appreciated.
Code:
Dim pdfjob As PDFCreator.clsPDFCreator
Dim DefaultPrinter As String
Dim sPDFName As String
Dim sPDFPath As String
Dim CurvePath As String
Dim AcroApp As Acrobat.CAcroApp
Dim DefPrntr As String
DefPrntr = Application.ActivePrinter 'get default printer before starting in case need to set back again
sPDFName = InputBox("Enter desired File Name:", "SAVE AS...")
sPDFPath = "C:\\"
CurvePath = GetFilePath("C:\\") 'this is path and file name of existing PDF to include with sheets
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
DefaultPrinter = .cDefaultPrinter
.cDefaultPrinter = "PDFCreator"
End With
pdfjob.cPrintFile (CurvePath)
Application.Sheets("Cover").PrintOut copies:=1, ActivePrinter:="PDFCreator"
Application.Sheets("Report").PrintOut copies:=1, ActivePrinter:="PDFCreator"
Application.Sheets("Final").PrintOut copies:=1, ActivePrinter:="PDFCreator"
Application.Sheets("Raw").PrintOut copies:=1, ActivePrinter:="PDFCreator"
'Wait until all print jobs have entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 5
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
'above wait wasn't long enough, pdf not getting created; added 2-second delay
Application.Wait Now + TimeValue("00:00:02")
pdfjob.cClose
Set pdfjob = Nothing
'If had to change default printer, change it back
Application.ActivePrinter = DefPrntr
DefaultPrinter = DefPrntr