Combine PDFs using VBA

Dash12

Board Regular
Joined
Jun 17, 2015
Messages
125
Hi,
I have a workbook in which I save 3 worksheets as a PDF and print them out. Now, one of the actual excel sheets triggers an external pdf file. I need to combine this external PDF to the other 3 PDFs created before. I have tried few different way but to no avail. Any suggestions would be appreciated
Thank you
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi Dash12,

FWIW, I'm not aware of any way to do that with Excel alone.

I sometimes assemble multiple worksheets (from the same workbook or many), then export those to a single PDF file. Combining existing PDF files into a single file is a different task.

There's many free utilities and web services that allow a user to combine PDFs through the use of their interface.
If you need to accomplish this task through VBA, then I'd suggest you look for 3rd party software that has a command-line interface.

With that, you might be able to use VBA to call that through the Shell function.

https://msdn.microsoft.com/en-us/library/office/gg278437(v=office.15).aspx


Here's an open source product I found through a quick web search. I don't know how/if it will work for you, but it does appear to have an interface that could be called from VBA Shell.

PDF Split and Merge | Split and merge pdf documents, free and open source


Here's a thread from their help forum in which a user provides a VBA code example that worked for him.

pdfsam support forums • View topic - Splitting a PDF using the Console in VBA
 

Dash12

Board Regular
Joined
Jun 17, 2015
Messages
125
Hi Jerry,
Thank you for the message. I was actually able to solve this question myself, but the one I asked you in the other forum is the one I am struggling with right now. It is basically a checkbox opening a pdf.. somehow , I want all those opened pdf to get merged together , and if I uncheck a specific checkbox, that linked pdf should be deleted from the merged pdf. I am sure it is a very time consuming task as I am a newbie to VBA but have managed to learned a lot myself, but this is something very advanced I suppose!
Hi Dash12,

FWIW, I'm not aware of any way to do that with Excel alone.

I sometimes assemble multiple worksheets (from the same workbook or many), then export those to a single PDF file. Combining existing PDF files into a single file is a different task.

There's many free utilities and web services that allow a user to combine PDFs through the use of their interface.
If you need to accomplish this task through VBA, then I'd suggest you look for 3rd party software that has a command-line interface.

With that, you might be able to use VBA to call that through the Shell function.

https://msdn.microsoft.com/en-us/library/office/gg278437(v=office.15).aspx


Here's an open source product I found through a quick web search. I don't know how/if it will work for you, but it does appear to have an interface that could be called from VBA Shell.

PDF Split and Merge | Split and merge pdf documents, free and open source


Here's a thread from their help forum in which a user provides a VBA code example that worked for him.

pdfsam support forums • View topic - Splitting a PDF using the Console in VBA
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,082
As Jerry said, a 3rd party program is needed to merge/combine existing PDF files. The methods to use those from VBA differs depending on the version and their capabilities. PDFSam is one that I like to use in a Shell() process. PDFCreator is probably better in v1 than v2.1. Below I will show a link that has v1 example code and my code which is very slow due to long lags in dialog dismissal for v2.1. Of course you can manually dismiss those dialogs but it is a pain if you do many.

Of course if you have Adobe Acrobat, not Reader, you can use VBA to do it.

