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

Thread: merging multiple PDF files into a single PDF file via VBA macro

  1. #1

    merging multiple PDF files into a single PDF file via VBA macro



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

    I have an excel file in which I have a macro that creates a series of PDF files via the ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ command.

    I end up with files such as main file, temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8 and temp9. What I would like to do now is combine these files into a single file with the same name as the main file. I am attempting to do this with PDFCreator version 1. I can do it just fine manually, but I would like to be able to do it via the same macro that creates the files. So far, I have added PDFCreator as a reference in the tools, reference in VBA. I then go into object browser and can see the diffent objects that are available, but none of them have a description of what they do so I am at a loss at to which commands will help with the task I am attempting. (If anyone knows of a resource that explains the objects I would appreciate it if you point me in that direction. A goggle search has come up empty for me so far.)

    What I am in need of help with is this:

    Either help creating a macro that loads the existing files into PDFCreator, combines them all into one file, then outputs them as a PDF file with the same name as the main file

    or

    help creating a macro that gets rid of the command I am using and instead sends the files to the PDFCreator print queue and then combines them and outputs them with the selected name.

    Thank you in advance!

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Simplest thing to see what the required code would be is to use the macro recorder.
    Select all the sheets and save as pdf.

  3. #3
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    129
    Articles
    0
    Excel Version
    365
    Are you married to a PDFCreator solution or just a way to accomplish your goals?

    IF the later, then something like this can be done as NoS said. This example does a bit more as it orders the sheets so that the PDF file is created in a specify way.

    I don't know what you mean by a selected name.
    Code:
    Sub CreatePDF2()  Dim ws As Worksheet
    
    
      Set ws = ActiveSheet
    
    
      If ThisWorkbook.Path = "" Then
        MsgBox "Save File First"
        Exit Sub
      End If
      
      'Order worksheets for proper pdf creation in order
      Worksheets("Customer Info").Move after:=Worksheets(Worksheets.Count)
      Worksheets("Order Info").Move after:=Worksheets(Worksheets.Count)
       
      With Sheets("Order Info")
        Worksheets(Array("Customer Info", "Order Info")).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
          Filename:=ThisWorkbook.Path & "\CustomerOrderInfo.pdf", _
          Quality:=xlQualityStandard, IncludeDocProperties:=True, _
          IgnorePrintAreas:=False, OpenAfterPublish:=False
      End With
         
      ws.Select
    End Sub

  4. #4
    As I stated in my original post, I am not married to a PDFCreator solution, but as I also stated I am working with PDF files. I have a worksheet that has a drop down selector. Based on the selection the data in a number of graphs on that sheet changes. The macro I have created makes a selection, then creates a PDF file of the sheet. Several selections comprise a region, so I am trying to merge the PDF files that are created into one PDF file. So, I end up creating PDF files like this:

    Main PDF - Worksheet 1 with overall data
    Temp1 PDF - Worksheet 2 with data for area 1
    Temp2 PDF - Worksheet 2 with data for area 2
    etc...

    I then want to create a merged PDF file that has the same name as the Main PDF, but merges Main PDF, Temp1 PDF, Temp2 PDF, etc.

    Due to the fact that I have to make a selection for each PDF file I can not load them into an array and export them as shown in your code.

  5. #5
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    129
    Articles
    0
    Excel Version
    365
    Of course your macro could create the worksheets, use the method that I posted, and then remove the scratch worksheets.

    I use Adobe Acrobat, not Reader, to do that at work. Since I don't have that at home, I use a 3rd party program like pdfsam to do that.

    I don't know your level VBA experience. If you can't figure it out from link 181, post back and I will download pdfcreator and work up a more specific solution for you using pdfcreator.

    I had to insert spaces in the url since I don't have 5 posts on this forum yet. www .excelguru .ca/content .php?181
    Last edited by Kenneth Hobson; 2015-07-26 at 06:59 PM.

  6. #6
    Ok, I was finally able to modify the example code to get it to do what I wanted, however it hangs at this point every time I run it:

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

    The file is created correctly and I can see it is in that path with that file name but it seems that the macro never sees it there so it never stops looping.

  7. #7
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    129
    Articles
    0
    Excel Version
    365
    If you could paste, the code, it would help us help you. Paste between code tags. Click the Go Advanced button in lower right of a reply, and then the # icon on the toolbar or (code)MsgBox "test"(/code) but replace ()'s with []'s.

    I am guessing that you may have used UNC paths. Dir() does not work with UNC. Scripting.FileSystemObject() (FSO) methods accepts UNC paths.

    What version of PDFCreator are you using? I downloaded v2.1. One has to be careful getting the right version. Even then, be careful that you don't download a trojan.

    PDFCreator.clsPDFCreator was not created in the registry by my version. The JScript help files from the installation did not work for me. This needs more work as it is annoying right now. It opens the PDF viewer(s) which one has to close manually for it to continue. In any case, here it is if you or others to play with by changing the settings to not open those applications and not show the final dialog where it creates the merged file.

    Maybe Ken Puls can stop by and fix those issues.
    Code:
    Sub Test_PDFCreatorCombine()  Dim fn(0 To 1) As String
      fn(0) = "x:\pdf\ken.pdf"
      fn(1) = "x:\pdf\ken2.pdf"
      PDFCreatorCombine fn(), "x:\pdf\PDFCreatorCombined.pdf"
    End Sub
    
    
    'Macro Purpose: Print to PDF file using PDFCreator
    ' (Download from http://sourceforge.net/projects/pdfcreator/)
    ' COM interface, http://www.pdfforge.org/pdfcreator/manual/com-interface
    ' Designed for early bind, set reference to: PDFCreator - Your OpenSource PDF Solution
    Sub PDFCreatorCombine(sPDFName() As String, sMergedPDFname As String)
      Dim oPDF As PDFCreator.PdfCreatorObj, q As PDFCreator.Queue
      Dim pj As PrintJob
      Dim v As Variant, i As Integer
      
      'On Error GoTo EndNow
      
      Set q = New PDFCreator.Queue
      q.Initialize
      If LBound(sPDFName) = 0 Then
        q.WaitForJobs UBound(sPDFName) + 1, 1
        Else
        q.WaitForJobs UBound(sPDFName), 1
      End If
      
      Set oPDF = New PDFCreator.PdfCreatorObj  'PDFCreator.clsPDFCreator
      'Set pj = q.NextJob
    
    
      i = 0
      For Each v In sPDFName()
        oPDF.PrintFile v
        i = i + 1
        'Wait print job has entered the print queue
        'Do Until pj.GetOutputFiles.Count = i
        '    DoEvents
        'Loop
        'Debug.Print pj.IsFinished
        'Set pj = q.NextJob
        
        'pj.SetProfileByGuid ("DefaultGuid")
      Next v
      
      q.MergeAllJobs
      Set pj = q.NextJob
      pj.SetProfileByGuid ("DefaultGuid")
      pj.ConvertTo sMergedPDFname
    EndNow:
      q.ReleaseCom
    End Sub

  8. #8
    I am using PDFCreator version 1. The company doesn't allow us to download and install software, so I am unable to update to the newest version at this time.

    Here is the code I ended up with.

    Code:
    Sub CreateWestPDF()
    Dim pdfjob As Object
    Dim outputPDFName As String
    Dim outputPDFPath As String
    Dim i As Integer
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
    
    ' Check to see if PDFCreator can start normally.
    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
            vbOKOnly, "Error!"
        Exit Sub
    End If
    
    pdfjob.cPrinterStop = True
    
    '/// Change the output file name here! ///
    outputPDFName = "West Area with Regions Week " & Sheets("Instructions_Input").Range("D5").Value & ".PDF"
    outputPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    
    ' set the defaults for the print jobs
    With pdfjob
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = outputPDFPath
        .cOption("AutosaveFilename") = outputPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With
    
    Sheets("West Area").Select
    ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
    Sheets("Regional").Select
    
    i = 1
    Range("C1").Value = i
    Do While Range("C1") < 10
        Sheets("Regional").Select
        ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
        i = i + 1
        Range("C1").Value = i
    Loop
    
    ' Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = i
        DoEvents
    Loop
    
    pdfjob.cCombineAll
    pdfjob.cPrinterStop = False
    
    'Wait until the file shows up before closing PDF Creator
    Do
        DoEvents
    Loop Until Dir(outputPDFPath & outputPDFName) = outputPDFName
    
    pdfjob.cClose
    Set pdfjob = Nothing
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    Sheets("Instructions_Input").Select
    
    End Sub

  9. #9
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    129
    Articles
    0
    Excel Version
    365
    Can you run this in your workbook and paste the results from the Immediate window?

    Code:
    Sub ken()  
      Dim outputPDFName As String
      Dim outputPDFPath As String
      
      '/// Change the output file name here! ///
      outputPDFName = "West Area with Regions Week " & Sheets("Instructions_Input").Range("D5").Value & ".PDF"
      outputPDFPath = ActiveWorkbook.Path & Application.PathSeparator
      
      Debug.Print outputPDFPath & outputPDFName, Dir(outputPDFPath & outputPDFName)
    End Sub

  10. #10
    C:\Users\DMAM34\Documents\West Area with Regions Week 7.PDF West Area with Regions Week 7.pdf

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
  •