Append Pdf pages with Distiller from Excel VBA

Sorry about the delay.  Are all 4 PDF's files that are saved?  You just need a routine to merge the 4 files?

Dave

Ok - anticipating your response it that they're all saved files, here's a routine that looks in Column A, starting at A1 for as many rows as you have data in that row (so nothing else but these file names in that column - at least for how I wrote it).

The app opens the first doc, then inserts pages on successive doc's opening/closing them as it goes, then it prompts you for a saveas filename (overwriting without warning) and gives you an option to preview the results.

Code here:

Option Explicit

Type myPDDOC_Type
    Filepath As String
    openResult As Variant
    objCAcroPDDOC As Object
End Type

Sub MergePDF_Files()
'Relies on the Adobe Acrobat X.X Type Library - just add Tools->References->Acrobat and it will work, accordingly
Dim objAcroApp As Object, avDOC As Object
Dim myPDDOC() As myPDDOC_Type, i As Integer
Dim basePDDOC As Object
Dim myPDDocFile As Range
Dim mergeResult As Variant
Dim sFile As String, saveResult As Variant
Dim xMsg As Long

    'Initialize the objects
    Set objAcroApp = CreateObject("AcroExch.App")

    i = 0
    For Each myPDDocFile In Range("A1", Range("A" & Rows.Count).End(xlUp)) 'look for contiguous range for files to merge
        ReDim Preserve myPDDOC(i) As myPDDOC_Type
        myPDDOC(i).Filepath = myPDDocFile.Value
        i = i + 1
    Next myPDDocFile
    

    For i = 0 To UBound(myPDDOC)
        Set myPDDOC(i).objCAcroPDDOC = CreateObject("AcroExch.PDDoc")
        myPDDOC(i).openResult = myPDDOC(i).objCAcroPDDOC.Open(myPDDOC(i).Filepath)
        If i = 0 Then
            Set basePDDOC = myPDDOC(0).objCAcroPDDOC
        Else ' start merging using InsertPages method on the first document
            'mergeResult = myPDDOC(0).objCAcroPDDOC.InsertPages(i - 1, myPDDOC(i).objCAcroPDDOC, 0, 1, 0)
            'InsertPages (See Acrobat API Reference for parameters)
            mergeResult = basePDDOC.InsertPages(basePDDOC.GetNumPages - 1, myPDDOC(i).objCAcroPDDOC, 0, myPDDOC(i).objCAcroPDDOC.GetNumPages, 0)
            myPDDOC(i).objCAcroPDDOC.Close
            Set myPDDOC(i).objCAcroPDDOC = Nothing
        End If
    Next i
        
    sFile = Application.GetSaveAsFilename(filefilter:="PDF Files (*.pdf), *.pdf", Title:="Select Save As Filename for Merged PDF File")
    
    If sFile <> "False" Then
        saveResult = basePDDOC.Save(1, sFile)
        xMsg = MsgBox("Documents Merged!  Open and Review?", vbYesNo, "Hit Yes to open PDF for review")
        
        If xMsg = vbYes Then
            
            objAcroApp.Show
            
            Set avDOC = basePDDOC.OpenAVDoc("")
            
            MsgBox "Hit Ok to close it out and continue...", vbOKOnly, "Hit Ok, Ok? :)"
            
            objAcroApp.Hide
            
            avDOC.Close (True)

            Set avDOC = Nothing
        End If
    End If
    
    'do cleanup routine
    basePDDOC.Close
    Set basePDDOC = Nothing
    objAcroApp.Exit
    Set objAcroApp = Nothing
    
End Sub

Select allOpen in new window

See attached.

Hope this is hitting the target!

Enjoy!

Dave
PDF-Merge-r1.xlsm

Dave, this is great stuff. I have increased to 500. Amazing.

Last questions:
1) If instead putting the path of the pdf's (already saved) to be merged in A1... but in named ranges, for example PdfPath1 PdfPath2 PdfPath3, how do I need to change the code... I guess removing the loop

 i = 0
    For Each myPDDocFile In Range("A1", Range("A" & Rows.Count).End(xlUp)) 'look for contiguous range for files to merge
        ReDim Preserve myPDDOC(i) As myPDDOC_Type
        myPDDOC(i).Filepath = myPDDocFile.Value
        i = i + 1
    Next myPDDocFile

Select allOpen in new window

and what else?

2) if the name of the merged pdf is in a range or cell, I just change

    sFile = Application.GetSaveAsFilename(filefilter:="PDF Files (*.pdf), *.pdf", Title:="Select Save As Filename for Merged PDF File")

Select allOpen in new window

to for example this, right:

    sFile = Range("MergedPdfFilePath").Value

Select allOpen in new window

For the first, if you put your PDF Path in a list somewhere:

Change:
For Each myPDDocFile In Range("A1", Range("A" & Rows.Count).End(xlUp)) 'look for contiguous range for files to merge

To:
For Each myPDDocFile in Range("your named range of files")

Select allOpen in new window

Yes, you could assign them all individually, just increment the array myPDDOC() by one for each add...

>>2) if the name of the merged pdf is in a range or cell, I just change

Yes, that's correct

first class. excellent. thank you very much.