Using Excel to merge 2 or more PDF documents Into 1 PDF Document

**jonvanwyk** · 10-11-2011, 01:30 PM

I want to create a simple Excel form that allows a user to browse for 2 or more PDF files located on the desktop, and then merge them into one PDF document. It would look something like this:

--------------------

File 1: [Browse]
File 2: [Browse]
File 3: [Browse]
File 4: [Browse]
File 5: [Browse]

Name of New Document: [User Defined]

[Submit Button]

--------------------

The name of the new PDF document would be user defined, but would automatically save to the desktop when "Submit" is clicked.
It would also be appropriate for there to be VBA that would also automatically delete from the desktop the original files that were merged.

Any assistance with this is greatly appreciated!

**royUK** · 10-11-2011, 01:35 PM

I doubt if you can do this with Excel, you would need to use a pdf software

**jonvanwyk** · 10-11-2011, 01:38 PM

I am also trying to research this question online. I found a post on another forum that provided the code below. Do you see this code as meeting my stated needs?


Public Sub mergePDFFiles(inDirectory As String, outMergeFile As String, fileList() As String)
Dim AcroApp As CAcroApp
Dim PDDoc As CAcroPDDoc
Dim InsertPDDoc As CAcroPDDoc
Dim iNumberOfPagesToInsert As Integer, iLastPage As Integer

'On Error GoTo ErrorHandler

Set AcroApp = CreateObject("AcroExch.App")
Set PDDoc = CreateObject("AcroExch.PDDoc")
Set InsertPDDoc = CreateObject("AcroExch.PDDoc")

'/  Is there a trailing backslash on the inDirectory?
If Right(inDirectory, 1) <> "" Then
    inDirectory = inDirectory & ""
End If
    
'/ Hide the Acrobat window
AcroApp.Hide
 
'/ Create a Blank PDDoc (The Merge File) - Alterations courtesy of KDA.
If PDDoc.Create = False Then
    MsgBox "Creation not successful"
    End
End If

'/PDDoc.Save &H1, inDirectory & outMergeFile

For goRound = LBound(fileList()) To UBound(fileList())
    
    ' Get the total pages less one for the last page num [zero based]
    iLastPage = PDDoc.GetNumPages - 1
    '/If (iLastPage = -1) Then iLastPage = 0
    
    If fileList(goRound) = "" Then
        Exit For
    End If
    
    '/  Using the fileList Array access the first index member
    If InsertPDDoc.Open(fileList(goRound)) = False Then
        writeError inDirectory & "errpdfMerge.log", "Error Opening The File To Insert - " & fileList(goRound)
        '/ MsgBox "Error Opening The File To Insert - " & fileList(goRound), , "MergePDFs - Command Line"
        Exit Sub
    End If
        
    '/ Get the number of pages to insert from the Current InsertPDDoc object
    iNumberOfPagesToInsert = InsertPDDoc.GetNumPages
    
    strOutput = "Go Round Counter for fileList array: " & goRound & vbCrLf & _
            "File List Array Item: " & fileList(goRound) & vbCrLf & _
            "Insert Document: " & InsertPDDoc.GetFileName & vbCrLf & _
            "Last Page Number: " & iLastPage & vbCrLf & _
            "Number of Pages to Insert: " & iNumberOfPagesToInsert & vbCrLf & _
            "File Name of PDDoc:  " & PDDoc.GetFileName & vbCrLf & _
            "Directory of In Files: " & inDirectory & vbCrLf & _
            "Title from Insert Doc: " & InsertPDDoc.GetInfo("Title") & vbCrLf & _
            "Number of Files in Array: " & cntFiles

    '/MsgBox strOutput
   
    '/ Insert the pages
    If PDDoc.InsertPages(iLastPage, InsertPDDoc, 0, iNumberOfPagesToInsert, True) = False Then
        'writeError inDirectory & "errpdfMerge.log", "Error Inserting Pages - " & inDirectory & fileList(goRound)
        '/MsgBox "Error Inserting Pages - " & inDirectory & fileList(goRound), , "MergePDFs - Command Line"
        Exit Sub
    End If

    ' Close the document without saving
    If InsertPDDoc.Close() = False Then
        'writeError inDirectory & "errpdfMerge.log", "Error Closing Insert Document - " & fileList(goRound)
        '/MsgBox "Error Closing Insert Document - " & fileList(goRound), , "MergePDFs - Command Line"
        Exit For
    End If
    
    writeLog inDirectory & "pdfMerge.log", inDirectory & fileList(goRound), iNumberOfPagesToInsert
Next
        '/ Save the File Optimized
        If (PDDoc.Save(&H5, outMergeFile)) = False Then
         '   writeError inDirectory & "errpdfMerge.log", "Error Saving The Merged Document - " & outMergeFile
            MsgBox "Error Saving The Merged Document - " & outMergeFile, , "MergePDFs - Command Line"
            Exit Sub
        End If
    
    ' Close the PDDoc
    If PDDoc.Close() = False Then
        'writeError inDirectory & "errpdfMerge.log", "Error Closing The Merged Document - " & fileList(goRound)
        MsgBox "Error Closing The Merged Document - " & fileList(goRound), , "MergePDFs"
        Exit Sub
    End If
    ' Close Acrobat Exchange
    AcroApp.Exit

    Set AcroApp = Nothing
    Set PDDoc = Nothing
    Set InsertPDDoc = Nothing
    
    '/MsgBox "Merge Completed", vbOKOnly, "MergePDF's - Command Line Version"
    'writeLogFooter inDirectory & "pdfMerge.log"
