Printing to PDF using Ken's "Print a Single Worksheet to a PDF File" code

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
1
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
I am trying to use Ken's Printing Worksheet to PDF file using PDFCreator found here: http://www.excelguru.ca/content.php?161

I am trying to use this in a loop.

What I am doing is running a query multiple times. Each time the query runs, I want to print to a new pdf. I keep getting the error"

"There was an error encountered. PDFCreator has been terminated. Please try again."

which is Ken's error control message.... and in the end it only prints the last query of the list to pdf.

My loop containing a call to Ken's code looks something like:

Code:
For i = 2 To x
    PartID = Sheets("Sheet2").Range("A" & i)
    With Sheets("Sheet1")
        .Activate
        sSql = "SELECT ... "
        sSql = sSql & "FROM ... "
        sSql = sSql & "WHERE ... "
        With .Range("D6").QueryTable
            .Connection = sConn
            .Sql = sSql
            .Refresh BackgroundQuery:=False
        End With
    [B]   Call PrintToPDF_Early[/B]
    End With
    
Next i

The PrintToPDF code I am trying to use is the Print a Single Worksheet to a PDF File version

Am I putting the call in the right place? Or is there something else I need to consider?
 
I don't know exactly what causes the error, but I can see a problem. In the "PrintToPDF_Early" sub, there is a line where you specify the file name to be saved as, and this is hard coded and not dynamic. Meaning, even if there are no errors encountered for your printing of multiple worksheets, only 1 PDF file will be created (and overwritten how many times how many worksheets you are printing). You may want to modify the sub a little to take in a parameter (your sheets' name, for example) and save them into different file names.