Code:
'http://www.excelguru.ca/forums/showthread.php?4733-merging-multiple-PDF-files-into-a-single-PDF-file-via-VBA-macro
Sub Test_PDFCreatorCombine()
  Dim fn(0 To 1) As String, s As String
  fn(0) = "x:\pdf\ken.pdf"
  fn(1) = "x:\pdf\ken2.pdf"
  s = "x:\pdf\PDFCreatorCombined.pdf"
  PDFCreatorCombine fn(), s
      
  If vbYes = MsgBox(s, vbYesNo + vbQuestion, "Open?") Then Shell ("cmd /c " & """" & s & """")
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
  Dim fso As Object
  
  Set fso = CreateObject("Scripting.FileSystemObject")
  
  Set q = New PDFCreator.Queue
  With q
    .Initialize
    If LBound(sPDFName) = 0 Then
      .WaitForJobs UBound(sPDFName) + 1, 1
      Else
      .WaitForJobs UBound(sPDFName), 1
    End If
  
    
    Set oPDF = New PDFCreator.PdfCreatorObj  'PDFCreator.clsPDFCreator
  
    i = 0
    For Each v In sPDFName()
      If fso.FileExists(v) Then oPDF.PrintFile v
      i = i + 1
    Next v
    .MergeAllJobs
    
    Set pj = q.NextJob
    With pj
      .SetProfileByGuid "DefaultGuid"
      .SetProfileSetting "Printing.PrinterName", "PDFCreator"
      .SetProfileSetting "Printing.SelectPrinter", "SelectedPrinter"
      .SetProfileSetting "OpenViewer", "false"
      .SetProfileSetting "OpenWithPdfArchitect", "false"
      .SetProfileSetting "ShowProgress", "false"
      .ConvertTo sMergedPDFname
    End With
  
    .ReleaseCom
  End With
End Sub
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
I was actually able to solve this question myself, but the one I asked you in the other forum is the one I am struggling with right now. It is basically a checkbox opening a pdf.. somehow , I want all those opened pdf to get merged together , and if I uncheck a specific checkbox, that linked pdf should be deleted from the merged pdf.
What solution to this question did you come up with?

Reading the state of the checkboxes and applying your solution to specific files would be the easy part. The method through which pages are added or removed from the pdf through VBA is harder. If you have found a way to do that, please share it.

PDFs can be opened using the FollowHyperlink method (which doesn't require an actual hyperlink in your workbook).
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
PDFSam is one that I like to use in a Shell() process......
Of course if you have Adobe Acrobat, not Reader, you can use VBA to do it.
Hi Ken, Thanks for sharing that. Can you post a code example of how you've used VBA to call PDFSam?

Does Acrobat have a command line interface? Could you share a VBA example of that as well if you have one?

Thanks, - Jerry
 

Dash12

Board Regular
Joined
Jun 17, 2015
Messages
125
Thank you for the response Mr. Hobson, I already wrote some code for Merging 3 files and manually wrote that 3 files would be used as the input for the sub. I want to automate it though. Does the code that you posted here merges every pdf in the folder? I see the following lines of code:
Code:
fn(0) = "x:\pdf\ken.pdf"
  fn(1) = "x:\pdf\ken2.pdf"
Is it merging only these 2 files? I am using Adobe Acrobat Pro
As Jerry said, a 3rd party program is needed to merge/combine existing PDF files. The methods to use those from VBA differs depending on the version and their capabilities. PDFSam is one that I like to use in a Shell() process. PDFCreator is probably better in v1 than v2.1. Below I will show a link that has v1 example code and my code which is very slow due to long lags in dialog dismissal for v2.1. Of course you can manually dismiss those dialogs but it is a pain if you do many.

Of course if you have Adobe Acrobat, not Reader, you can use VBA to do it.

Code:
'http://www.excelguru.ca/forums/showthread.php?4733-merging-multiple-PDF-files-into-a-single-PDF-file-via-VBA-macro
Sub Test_PDFCreatorCombine()
  Dim fn(0 To 1) As String, s As String
  fn(0) = "x:\pdf\ken.pdf"
  fn(1) = "x:\pdf\ken2.pdf"
  s = "x:\pdf\PDFCreatorCombined.pdf"
  PDFCreatorCombine fn(), s
      
  If vbYes = MsgBox(s, vbYesNo + vbQuestion, "Open?") Then Shell ("cmd /c " & """" & s & """")
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
  Dim fso As Object
  
  Set fso = CreateObject("Scripting.FileSystemObject")
  
  Set q = New PDFCreator.Queue
  With q
    .Initialize
    If LBound(sPDFName) = 0 Then
      .WaitForJobs UBound(sPDFName) + 1, 1
      Else
      .WaitForJobs UBound(sPDFName), 1
    End If
  
    
    Set oPDF = New PDFCreator.PdfCreatorObj  'PDFCreator.clsPDFCreator
  
    i = 0
    For Each v In sPDFName()
      If fso.FileExists(v) Then oPDF.PrintFile v
      i = i + 1
    Next v
    .MergeAllJobs
    
    Set pj = q.NextJob
    With pj
      .SetProfileByGuid "DefaultGuid"
      .SetProfileSetting "Printing.PrinterName", "PDFCreator"
      .SetProfileSetting "Printing.SelectPrinter", "SelectedPrinter"
      .SetProfileSetting "OpenViewer", "false"
      .SetProfileSetting "OpenWithPdfArchitect", "false"
      .SetProfileSetting "ShowProgress", "false"
      .ConvertTo sMergedPDFname
    End With
  
    .ReleaseCom
  End With
End Sub
 

Dash12

Board Regular
Joined
Jun 17, 2015
Messages
125
Hi,
I was able to achieve what I was trying to do. Only issue is that I want a certain pdf to be the first one in the merged pdf. Perhaps you could suggest something I can implement in it?
Code:
Sub MergePDFs(MyPath As String, MyFiles As String, Optional DestFile As String = "MergedFile.pdf")
    Dim a As Variant, i As Long, n As Long, ni As Long, p As String
    Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc
     
    If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
    a = Split(MyFiles, ",")
    ReDim PartDocs(0 To UBound(a))
     
    On Error GoTo exit_
    If Len(Dir(p & DestFile)) Then Kill p & DestFile
    For i = 0 To UBound(a)
         ' Check PDF file presence
        If Dir(p & Trim(a(i))) = "" Then
            MsgBox "File not found" & vbLf & p & a(i), vbExclamation, "Canceled"
            Exit For
        End If
         ' Open PDF document
        Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
        PartDocs(i).Open p & Trim(a(i))
        If i Then
             ' Merge PDF to PartDocs(0) document
            ni = PartDocs(i).GetNumPages()
            If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
                MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
            End If
             ' Calc the number of pages in the merged document
            n = n + ni
             ' Release the memory
            PartDocs(i).Close
            Set PartDocs(i) = Nothing
        Else
             ' Calc the number of pages in PartDocs(0) document
            n = PartDocs(0).GetNumPages()
        End If
    Next
     
    If i > UBound(a) Then
         ' Save the merged document to DestFile
        If Not PartDocs(0).Save(PDSaveFull, p & DestFile) Then
            MsgBox "Cannot save the resulting document" & vbLf & p & DestFile, vbExclamation, "Canceled"
        End If
    End If
  End Sub
Hi Ken, Thanks for sharing that. Can you post a code example of how you've used VBA to call PDFSam?

Does Acrobat have a command line interface? Could you share a VBA example of that as well if you have one?

Thanks, - Jerry
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,082
Jerry, my PDFSam routine is a split routine like most have posted and you referenced. The concept is the same. I have used the merge/combine command line parameter in other languages like PerfectScript I believe.

PDFSam which is version dependent. Change the path to your files.
Code:
'http://vbaexpress.com/forum/showthread.php?p=180767

Sub Test2()
  Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
  Dim pdfOut As String, q As String
  q = """"
  pdfsam = "cmd /k java -jar " & q & _
    "C:\MyFiles\pdfsam\2.0.0\lib\" & _
    "pdfsam-console-2.0.2e.jar" & q
  pdfFiles = "-f " & q & "C:\MyFiles\pdfsam\2.0.0\doc\pdfsam-1.0.0-tutorial.pdf" & q
  pdfOut = "-o " & q & Environ("temp") & "\" & q
  pdfsamStr = pdfsam & " -p SamDoc -s BURST " & pdfFiles & " " & pdfOut & " SPLIT"
  Debug.Print pdfsamStr
  Shell pdfsamStr, vbNormalFocus
End Sub

Sub Test1()
  Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
  Dim pdfOut As String, q As String
  q = """"
  pdfsam = "cmd /k java -jar " & q & _
    "C:\MyFiles\pdfsam\1.0.3\lib\" & _
    "pdfsam-console-1.1.5e.jar" & q
  pdfFiles = "-f " & q & "C:\MyFiles\pdfsam\1.0.3\doc\pdfsam-1.0.0-tutorial.pdf" & q
  pdfOut = "-o " & q & "c:\temp" & "\" & q
  pdfsamStr = pdfsam & " " & pdfFiles & " -p ken.pdf -s BURST " & pdfOut & " split"
  Debug.Print pdfsamStr
  Shell pdfsamStr, vbNormalFocus
End Sub

Sub TestBat()
  Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
  Dim pdfOut As String, q As String
  q = ""
  pdfsam = "cmd /k java -jar " & q & _
    "C:\MyFiles\pdfsam\1.0.3\bin\" & _
    "run-console.bat" & q
  pdfFiles = "-f " & q & "C:\MyFiles\pdfsam\1.0.3\doc\pdfsam-1.0.0-tutorial.pdf" & q
  pdfOut = "-o " & q & "c:\temp" & "\" & q
  pdfsamStr = pdfsam & " " & pdfFiles & " " & pdfOut & " -p ken.pdf -s BURST split"
  Debug.Print pdfsamStr
  Shell pdfsamStr, vbNormalFocus
End Sub
For the Adobe Acrobat using its object and methods:
Code:
Sub MergePDFs(MyPath As String, MyFiles As String, Optional DestFile As String = "MergedFile.pdf")
' ZVI:2013-08-27 http://www.vbaexpress.com/forum/showthread.php?47310-Need-code-to-merge-PDF-files-in-a-folder-using-adobe-acrobat-X
' Reference required: VBE - Tools - References - Acrobat
 
    Dim a As Variant, i As Long, n As Long, ni As Long, p As String
    Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc
 
    ' Adjust MyPath string if needed.
    If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
    a = Split(MyFiles, ",")
    ReDim PartDocs(0 To UBound(a))
    
    ' Save to MyPath folder if target folder for merged PDF file was not input. (ken)
    If InStr(DestFile, "\") = 0 Then DestFile = p & DestFile
 
    On Error GoTo exit_
    If Len(Dir(DestFile)) Then Kill DestFile
    For i = 0 To UBound(a)
        ' Check PDF file presence
        If Dir(p & Trim(a(i))) = "" Then
            MsgBox "File not found" & vbLf & p & a(i), vbExclamation, "Canceled"
            Exit For
        End If
        ' Open PDF document
        Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
        PartDocs(i).Open p & Trim(a(i))
        If i Then
            ' Merge PDF to PartDocs(0) document
            ni = PartDocs(i).GetNumPages()
            If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
                MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
            End If
            ' Calc the number of pages in the merged document
            n = n + ni
            ' Release the memory
            PartDocs(i).Close
            Set PartDocs(i) = Nothing
        Else
            ' Calc the number of pages in PartDocs(0) document
            n = PartDocs(0).GetNumPages()
        End If
    Next
 
    If i > UBound(a) Then
        ' Save the merged document to DestFile
        If Not PartDocs(0).Save(PDSaveFull, DestFile) Then
            MsgBox "Cannot save the resulting document" & vbLf & DestFile, vbExclamation, "Canceled"
        End If
    End If
 
exit_:
 
    ' Inform about error/success
    If Err Then
        MsgBox Err.Description, vbCritical, "Error #" & Err.Number
    ElseIf i > UBound(a) Then
        MsgBox "The resulting file was created in:" & vbLf & p & DestFile, vbInformation, "Done"
    End If
 
    ' Release the memory
    If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
    Set PartDocs(0) = Nothing
 
    ' Quit Acrobat application
    AcroApp.Exit
    Set AcroApp = Nothing
 
End Sub
 
Last edited:

Dash12

Board Regular
Joined
Jun 17, 2015
Messages
125
I was able to find the code before you posted it here, so it worked out fine and I made the changes as desired. Although, it is merging the files in a random order. I want one of the files to be placed first than the others. How can I achieve that?

Jerry, my PDFSam routine is a split routine like most have posted and you referenced. The concept is the same. I have used the merge/combine command line parameter in other languages like PerfectScript I believe.

PDFSam which is version dependent. Change the path to your files.
Code:
'http://vbaexpress.com/forum/showthread.php?p=180767

Sub Test2()
  Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
  Dim pdfOut As String, q As String
  q = """"
  pdfsam = "cmd /k java -jar " & q & _
    "C:\MyFiles\pdfsam\2.0.0\lib\" & _
    "pdfsam-console-2.0.2e.jar" & q
  pdfFiles = "-f " & q & "C:\MyFiles\pdfsam\2.0.0\doc\pdfsam-1.0.0-tutorial.pdf" & q
  pdfOut = "-o " & q & Environ("temp") & "\" & q
  pdfsamStr = pdfsam & " -p SamDoc -s BURST " & pdfFiles & " " & pdfOut & " SPLIT"
  Debug.Print pdfsamStr
  Shell pdfsamStr, vbNormalFocus
End Sub

Sub Test1()
  Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
  Dim pdfOut As String, q As String
  q = """"
  pdfsam = "cmd /k java -jar " & q & _
    "C:\MyFiles\pdfsam\1.0.3\lib\" & _
    "pdfsam-console-1.1.5e.jar" & q
  pdfFiles = "-f " & q & "C:\MyFiles\pdfsam\1.0.3\doc\pdfsam-1.0.0-tutorial.pdf" & q
  pdfOut = "-o " & q & "c:\temp" & "\" & q
  pdfsamStr = pdfsam & " " & pdfFiles & " -p ken.pdf -s BURST " & pdfOut & " split"
  Debug.Print pdfsamStr
  Shell pdfsamStr, vbNormalFocus
End Sub

Sub TestBat()
  Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
  Dim pdfOut As String, q As String
  q = ""
  pdfsam = "cmd /k java -jar " & q & _
    "C:\MyFiles\pdfsam\1.0.3\bin\" & _
    "run-console.bat" & q
  pdfFiles = "-f " & q & "C:\MyFiles\pdfsam\1.0.3\doc\pdfsam-1.0.0-tutorial.pdf" & q
  pdfOut = "-o " & q & "c:\temp" & "\" & q
  pdfsamStr = pdfsam & " " & pdfFiles & " " & pdfOut & " -p ken.pdf -s BURST split"
  Debug.Print pdfsamStr
  Shell pdfsamStr, vbNormalFocus
End Sub
For the Adobe Acrobat using its object and methods:
Code:
Sub MergePDFs(MyPath As String, MyFiles As String, Optional DestFile As String = "MergedFile.pdf")
' ZVI:2013-08-27 http://www.vbaexpress.com/forum/showthread.php?47310-Need-code-to-merge-PDF-files-in-a-folder-using-adobe-acrobat-X
' Reference required: VBE - Tools - References - Acrobat
 
    Dim a As Variant, i As Long, n As Long, ni As Long, p As String
    Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc
 
    ' Adjust MyPath string if needed.
    If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
    a = Split(MyFiles, ",")
    ReDim PartDocs(0 To UBound(a))
    
    ' Save to MyPath folder if target folder for merged PDF file was not input. (ken)
    If InStr(DestFile, "\") = 0 Then DestFile = p & DestFile
 
    On Error GoTo exit_
    If Len(Dir(DestFile)) Then Kill DestFile
    For i = 0 To UBound(a)
        ' Check PDF file presence
        If Dir(p & Trim(a(i))) = "" Then
            MsgBox "File not found" & vbLf & p & a(i), vbExclamation, "Canceled"
            Exit For
        End If
        ' Open PDF document
        Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
        PartDocs(i).Open p & Trim(a(i))
        If i Then
            ' Merge PDF to PartDocs(0) document
            ni = PartDocs(i).GetNumPages()
            If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
                MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
            End If
            ' Calc the number of pages in the merged document
            n = n + ni
            ' Release the memory
            PartDocs(i).Close
            Set PartDocs(i) = Nothing
        Else
            ' Calc the number of pages in PartDocs(0) document
            n = PartDocs(0).GetNumPages()
        End If
    Next
 
    If i > UBound(a) Then
        ' Save the merged document to DestFile
        If Not PartDocs(0).Save(PDSaveFull, DestFile) Then
            MsgBox "Cannot save the resulting document" & vbLf & DestFile, vbExclamation, "Canceled"
        End If
    End If
 
exit_:
 
    ' Inform about error/success
    If Err Then
        MsgBox Err.Description, vbCritical, "Error #" & Err.Number
    ElseIf i > UBound(a) Then
        MsgBox "The resulting file was created in:" & vbLf & p & DestFile, vbInformation, "Done"
    End If
 
    ' Release the memory
    If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
    Set PartDocs(0) = Nothing
 
    ' Quit Acrobat application
    AcroApp.Exit
    Set AcroApp = Nothing
 
End Sub
 

Forum statistics

Threads
1,077,649
Messages
5,335,473
Members
399,017
Latest member
npatel917

Some videos you may like

This Week's Hot Topics

Top