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]

now i happened an issue but i don't know what is reason, Could you help me to resolve this case?

As I posted above:

I should have seen that Acrobat.PDSaveFlags.PDSaveFull would require a reference to the Adobe Acrobat Type library. Apart from that, the code uses late binding of the Acrobat objects and therefore you can replace that enumeration name with the value 1 and remove the library reference.

Therefore change the errant line to:

        PDDocDestination.Save 1, PDFfiles(i, 1) & PDFfiles(i, 3)

and it should compile successfully.

 

[phuongnguyenvpvn]

As I posted above:


Therefore change the errant line to:

        PDDocDestination.Save 1, PDFfiles(i, 1) & PDFfiles(i, 3)

and it should compile successfully.

Dear Mr John
thank for your support, i tried change the code as you instruction, but now i had new issue as picture attachment, please help me to fix this
thank you!

 

[John_w]

i tried change the code as you instruction, but now i had new issue as picture attachment,

You haven't shown the actual error caused by the highlighted line. Have you installed Acrobat Pro? The OP has, therefore the code can use the Acrobat API.

 

[phuongnguyenvpvn]

You haven't shown the actual error caused by the highlighted line. Have you installed Acrobat Pro? The OP has, therefore the code can use the Acrobat API.

Dear Mr John
Many thanks!
i combined success after reinstall Acrobat pro

thank you so much!!!

 

[EvdM]

Dear Mr John
Thanks for your posts
now i happened an issue but i don't know what is reason, Could you help me to resolve this case?
Thank you so much!!


View attachment 62466

 

[EvdM]

Hi sir,

Have you checked for Tools --> Reference, if Acrobat is selected?

 

[LEENAA]

Welccome to MrExcel forums.

Try this macro:

Option Explicit

Public Sub Merge_PDFs()

    Dim PDFfiles As Variant
    Dim i As Long
    Dim PDDocDestination As Object 'Acrobat.CAcroPDDoc
    Dim PDDocSource As Object 'Acrobat.CAcroPDDoc
      
    With ActiveSheet
        PDFfiles = .Range("A2", .Cells(.Rows.Count, "C").End(xlUp)).Value
    End With
  
    'Create Acrobat API objects
  
    Set PDDocDestination = CreateObject("AcroExch.PDDoc")
    Set PDDocSource = CreateObject("AcroExch.PDDoc")
  
    'Loop through rows, opening PDF file in column A & B, merging to and saving as PDF file in column A & C
  
    For i = 1 To UBound(PDFfiles)
        If Right(PDFfiles(i, 1), 1) <> "\" Then PDFfiles(i, 1) = PDFfiles(i, 1) & "\"
        If Dir(PDFfiles(i, 1) & PDFfiles(i, 3)) = vbNullString Then
            'Destination (merged) PDF doesn't exist, so create new file
            PDDocDestination.Create
        Else
            'Open existing destination (merged) PDF
            PDDocDestination.Open PDFfiles(i, 1) & PDFfiles(i, 3)
        End If
        PDDocSource.Open PDFfiles(i, 1) & PDFfiles(i, 2)
        If Not PDDocDestination.InsertPages(PDDocDestination.GetNumPages - 1, PDDocSource, 0, PDDocSource.GetNumPages, 0) Then
            MsgBox "Error merging" & vbCrLf & PDFfiles(i, 1) & PDFfiles(i, 2) & vbCrLf & "to" & vbCrLf & PDFfiles(i, 1) & PDFfiles(i, 3), vbExclamation
        End If
        PDDocSource.Close
        PDDocDestination.Save Acrobat.PDSaveFlags.PDSaveFull, PDFfiles(i, 1) & PDFfiles(i, 3)
        PDDocDestination.Close
    Next
  
    Set PDDocSource = Nothing
    Set PDDocDestination = Nothing

    MsgBox "Done"
  
End Sub

Hi John ,

When i am trying this code i am getting error . any help in this?

 

[John_w]

When i am trying this code i am getting error . any help in this?

Is Acrobat Pro installed? The code requires Acrobat Pro.

 

[LEENAA]

Thank You , John for responding.

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

When I change code from AcroExch.App to AcroExch.PDDoc , it does not give error , but stuck at "PDDocdestination . Create ", as Create method is not available with AcroExch.APP

 

[LEENAA]

and Yes John , if this class do not support PDF X-change Editor as well as PDF X-Change pro , Is there any class of AcroExch that we can use and sort out the issue , as PDF X-change Editor as well as PDF X-Change pro , both having capabilities of Combining PDF as well as insering , deleting etc.

Thanks Once again.