One way to start debug is to add a
Code:
MsgBox Err.number & " " & Err.Description
before or after here
Code:
[COLOR=#3E3E3E]MsgBox "There was an error encountered.  PDFCreator has" & vbCrLf & _[/COLOR]
           "has been terminated.  Please try again.", _           vbCritical + vbOKOnly, "Error"

and see what the error message is telling you, then work from there?
 
In the "PrintToPDF_Early" sub, there is a line where you specify the file name to be saved as, and this is hard coded and not dynamic. Meaning, even if there are no errors encountered for your printing of multiple worksheets, only 1 PDF file will be created (and overwritten how many times how many worksheets you are printing). You may want to modify the sub a little to take in a parameter (your sheets' name, for example) and save them into different file names.
millz is quite right, so to put into practice change the:
Code:
[FONT=arial]Sub PrintToPDF_Early()[/FONT]

to:
Code:
Sub PrintToPDF_Early(myFName as string)
Change:
Code:
sPDFName = "testPDF.pdf"
to:
Code:
sPDFName = myFName
(no Call)

In the calling sub, change:
Code:
Call PrintToPDF_Early
to say:
Code:
PrintToPDF_Early "PartID " & PartID
 
Hi guys, I did as suggested and got similar results. It only prints the last pdf and errors. It won't display the result of

Code:
  MsgBox Err.Number & " " & Err.Description
 
Hi guys, I did as suggested and got similar results. It only prints the last pdf and errors.
So what name did the pdf end up with? Do you have more than 1 PartId? There should be as many .pdf files as there are PartIDs. I can't check the code without installing pdfCreator and reconstructing what i imagine to be on your sheet.
 
Hi p45cal,

It seems a bit random. The first time I ran the code, I got 2 pdfs, which would be correct. After that I always get 1 pdf... and sometimes it's named after the first partid, and sometimes after the 2nd partid.

My code uses a For/Next look to get the PartID from a range in Sheet2, then it runs the SQL statements using the PartID as a parameter, the query returns the results to sheet1, then I want to PDF that sheet using the current PartID. Then it should go to next cell and retrieve the next PartID and repeat the process.

I have a Debug.Print for the count and for the PartIDs and they return expected results.
 
Well if you can get me (a version of) your workbook say by attaching it here (I'm not sure if that's possible - so perhaps box.net dropbox etc.) I will install pdfCreator here and see what's going on. If the file contains sensitive info and you don't want it in the public domain, Private Message me here for a private email adress you can send it to.
 
It contains SQL statements that you would need to access my ERP database for... which obviously you can't....

If you can somehow create a looping situation where you make a change to sheet1, based on a variable in Sheet2, and based on the change, rename the pdf on each cycle of the loop and create the pdf, then perhaps you can duplicate to a certain extent, my issue.

I can post my actual codes here, including the updates you suggested so you can see if there is a problem I only removed the SQL statements....

Code:
Sub batch_print()
Dim i As Long
Dim x As Long
Dim PartID As String
Dim sConn As String
Dim sSql As String

Application.ScreenUpdating = False
ActiveSheet.Unprotect

On Error Resume Next

x = Application.CountA(Sheets("sheet2").Range("A:A"))
sConn = "ODBC;..."

Debug.Print x

For i = 2 To x
    PartID = Sheets("Sheet2").Range("A" & i)
    Debug.Print PartID
    With Sheets("Sheet1")
        .Activate
        sSql = "SELECT... "
        sSql = sSql & "FROM ... "
        sSql = sSql & "WHERE ... "
        With .Range("D6").QueryTable
            .Connection = sConn
            .Sql = sSql
            .Refresh BackgroundQuery:=False
        End With
        If .Range("A7") = "" Then
            .Range("D2").Value = "No Bill Of Material Exists"
            .Range("D3").Value = ""
        Else
            .Range("D2").Value = "Part No. " & .Range("A7").Value
            .Range("D3").Value = .Range("B7").Value
        End If
        .Columns("D:D").ColumnWidth = 9
        .Columns("E:E").ColumnWidth = 15.14
        .Columns("F:F").ColumnWidth = 45
        .Columns("G:G").ColumnWidth = 9.71
        .Columns("K:K").ColumnWidth = 9.71
        ' Columns("H:J").ColumnWidth = 11.14
        PrintToPDF_Early "PartID " & PartID
        'ActiveWindow.SelectedSheets.PrintOut copies:=1, Collate:=True
    End With
     
Next i
     Application.ScreenUpdating = True
     ActiveSheet.Protect
End Sub

you will notice the commented

Code:
'ActiveWindow.SelectedSheets.PrintOut copies:=1, Collate:=True

this worked to print each re-run of the sheet properly.

Code:
Option Explicit
Sub PrintToPDF_Early(myFName As String)
'Author       : Ken Puls (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

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim bRestart As Boolean

    '/// Change the output file name here! ///
   [B] sPDFName = myFName[/B]
    sPDFPath = "X:\BOMs\"
    'Check if worksheet is empty and exit if so
    If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub

    '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") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Delete the PDF if it already exists
    If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)

    '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 the file shows up before closing PDF Creator
    Do
        DoEvents
    Loop Until Dir(sPDFPath & sPDFName) = sPDFName
    
  

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 has" & vbCrLf & _
               "has been terminated.  Please try again.", _
           vbCritical + vbOKOnly, "Error"
    Resume Cleanup
    
End Sub
 
Last edited:
Hey Vittorio,

The structure of the PrintToPDF_Early looks correct to me. I'm curious... if you step through the code does it work correctly, but manifest different results when run at full speed? If that's the case, then the issue is in this section:
Code:
 'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False

'Wait until the file shows up before closing PDF Creator
Do
DoEvents
Loop Until Dir(sPDFPath & sPDFName) = sPDFName

I've tried a variety of methods here to make this run smoothly, and what you have there is what I've found to be the most reliable... although most isn't always 100% it seems. Other alternatives are to add Application.Wait commands to delay the execution for a bit.

Maybe even add a DoEvents after the loop line?
 
Thinking about it some more, maybe even add a pause before the PDF routine is even called?

The issue I've typically seen is that Excel hasn't finished with the PDF in the previous iteration completely, or the system hasn't finished doing something, causing it to go out of sync. That should be impossible with VBA code though, unless PDF Creator shells something out I suppose...
 
Yes NBVC and Ken, it does seem to be hit-and-miss!
Still fiddling with it but in the interim, it'd be better to change:

Code:
PrintToPDF_Early "PartID " & PartID
to:
Code:
PrintToPDF_Early "PartID " & PartID [B]& ".pdf"[/B]
Yes Ken, the problems arise at full speed, not white stepping through the code.
 
Hi Ken,

Thanks a bunch. Your last suggestion seemed to hit it.

I added a Wait before printing in the main module (and 1 second is enought :) )

Code:
Application.Wait (Now + TimeValue("0:00:01"))
        PrintToPDF_Early "PartID " & PartID & .pdf"

Thanks again all of you.
 
Last edited:
Back
Top