Combine multiple PDF files with VBA

[EvdM]

Dear VBA Specialists,

I want to create a macro which merges multiple PDF files, but the amount of files that has to be merged is variable and need to be determined in the excelsheet. So my wish is to gain a VBA code that reads the filenames out of a folder, Somehow select which ones should merged into the same file and let Adobe Acrobat Pro do the rest.

I have achieved to get a code running that reads out a folder and projets the filename and path in the sheet (column A and B). Now should be selected which files belong in the same combined PDF. My best guess is to write down the name of the future merged filename in column C...? And adjust the code to that system. (Example Below)

Filepath 1	File 1	Mergedfile 1
Filepath 2	File 2	Mergedfile 1
Filepath 3	File 3	Mergedfile 1
Filepath 4	File 4	Mergedfile 2
Filepath 5	File 5	Mergedfile 2

Online are some examples of VBA code that could merge PDF files but not exactly working like how I imagine.
This raises the question if it makes sense to let it work how I described over here?

If you can have any advice or can help me out with making this work, that would be very much appreciated!

Kind regards,

Edwin

 

[John_w]

Actually we have PDF X-change Editor as well as PDF X-Change pro in the office

PDF X-Change Pro is not the same as Acrobat Pro. Please start a new thread because the code in this thread is for Adobe Acrobat Pro and would be completely different for the other API, which I've never used.

 

[LEENAA]

Thank You John

 

[LEENAA]

Hi John,

I installed Acrobat pro , now its stuck at a point it stuck at Right Function

Thanks

'If Right(PDFfiles(i, 1), 1) <> "\" Then PDFfiles(i, 1) = PDFfiles(i, 1) & "\"

 

[LEENAA]

Hi Paul,

Issue has resolved

Need to delete reference which sarted with missing...

Thanks

 

[avpenaloza]

As described by your OP, my code expects the following:

Column A - path
Column B - PDF file name (including .pdf extension)
Column C - PDF merged file name (including .pdf extension)

with data starting in row 2 of the active sheet.



Yes, and:

    With ActiveSheet
        PDFfiles = .Range("A2", .Cells(.Rows.Count, "G").End(xlUp)).Value
    End With

and append the ".pdf" extension to every PDFfiles(i,4) and PDFfiles(i,7) reference, for example:

        If Dir(PDFfiles(i, 3) & PDFfiles(i, 4) & ".pdf") = vbNullString Then

Hi John,

I have a similar situation that I was hoping you could help me with.
The difference is, I have one set of PDFs in one folder and the other set of PDFs in a separate folder that I want to combine. Folder A has a list of PDFs and only one of them will be combined with something in Folder B. But the number of PDFs in Folder B will vary - could be 1, could be 20.

So I was thinking, in excel I would have the list of Folder A pdfs in Column A, the list of Folder B pdfs in Column B, and then the file path with the new name in Column C (in a completely different folder).
Is that possible?

Thank you so much for your time and help in advance!

 

[John_w]

list of Folder A pdfs in Column A, the list of Folder B pdfs in Column B, and then the file path with the new name in Column C (in a completely different folder).

Try this macro.

Public Sub Merge_PDFs()

    Dim PDFfiles As Variant
    Dim i As Long, s As String
    Dim PDDocA As Object 'Acrobat.AcroPDDoc
    Dim PDDocB As Object 'Acrobat.AcroPDDoc
       
    With ActiveSheet
        PDFfiles = .Range("A2", .Cells(.Rows.Count, "C").End(xlUp)).Value
    End With
   
    'Create Acrobat API objects
   
    Set PDDocA = CreateObject("AcroExch.PDDoc")
    Set PDDocB = CreateObject("AcroExch.PDDoc")
   
    'Loop through rows, open PDF files in column A & B, merge B to A and save as PDF file in column C
   
    For i = 1 To UBound(PDFfiles)
        If Dir(PDFfiles(i, 1)) <> vbNullString And Dir(PDFfiles(i, 2)) <> vbNullString Then
            PDDocA.Open PDFfiles(i, 1)
            PDDocB.Open PDFfiles(i, 2)
            If Not PDDocA.InsertPages(PDDocA.GetNumPages - 1, PDDocB, 0, PDDocB.GetNumPages, 0) Then
                MsgBox "Error merging" & vbCrLf & PDFfiles(i, 1) & vbCrLf & PDFfiles(i, 2) & vbCrLf & "to" & vbCrLf & PDFfiles(i, 3), vbExclamation
            End If
            PDDocB.Close
            PDDocA.Save 1, PDFfiles(i, 3)
            PDDocA.Close
        Else
            s = ""
            If Dir(PDFfiles(i, 1)) = vbNullString Then s = PDFfiles(i, 1) & vbCrLf
            If Dir(PDFfiles(i, 2)) = vbNullString Then s = s & PDFfiles(i, 1) & vbCrLf
            MsgBox "File(s) not found" & vbCrLf & s, vbExclamation
        End If
    Next
   
    Set PDDocA = Nothing
    Set PDDocB = Nothing

    MsgBox "Done"
   
End Sub

Please start a new thread if this doesn't work or you need more help.

 

[avpenaloza]

Thanks, John_w !
We are definitely getting somewhere But I have an additional question.
I posted a new thread, if you could please help me.

Thank you in advance!

Combine Multiple PDFs Using VBA

Creating a new thread for additional help - previous thread: Combine multiple PDF files with VBA I am trying to merge PDFs. I have a set of PDFs in Folder A, a set of PDFs in Folder B, and I want them merged into Folder C. In Folder A, there is 1 PDF that will be matched with a variable...

www.mrexcel.com

 

[cazzatm19675]

HI i got this VBA to work for me thanks for this , i would like to know if there is way for each pdf that is merged to be bookmarked within the pdf - any help would be appreciated Thanks

 

[John_w]

See if you can incorporate the code for adding bookmarks in this macro:

VBA and Acrobat add-in to create new PDF with bookmarks

My question is hopefully rather simple, but I'm relatively new to VBA and I know nothing about Acrobat API. After spending the better part of the past two days scouring the web for an answer, I've only found long-outdated results (like like this post from 2016 based on this blog from 2009 and...

www.mrexcel.com

Search for 'bookmark' in the code and you'll see that it gets the BookmarkRoot and creates each bookmark by calling BookmarkRoot.CreateChild with a bookmark name, page index and bookmark index.

I'll reply in your new thread if you need more help.

 

[cazzatm19675]

See if you can incorporate the code for adding bookmarks in this macro:

VBA and Acrobat add-in to create new PDF with bookmarks

My question is hopefully rather simple, but I'm relatively new to VBA and I know nothing about Acrobat API. After spending the better part of the past two days scouring the web for an answer, I've only found long-outdated results (like like this post from 2016 based on this blog from 2009 and...

www.mrexcel.com

Search for 'bookmark' in the code and you'll see that it gets the BookmarkRoot and creates each bookmark by calling BookmarkRoot.CreateChild with a bookmark name, page index and bookmark index.

would i be able to incorporate the bookmark part into the code to merge the pdfs?