Print 2 pdf VBA error

[lionelnz]

Hi all. I have this VBA using 3rd party PDF printer to print a sheet PDF & it was working fine but for some reason the code does not close the PDF printer app. The problem lines are

'Sleep 1000
    pdfjob.cClose
    'Needed to close PDFCreator
    Sleep 1000
    Set pdfjob = Nothing

Can anyone see what needs fixing? I have experimented where to to put the "Sleep 1000" value maybe I need more?

 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://www.mrexcel.com/forum/showthread.php?t=345461
'Author       : Ken Puls (www.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
    Loop
    pdfjob.cClose
    'Needed to close PDFCreator
    Sleep 1000
    Set pdfjob = Nothing
    
    Application.ScreenUpdating = True
End Sub

 

[lionelnz]

Re: Print 2 pdf VBA error - Resolved

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.

Below is the correct code -

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://www.mrexcel.com/forum/showthread.php?t=345461
'Author       : Ken Puls (www.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

 

[Jon von der Heyden]

For reference, cross posted here: http://vbaexpress.com/forum/showthread.php?t=26843

 

[newyorkhcm]

re: pdf creator

Just get this program : PDF Creating v2.0

PDF Creator: create PDF documents easily

Download:
http://www.mediafire.com/download.php?dtnyt5zgez1

 

[lionelnz]

For reference, cross posted here: http://vbaexpress.com/forum/showthread.php?t=26843

Hi Jon. I have requested help from a moderator re-my indiscretions, see below. I assure you & the board this will not happen in future.

"I have been guilty in the past for being a serial cross-poster only because I did not fully understand the implications of what a potential time waster this was for people generously giving their time & knowledge to provide solutions.

What is the best way to make a general apology for my indiscretion to the board and reassure the board(s) that my cross-posting has stopped?"

 

[Jon von der Heyden]

Hi Lionel

I'm sure there's no need for an apology.

I provided the link to the cross-post only so that fellow members could see what other potential solutions are already on offer. Although some members disagree with cross-posting there are also many of us who do not object just as long as you let us know where you have cross posted (by posting a URL to the cross-post).