Solved: Print 2 pdf VBA error

[Lionelnz]

The code below works fine, creates the PDF file but does not close app PDFCreator. The procedure hangs (no error msg) & I have to go into task manager to close app & then of course it is halted on the line "pdfjob.cClose". Anyone got any ideas??? The original code author
Ken Puls at "w w w . excelguru .ca" TIA Lionel downunda!
BTW I am using XL2002 with XP Prof SP3

[vba]
Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub PrintToPDF_Late_Lionels()
'testing May 22
'Modified 22/02/09 for Civic Invoice 09.xls
'Macro Purpose: Print to PDF file using PDFCreator
' Designed for late bind, no references req'd
Dim pdfjob As Object
'Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String

'/// Change the output file name here! ///
Application.ScreenUpdating = False

With ActiveWorkbook
sPDFName = "Civic Invoice " & Range("F5") & ".pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
End With
'Check if worksheet is empty and exit if so
If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
With pdfjob
If .cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "PrtPDFCreator"
Exit Sub
End If
.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
ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False
'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
'added as an experiment to close PDFCreator
'pdfjob.cPrinterStop = True


Sleep 1000
pdfjob.cClose
'Needed to close PDFCreator

Set pdfjob = Nothing

Application.ScreenUpdating = True
End Sub
[/vba]

[mdmackillop]

Crosspost.
I refer you back to Ken's site.

[Lionelnz]

Crosspost.
I refer you back to Ken's site.

Thank you for this however I do have couple queries about the comments made on Ken's site about crossposting.

First is most average users (non-professional) of forums do not know which forums are linked.

As for putting in a link, my rating on this forum does not allow me me to insert hyperlinks.

When I have resolved a (usually difficult) problem I post my solution back to various forums I belong to.

Now I know VBAExpress is linked to VB Forums I will be mindful & not cross-post & as Ken says on his site, I am trying to resolve a difficult problem based on his original code with out a happy ending.

Just now when I tried to submit this response I had to remove the links originally supplied by MD.

[Lionelnz]

This VBA problem has now been resolved & is posted on VB forum site linked above by MD as Crosspost.

[mdmackillop]

These forums are not "linked" and that really is an irrelevancy. To quote

What we are asking for, however, is the courtesy of knowing about it. Why? Well it's kind of simple really. If you post on several boards, you have several groups of people working on the same issue, without necessarily knowing what the others are doing.
Ask yourself this: How would you feel if you worked on an answer for an hour, only to find out that fifteen minutes after you started, someone already had a solution they accepted? I'd be willing to bet you'd be a little upset that you'd wasted your time.

[JONvdHeyden]

Here on MrExcel: http://www.mrexcel.com/forum/showthr...=1#post1948115

[Aussiebear]

As for putting in a link, my rating on this forum does not allow me me to insert hyperlinks.

Out of courtesy to other users on any forum, (VB, Mr Excel, VBA Express just to name a couple), you could still have made mention of the fact that you have posted on other forums.

When I have resolved a (usually difficult) problem I post my solution back to various forums I belong to.

Please post your solution here. You went to the trouble to raise the issue here, and indeed posted code here as part of your initial post, so why not post your solution here as well.

Please note that whilst its a big world out there, members of forums such as ours, often frequent a number of similar forums, seeking to update their understanding and knowledge, so it quickly becomes common knowledge of what issues are currently being raised, and more importantly by whom. Serial cross-posters and/or those who do not have respect for other contributors, soon find that their requests are ignored. If you wish to discuss this issue in greater detail, please PM me.

[Lionelnz]

Yes I had posted the same problem a few months ago without a result so I tried again. However with a little prompting from another member I have resolved the problem & posted the solution.

From MY perspective when I try to help others with a problem I usually learn a lot more as i am about to do privately, help another member from a forum with VBA, but the best bit is that I do not know how to solve it YET so I will learn heaps.

Enuff said & I will bear in mind all your comments in future.

[Lionelnz]

Please post your solution here. You went to the trouble to raise the issue here, and indeed posted code here as part of your initial post, so why not post your solution here as well.

First of all I apologise for all the inconvenience I caused & will stick to one or two forums as suggested & if then no result will try others making sure I let members know I have already posted without success.

here is my solution -

If I place the sleep 1000 value in the 2nd loop it obviously holds open PDFCreator long enough to check zero printjobs then closes PDFcreator.

The problem was with the 2nd Loop,. Needed sleep value before looping.

[vba]'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
'added to allow PDFCreator check 0 printjobs
Sleep 1000
Loop[/vba]
Below is the correct code -


[vba] Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub PrintToPDF_Late_Lionels()
'testing May 22
'Modified 22/02/09 for Civic Invoice 09.xls
'http : //ww w.mrexcel.com/forum/showthread.php?t=345461
'Author : Ken Puls (w w w.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from http : //sourceforge.net/projects/pdfcreator/)
' Designed for late bind, no references req'd
Dim pdfjob As Object
'Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String

'/// Change the output file name here! ///
Application.ScreenUpdating = False

With ActiveWorkbook
sPDFName = "Civic Invoice " & Range("F5") & ".pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
End With
'Check if worksheet is empty and exit if so
If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
With pdfjob
If .cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "PrtPDFCreator"
Exit Sub
End If
.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
ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False
'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
'added to allow PDFCreator check 0 printjobs
Sleep 1000
Loop

pdfjob.cClose
'Needed to close PDFCreator

Set pdfjob = Nothing

Application.ScreenUpdating = True
End Sub

[/vba]

Once again sincere apologies to all & it won't happen again.

[mdmackillop]

Thanks Lionel.