Exit Sub

ErrorHandler:
    MsgBox Err.Description & " dave", , "MergePDFs"
    Err.Clear
    Exit Sub
End Sub

Public Sub writeLog(logFileName As String, fileBeingInserted As String, numberOfPagesInserted As Integer)

Open logFileName For Append As #3
    Print #3, fileBeingInserted & "," & numberOfPagesInserted
    'Print #1, "--------------- end of session: " & Now()
Close #3

End Sub

**jonvanwyk** · 10-11-2011, 02:16 PM

I am finding some indication that this can be done, but am not advanced enough to decifer if the code will meet my needs; or how to modify it to do so. Below is another example I found:


Public Sub updfConcatenate(pvarFromPaths As Variant, _
                           pstrToPath As String)
 
    Dim origPdfDoc      As Acrobat.CAcroPDDoc
    Dim newPdfDoc       As Acrobat.CAcroPDDoc
    Dim lngNewPageCount As Long
    Dim lngInsertPage   As Long
    Dim i               As Long
 
    Set origPdfDoc = CreateObject("AcroExch.PDDoc")
    Set newPdfDoc = CreateObject("AcroExch.PDDoc")
    mlngBkmkCounter = 0
 
    'set the first file in the array as the "new"'
    If newPdfDoc.Open(pvarFromPaths(LBound(pvarFromPaths))) = True Then
        updfInsertBookmark "Test Start", lngInsertPage, , newPdfDoc
        mlngBkmkCounter = 1
 
        For i = LBound(pvarFromPaths) + 1 To UBound(pvarFromPaths)
            Application.StatusBar = "Merging " &amp; pvarFromPaths(i) &amp; "..."
            If origPdfDoc.Open(pvarFromPaths(i)) = True Then
                lngInsertPage = newPdfDoc.GetNumPages
                newPdfDoc.InsertPages lngInsertPage - 1, origPdfDoc, 0, origPdfDoc.GetNumPages, False
                updfInsertBookmark "Test " &amp; i, lngInsertPage, , newPdfDoc
                origPdfDoc.Close
                mlngBkmkCounter = mlngBkmkCounter + 1
            End If
        Next i
        newPdfDoc.Save PDSaveFull, pstrToPath
    End If
 
ExitHere:
    Set origPdfDoc = Nothing
    Set newPdfDoc = Nothing
    Application.StatusBar = False
    Exit Sub
 
End Sub

**jonvanwyk** · 10-11-2011, 02:24 PM

I am trying to do the leg work so that you Excel VBA gurus out there can let me know if I am on the right track, or if it is a lost cause.

Here is another example that I have found:


Sub MergePDF() 

Dim Path1 As String, Path2 As String, Path3 As String, Path4 As String 

Dim gPDDoc1 As AcroPDDoc 
Dim gPDDoc2 As AcroPDDoc 
Dim gPDDoc3 As AcroPDDoc 
Dim gPDDoc4 As AcroPDDoc 

Set gPDDoc1 = CreateObject("AcroExch.PDDoc") 
Set gPDDoc2 = CreateObject("AcroExch.PDDoc") 
Set gPDDoc3 = CreateObject("AcroExch.PDDoc") 
Set gPDDoc4 = CreateObject("AcroExch.PDDoc") 

chk1 = gPDDoc1.Open("C:\junk\1.pdf") 
chk2 = gPDDoc2.Open("C:\junk\2.pdf") 
chk3 = gPDDoc3.Open("C:\junk\3.pdf") 
chk4 = gPDDoc4.Open("C:\junk\4.pdf") 

mergefile = gPDDoc1.InsertPages(0, gPDDoc2, 0, 1, 0) 
mergefile = gPDDoc1.InsertPages(1, gPDDoc3, 0, 1, 0) 
mergefile = gPDDoc1.InsertPages(2, gPDDoc4, 0, 1, 0) 

savemergefile = gPDDoc1.Save(1, "C:\junk\merged.pdf") 
End Sub

**Kyle123** · 10-11-2011, 02:33 PM

Hi, all the examples you have posted use external pdf software to mge the documents. Do you have any software that can do this? You can then look to incorporate this into,your workbook

**jonvanwyk** · 10-11-2011, 02:55 PM

I was afraid that they were using external software. No...this would not be available. I will keep looking...

**Kyle123** · 10-11-2011, 03:16 PM

I'm pretty sure that it's not possible without external software

**jonvanwyk** · 10-11-2011, 03:34 PM

I see a lot of words like "probably" and "pretty sure" being used. You are 'probably' right, but it is worth asking the question, as maybe someone has figured out a solution before.

Perhaps the key is to use Excel to convert the PDF into a different type of document that Excel CAN merge together, and then convert THAT combined file back into a PDF? It seems reasonable to assume that if Excel can convert Excel to PDF, then Excel can convert PDF to Excel. It then seems reasonable that Excel can combine two or more Excel documents into one Excel document, and then convert that one Excel Document back into a single PDF document [for example].