Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

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

  1. #1
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
           Call PrintToPDF_Early
        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?


  2. #2
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    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:
    MsgBox "There was an error encountered.  PDFCreator has" & vbCrLf & _
               "has been terminated.  Please try again.", _           vbCritical + vbOKOnly, "Error"
    and see what the error message is telling you, then work from there?

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by millz View Post
    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:
    Sub PrintToPDF_Early()

    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

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    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


  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by NBVC View Post
    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.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    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.


  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    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.

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    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! ///
        sPDFName = myFName
        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 by NBVC; 2013-08-20 at 02:29 PM.


  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •