• Printing Worksheets To A PDF File (Using Early Binding)

    Introduction:
    This article contains code examples to print worksheets to PDF files.

    These code examples are built for PDFCreator, an open source PDF writer utility. Unlike Adobe Acrobat and CutePDF, which both require pro versions to create PDF's via code, PDFCreator is completely free! Download PDF Creator from Sourceforge here. Please note that this code will NOT work with Adobe Acrobat.

    It should also be noted that each of the examples in this section use an Early Bind. If you are not familiar with the difference between Early and Late Binding, please read our article on Early vs Late binding.

    Routines Included In This Article:
    • Print a Single Worksheet to a PDF File
    • Print Multiple Worksheets to Multiple PDF Files
    • Print Multiple Worksheets to a Single PDF File
    • Print Selected Worksheets to Multiple PDF Files
    • Print Specified Worksheets to a Single PDF File

    Versions Tested:
    These routines were originally developed using PDFCreator 0.9.1 (GPLGhostscript.exe download package) on Windows XP Pro (SP2). The current versions below contain numerous improvements and were fully tested using PDFCreator 1.2.0 on Windows 7 Ultimate x64 and PDFCreator 1.2.3 on Windows 8 Professional x64. Excel versions tested include:
    • Excel 2003
    • Excel 2007
    • Excel 2010 (32 bit)
    • Excel 2010 (64 bit)
    • Excel 2013 (32 bit)
    • Excel 2013 (64 bit)

    NOTE: Before you "go it alone" with trying to adapt any of these routines, you may want to read this article, which shares some of the idiosyncrasies discovered in the development of the PDFCreator code samples.

    Print a Single Worksheet to a PDF File:
    Code:
    Option Explicit
    Sub PrintToPDF_Early()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Print to PDF file using PDFCreator
    '   (Download from https://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 = "testPDF.pdf"
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    
        '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
    Print Multiple Worksheets to Multiple PDF Files:
    Code:
    Option Explicit
    Sub PrintToPDF_MultiSheet_Early()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Print to PDF file using PDFCreator
    '   (Download from https://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 lSheet As Long
        Dim bRestart As Boolean
    
        'Activate error handling and turn off screen updates
        On Error GoTo EarlyExit
        Application.ScreenUpdating = False
        Set pdfjob = New PDFCreator.clsPDFCreator
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    
        '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
    
        For lSheet = 1 To ActiveWorkbook.Sheets.Count
            'Check if worksheet is empty and skip if so
            If Not IsEmpty(Sheets(lSheet).UsedRange) Then
                With pdfjob
                    '/// Change the output file name here! ///
                    sPDFName = "testPDF" & Sheets(lSheet).Name & ".pdf"
                    .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
                Worksheets(lSheet).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 moving on
                'Important:  Counter must reach zero or hangs on next iteration
                Do Until pdfjob.cCountOfPrintjobs = 0
                    DoEvents
                Loop
            End If
        Next lSheet
        
    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
    Print Multiple Worksheets to a Single PDF File:
    Code:
    Option Explicit
    Sub PrintToPDF_MultiSheetToOne_Early()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Print to PDF file using PDFCreator
    '   (Download from https://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 lSheet As Long
        Dim lTtlSheets As Long
        Dim bRestart As Boolean
    
        '/// Change the output file name here! ///
        sPDFName = "Consolidated.pdf"
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    
        '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
        lTtlSheets = Application.Sheets.Count
        For lSheet = 1 To Application.Sheets.Count
            On Error Resume Next 'To deal with chart sheets
            If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
                Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
            Else
                lTtlSheets = lTtlSheets - 1
            End If
            On Error GoTo EarlyExit
        Next lSheet
    
        'Wait until all print jobs have entered the print queue
        Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
            DoEvents
        Loop
    
        'Combine all PDFs into a single file and stop the printer
        With pdfjob
            .cCombineAll
            .cPrinterStop = False
        End With
    
        '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
    Print Specified Worksheets to a Multiple PDF Files:
    Code:
    Option Explicit
    Sub PrintToPDF_SpecifiedSheetsToMulti_Early()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Print to PDF file using PDFCreator
    '   (Download from https://sourceforge.net/projects/pdfcreator/)
    '   Designed for early bind, set reference to PDFCreator
        Dim pdfjob As PDFCreator.clsPDFCreator
        Dim sPDFName As String
        Dim sPDFPreface As String
        Dim sPDFPath As String
        Dim sSheetsToPrint As String
        Dim sSheets() As String
        Dim lSheet As Long
        Dim bRestart As Boolean
    
        'Activate error handling and turn off screen updates
        On Error GoTo EarlyExit
        Application.ScreenUpdating = False
        Set pdfjob = New PDFCreator.clsPDFCreator
        
        '/// Set the preface for the PDF file name and the file path here! ///
        sPDFPreface = "Report-"
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    
        '/// Record the sheets you want to print here! ///
        '/// Use sheet names separated by commas only  ///
        sSheetsToPrint = "Sheet1,Sheet3"
    
        '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
    
        'Split the sheets into an array
        sSheets() = Split(sSheetsToPrint, ",")
        
        For lSheet = LBound(sSheets) To UBound(sSheets)
            'Check if worksheet is empty and skip if so
            If Not IsEmpty(Application.Sheets(sSheets(lSheet)).UsedRange) Then
                With pdfjob
                    '/// Change the output file name here! ///
                    sPDFName = sPDFPreface & Sheets(sSheets(lSheet)).Name & ".pdf"
                    .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
                Application.Sheets(sSheets(lSheet)).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 moving on
                'Important:  Counter must reach zero or hangs on next iteration
                Do Until pdfjob.cCountOfPrintjobs = 0
                    DoEvents
                Loop
            End If
        Next lSheet
        
    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
    Print Specified Worksheets to a Single PDF File:
    Code:
    Option Explicit
    Sub PrintToPDF_SpecifiedSheetsToOne_Early()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Print to PDF file using PDFCreator
    '   (Download from https://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 sSheetsToPrint As String
        Dim sSheets() As String
        Dim lSheet As Long
        Dim lTtlSheets As Long
        Dim bRestart As Boolean
    
        '/// Change the output file name here! ///
        sPDFName = "Consolidated.pdf"
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
        
        '/// Record the sheets you want to print here! ///
        '/// Use sheet names separated by commas only  ///
        sSheetsToPrint = "Sheet1,Sheet3"
        
        '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
        
        'Split the sheets into an array
        sSheets() = Split(sSheetsToPrint, ",")
    
        'Delete the PDF if it already exists
        If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)
    
        'Print the document to PDF
        For lSheet = LBound(sSheets) To UBound(sSheets)
            On Error Resume Next 'To deal with chart sheets
            If Not IsEmpty(Application.Sheets(sSheets(lSheet)).UsedRange) Then
                Application.Sheets(sSheets(lSheet)).PrintOut copies:=1, ActivePrinter:="PDFCreator"
                lTtlSheets = lTtlSheets + 1
            End If
            On Error GoTo EarlyExit
        Next lSheet
    
        'Wait until all print jobs have entered the print queue
        Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
            DoEvents
        Loop
    
        'Combine all PDFs into a single file and stop the printer
        With pdfjob
            .cCombineAll
            .cPrinterStop = False
        End With
    
        '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
    This article was also published as the August 2006 Office Newsletter at Cimaware.com, makers of ExcelFIX. (Unfortunately, the history of these newsletters is no longer available on their site.)

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 28 Comments
    1. roll4ever's Avatar
      roll4ever -
      Quote Originally Posted by roll4ever View Post
      Hello Ken,


      I have the same question. Could someone please give me the answer?

      Thank you very much in advance.
      Solved it with a work around.
    1. Peter's Avatar
      Peter -
      Hi Ken,

      I've been using your VBA code "Printing Worksheets to a PDF File (Using Eearly binding)", the Print Multiple Worksheets to Multiple PDF Files version, with Excel 2002. Extremely useful; thanks for writing it. No problems until I went for the recent PDFCreator upgrade to v. 2.1 - and then the code won't run. The error message

      Compile error:
      Can't find project or library

      pops up, and the code is shown with "pdfjob As PDFCreator.clsPDFCreator" highlighted.

      I don't use PDFCreator for any other purpose so could go back to the earlier version, but wonder if this is something that's simple to fix?

      Thanks,
      Peter
    1. mcdobald's Avatar
      mcdobald -
      Hi Ken,

      I've been using this code a couple of months now without any problem. Great code! But using the code on my new laptop at work will also open Autodesk Navisworks Simulate program. I checked the code and found out that the line:
      "If pdfjob.cStart("/NoProcessingAtStartup") = False Then" opens the Autodesk program.
      Is there a way to resolve this? I tried the same code on my co-worker's laptop without Autodesk programs and it's perfectly working. My PDFCreator is 1.7.3 version.

      Thanks,
      McDobald
    1. vince47's Avatar
      vince47 -
      Hi Ken
      I've been using your code for a couple of years now and whenever I clicked the print command button that contains your code it always worked to perfection. From time to time I received that a recent version of PDFCreator is available and if I wanted to downloaded. I always clicked "No" until last week then I decided that after a couple of years there must have been great improvements.
      That was the worse decision I made. I downloaded version 2.2.2 and while installing it gave me several errors like "PrinterHelper.exe" - "Unable to find version of the runtime to run this application" and "SetupHelper.exe" - "Unable to find version of the runtime to run this application".
      In the "Reference" now shows "MISSING:PDFCreator"
      I uninstalled and reinstalled with virus protection both "ON" and "Disabled"
      In the program console panel the size of PDFCreator shows a blank and 105MB for PDF Architect 4.
      I am using Excel 2007 under Windows 7
      I appreciate any help a.s.a.p. as I use this program frequently.
      Thanking in anticipation
      regards
      Vince
    1. jimmydd123's Avatar
      jimmydd123 -
      Not sure how this works - i am brand new to forums - so suggest if i need to do this different.

      Anyway, i think i have a simple request. I have written a VBA routine that prints Autocad drawings to PDF. I have it working beautifully except my 36x24 drawings when printed to pdf come out 24 wide and 36 tall. In other words everything is correct but the drawing is turned 90 degrees. I want to simply rotate the pdf before the routine is done. I have not been able to find a VBA example for this nor do i know the object or method or whatever pdfcreator setting i need to use. i am assuming its a line something like job.cOption ("page rotation") = 90 degrees - or something along those lines. Anyone know the code?
    1. Pecoflyer's Avatar
      Pecoflyer -
      You'd better start a thread of your own ( with eventually a link to this one) to get answers
    1. Jón Valur's Avatar
      Jón Valur -
      I can not get the code to work. I have made some modifications to it to try to get it to work but I always get the same results.

      PDFCreator will not take the Autosave options. No matter what I try either way it uses the standard autosave naming if I mark in PDFcreator to use autosave or if I do not mark by autosave it brings up a pop up window asking for filename etc.

      Is there something I am missing. I have the server option installed. Exel 2000.

      Code:
      Option Explicit
      Sub PrintToPDF_Early()
      'Author       : Ken Puls
      'Macro Purpose: Print to PDF file using PDFCreator
      '   (Download from )
      '   Designed for early bind, set reference to PDFCreator
      
      
      
      
      'Breytt og ađlagađ af Jón Valur
      
      
          Dim job As PDFCreator.clsPDFCreator
          Dim job2 As PDFCreator.clsPDFCreatorOptions
          Dim name As String
          Dim Path As String
          Dim bRestart As Boolean
      
      
          Set job = New PDFCreator.clsPDFCreator
          Set job2 = New PDFCreator.clsPDFCreatorOptions
          
         'File named after cell C3
          name = Range("C3").Value & ".pdf"
          Path = "C:\sk\" 
          
          'Activate error handling and turn off screen updates
          On Error GoTo EarlyExit
          Application.ScreenUpdating = False
          
          'Check if PDFCreator is already running and attempt to kill the process if so
          Do
              bRestart = False
              If job.cStart("/NoProcessingAtStartup") = False Then
              
                  'PDF Creator is already running.  Kill the existing process
                  Shell "taskkill /f /im PDFCreator.exe", vbHide
                  DoEvents
                  Set job = Nothing
                  bRestart = True
                  
              End If
          Loop Until bRestart = False
              
          'Delete the PDF if it already exists
          If Dir(Path & name) = name Then Kill (Path & name)
        
              'Assign settings for PDF job (PDF Creator)
          With job2
              .UseAutosave = 1
              .UseAutosaveDirectory = 1
              .AutosaveDirectory = Path
              .AutosaveFilename = name
              .AutosaveFormat = 0    ' 0=PDF, 1=PNG, 2=JPG, 3=BMP, 4=PCX, 5=TIFF, 6=PS, 7= EPS, 8=ASCI
                 
          End With
          
           With job
              .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 job.cCountOfPrintjobs = 1
                DoEvents
          Loop
          job.cPrinterStop = False
          'Wait until the file shows up before closing PDF Creator
          Do
              DoEvents
          Loop Until Dir(Path & name) = name
          
      Cleanup:
          'Release objects and terminate PDFCreator
          Set job = 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
    1. jimmydd123's Avatar
      jimmydd123 -
      Hi Ken
      Let me first say that i appreciate all the help and code you give me and everyone else on this list. i was not very familiar with VBA coding when i first left comments here. Since then i feel a little more confident. i have my routine working quite will actually, but i cheated to do so and want to ask you for a clue or a comment as to what problems i might have by doing what i did. in the section "cleanup:" section of your code my routines always go to the on error and exit the sub. this has been ok in my Excel routine becasue that is the end of the sub anyway. but i also wrote an Autocad VBA routine using this code and there are things for the routine to do after this section and therefore the routine just hangs. in both instances i just commented out what lines were not working and so far so good. Actually i commetned out the cleanup section the early exit section and the line above that initiates the early exit code. Any hints why it might error right before the on error line of code?

      it gets through this line:

      Set pdfjob = Nothing

      So I assume this line is where it is erroring:

      Shell "taskkill /f /im PDFCreator.exe", vbHide

      thanks again for your help
      jim
  • MVP Logo
  • Recent Forum Posts

    Pecoflyer

    Sum only numeric columns with PQ

    @Nick


    Please do not hijack existing threads but rather create a new one with eventually a link to any threads of interest. Older threads...

    Pecoflyer Today, 07:28 AM Go to last post
    Kolyu

    Power Query Challenge 5

    The way you replaced null values using = Table.ReplaceValue(Source,null,each _[From],Replacer.ReplaceValue,{"To"}) was interesting
    ...

    Kolyu Today, 07:06 AM Go to last post
    Tobriand

    Cube formulae

    So cube functions are horrible, and thankfully it's been a while since I used them. That said, I think there are two issues you're running into:
    ...

    Tobriand Yesterday, 11:59 PM Go to last post
    Bill Szysz

    Sum only numeric columns with PQ

    No problem, Nick :-))
    = Table.AddColumn(YourPreviousStep, "Sum", each List.Sum(List.Transform(Record.ToList(_), each if _ is number then...

    Bill Szysz Yesterday, 09:52 PM Go to last post
    Nick_M

    Power Query Challenge 5

    My solution
    ...

    Nick_M Yesterday, 08:46 PM Go to last post