Merge PDF documents and save with a new name as per Excel Sheet

[abdulads]

Hi, Please help me Merge PDF Documents as per the Excel Sheet and path provided in it.

Here we have to combine File Name 1 and File Name 2 and give the Output in the Final Folder with the Given output name.

File Name1​	File Name2​	Output​
C:/Home/1.pdf​	C:/Office/2.pdf​	C:/Final/1&2.PDF​
C:/Home/3.pdf​	C:/Office/4pdf​	C:/Final/3&4.PDF​
C:/Home/5.pdf​	C:/Office/6.pdf​	C:/Final/5&6.PDF​

 

[John_w]

PDFs can be merged by calling the Acrobat API from VBA, however that is only available if you've installed Acrobat Pro, not the free Reader.

To give you an idea, the macro in the following thread merges PDFs:

VBA, Combine PDFs into one PDF

Hi all, Say I have a few pdfs, I list location/file name in cell, 1 pdf file with 5 pages, and a few other pdfs, is there any vba code that can combine this into one pdf? Is this possible?

www.mrexcel.com

 

[abdulads]

PDFs can be merged by calling the Acrobat API from VBA, however that is only available if you've installed Acrobat Pro, not the free Reader.

To give you an idea, the macro in the following thread merges PDFs:

VBA, Combine PDFs into one PDF

Hi all, Say I have a few pdfs, I list location/file name in cell, 1 pdf file with 5 pages, and a few other pdfs, is there any vba code that can combine this into one pdf? Is this possible?

www.mrexcel.com

Thank you for the quick response!
Sorry i am uanble to understand the link provided. I have both the files different how can i refer the Column A and colum B which has the file names. that is first file and second file and out put as per column C. Please can you help me according to that.

 

[John_w]

Try this macro, which requires the reference noted at the top of the code - set via Tools -> References in the VBA editor.

'Reference: Adobe Acrobat nn.0 Type Library

Public Sub Merge_PDFs()

    Dim PDFfiles As Variant
    Dim i As Long
    Dim objCAcroPDDocDestination As Acrobat.CAcroPDDoc
    Dim objCAcroPDDocSource As Acrobat.CAcroPDDoc
        
    With ActiveSheet
        PDFfiles = .Range("A2", .Cells(.Rows.Count, "C").End(xlUp)).Value
    End With
    
    'Create Acrobat API objects
    
    Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")
    Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")
    
    'Loop through rows, open PDF file in column A, open and insert PDF file in column B, save as PDF file in column C
    
    For i = 1 To UBound(PDFfiles)
        objCAcroPDDocDestination.Open PDFfiles(i, 1)
        objCAcroPDDocSource.Open PDFfiles(i, 2)
        If Not objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
            MsgBox "Error merging" & vbCrLf & PDFfiles(i, 1) & vbCrLf & "and" & vbCrLf & PDFfiles(i, 2), vbExclamation
        End If
        objCAcroPDDocSource.Close
        objCAcroPDDocDestination.Save 1, PDFfiles(i, 3)
        objCAcroPDDocDestination.Close
    Next
    
    Set objCAcroPDDocSource = Nothing
    Set objCAcroPDDocDestination = Nothing

    MsgBox "Done"
    
End Sub

 

[abdulads]

Try this macro, which requires the reference noted at the top of the code - set via Tools -> References in the VBA editor.

'Reference: Adobe Acrobat nn.0 Type Library

Public Sub Merge_PDFs()

    Dim PDFfiles As Variant
    Dim i As Long
    Dim objCAcroPDDocDestination As Acrobat.CAcroPDDoc
    Dim objCAcroPDDocSource As Acrobat.CAcroPDDoc
       
    With ActiveSheet
        PDFfiles = .Range("A2", .Cells(.Rows.Count, "C").End(xlUp)).Value
    End With
   
    'Create Acrobat API objects
   
    Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")
    Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")
   
    'Loop through rows, open PDF file in column A, open and insert PDF file in column B, save as PDF file in column C
   
    For i = 1 To UBound(PDFfiles)
        objCAcroPDDocDestination.Open PDFfiles(i, 1)
        objCAcroPDDocSource.Open PDFfiles(i, 2)
        If Not objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
            MsgBox "Error merging" & vbCrLf & PDFfiles(i, 1) & vbCrLf & "and" & vbCrLf & PDFfiles(i, 2), vbExclamation
        End If
        objCAcroPDDocSource.Close
        objCAcroPDDocDestination.Save 1, PDFfiles(i, 3)
        objCAcroPDDocDestination.Close
    Next
   
    Set objCAcroPDDocSource = Nothing
    Set objCAcroPDDocDestination = Nothing

    MsgBox "Done"
   
End Sub

Thank you so much ... it worked you are a genius.

 

[avpenaloza]

Try this macro, which requires the reference noted at the top of the code - set via Tools -> References in the VBA editor.

'Reference: Adobe Acrobat nn.0 Type Library

Public Sub Merge_PDFs()

    Dim PDFfiles As Variant
    Dim i As Long
    Dim objCAcroPDDocDestination As Acrobat.CAcroPDDoc
    Dim objCAcroPDDocSource As Acrobat.CAcroPDDoc
       
    With ActiveSheet
        PDFfiles = .Range("A2", .Cells(.Rows.Count, "C").End(xlUp)).Value
    End With
   
    'Create Acrobat API objects
   
    Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")
    Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")
   
    'Loop through rows, open PDF file in column A, open and insert PDF file in column B, save as PDF file in column C
   
    For i = 1 To UBound(PDFfiles)
        objCAcroPDDocDestination.Open PDFfiles(i, 1)
        objCAcroPDDocSource.Open PDFfiles(i, 2)
        If Not objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
            MsgBox "Error merging" & vbCrLf & PDFfiles(i, 1) & vbCrLf & "and" & vbCrLf & PDFfiles(i, 2), vbExclamation
        End If
        objCAcroPDDocSource.Close
        objCAcroPDDocDestination.Save 1, PDFfiles(i, 3)
        objCAcroPDDocDestination.Close
    Next
   
    Set objCAcroPDDocSource = Nothing
    Set objCAcroPDDocDestination = Nothing

    MsgBox "Done"
   
End Sub

Hi John_w,
Thank you for this code, it works perfectly for me. Although, I have saved the macro enabled workbook to a shared folder on my company's server and when my coworkers access this workbook and run the code they are getting an "error merging" message - image attached. I have confirmed that all their object references are the same as mine but I cannot for the life of me figure out why it won't work for them and will for me. I am not a vba pro in the slightest and don't understand the 'If Not' statement. Any ideas what could be happening? Or where I could start? Anything will help, thank you!

 

[John_w]

Thank you for this code, it works perfectly for me. Although, I have saved the macro enabled workbook to a shared folder on my company's server and when my coworkers access this workbook and run the code they are getting an "error merging" message - image attached. I have confirmed that all their object references are the same as mine but I cannot for the life of me figure out why it won't work for them and will for me. I am not a vba pro in the slightest and don't understand the 'If Not' statement. Any ideas what could be happening? Or where I could start? Anything will help, thank you!

Does the "Error merging" message occur on every pair of PDFs or only specific ones in the shared folder?

The If Not objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then statement is calling the Acrobat API's InsertPages method to insert all pages from the source PDF (objCAcroPDDocSource) after the last page in the destination PDF (objCAcroPDDocDestination). The method returns -1 (True) if the pages were successfully inserted or 0 (False) if they were not. So the If statement is saying 'If Not True that the pages were inserted Then' display the message.

Maybe the API doesn't like the shared folder - do the users have read and write access to it?

Here is a modified version of the macro which copies the PDFs to the user's temp folder, does the merging in that folder and copies the merged PDF to the required output file in column C.

Public Sub Merge_PDFs2()

    Dim PDFfiles As Variant
    Dim i As Long
    Dim objCAcroPDDocDestination As Acrobat.CAcroPDDoc
    Dim objCAcroPDDocSource As Acrobat.CAcroPDDoc
    Dim tempFolder As String, outputPDF As String
   
    tempFolder = Environ("temp") & "\"
   
    With ActiveSheet
        PDFfiles = .Range("A2", .Cells(.Rows.Count, "C").End(xlUp)).Value
    End With
   
    'Create Acrobat API objects
   
    Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")
    Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")
   
    'Loop through rows, copy PDF files in columns A and B to temp folder, merge them and save in temp folder and copy merged file to PDF file in column C
   
    For i = 1 To UBound(PDFfiles)
       
        Debug.Print tempFolder & Mid(PDFfiles(i, 1), InStrRev(PDFfiles(i, 1), "\") + 1)
        FileCopy PDFfiles(i, 1), tempFolder & Mid(PDFfiles(i, 1), InStrRev(PDFfiles(i, 1), "\") + 1)
        PDFfiles(i, 1) = tempFolder & Mid(PDFfiles(i, 1), InStrRev(PDFfiles(i, 1), "\") + 1)
        FileCopy PDFfiles(i, 2), tempFolder & Mid(PDFfiles(i, 2), InStrRev(PDFfiles(i, 2), "\") + 1)
        PDFfiles(i, 2) = tempFolder & Mid(PDFfiles(i, 2), InStrRev(PDFfiles(i, 2), "\") + 1)
        outputPDF = PDFfiles(i, 3)
        PDFfiles(i, 3) = tempFolder & Mid(PDFfiles(i, 3), InStrRev(PDFfiles(i, 3), "\") + 1)
       
        objCAcroPDDocDestination.Open PDFfiles(i, 1)
        objCAcroPDDocSource.Open PDFfiles(i, 2)
       
        If Not objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
            MsgBox "Error merging" & vbCrLf & PDFfiles(i, 1) & vbCrLf & "and" & vbCrLf & PDFfiles(i, 2), vbExclamation
        End If
       
        objCAcroPDDocSource.Close
        objCAcroPDDocDestination.Save Acrobat.PDSaveFlags.PDSaveFull, PDFfiles(i, 3)
        objCAcroPDDocDestination.Close
       
        FileCopy PDFfiles(i, 3), outputPDF
   
    Next
   
    Set objCAcroPDDocSource = Nothing
    Set objCAcroPDDocDestination = Nothing

    MsgBox "Done"
   
End Sub

 

[avpenaloza]

Does the "Error merging" message occur on every pair of PDFs or only specific ones in the shared folder?

The If Not objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then statement is calling the Acrobat API's InsertPages method to insert all pages from the source PDF (objCAcroPDDocSource) after the last page in the destination PDF (objCAcroPDDocDestination). The method returns -1 (True) if the pages were successfully inserted or 0 (False) if they were not. So the If statement is saying 'If Not True that the pages were inserted Then' display the message.

Maybe the API doesn't like the shared folder - do the users have read and write access to it?

Here is a modified version of the macro which copies the PDFs to the user's temp folder, does the merging in that folder and copies the merged PDF to the required output file in column C.

Public Sub Merge_PDFs2()

    Dim PDFfiles As Variant
    Dim i As Long
    Dim objCAcroPDDocDestination As Acrobat.CAcroPDDoc
    Dim objCAcroPDDocSource As Acrobat.CAcroPDDoc
    Dim tempFolder As String, outputPDF As String
  
    tempFolder = Environ("temp") & "\"
  
    With ActiveSheet
        PDFfiles = .Range("A2", .Cells(.Rows.Count, "C").End(xlUp)).Value
    End With
  
    'Create Acrobat API objects
  
    Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")
    Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")
  
    'Loop through rows, copy PDF files in columns A and B to temp folder, merge them and save in temp folder and copy merged file to PDF file in column C
  
    For i = 1 To UBound(PDFfiles)
      
        Debug.Print tempFolder & Mid(PDFfiles(i, 1), InStrRev(PDFfiles(i, 1), "\") + 1)
        FileCopy PDFfiles(i, 1), tempFolder & Mid(PDFfiles(i, 1), InStrRev(PDFfiles(i, 1), "\") + 1)
        PDFfiles(i, 1) = tempFolder & Mid(PDFfiles(i, 1), InStrRev(PDFfiles(i, 1), "\") + 1)
        FileCopy PDFfiles(i, 2), tempFolder & Mid(PDFfiles(i, 2), InStrRev(PDFfiles(i, 2), "\") + 1)
        PDFfiles(i, 2) = tempFolder & Mid(PDFfiles(i, 2), InStrRev(PDFfiles(i, 2), "\") + 1)
        outputPDF = PDFfiles(i, 3)
        PDFfiles(i, 3) = tempFolder & Mid(PDFfiles(i, 3), InStrRev(PDFfiles(i, 3), "\") + 1)
      
        objCAcroPDDocDestination.Open PDFfiles(i, 1)
        objCAcroPDDocSource.Open PDFfiles(i, 2)
      
        If Not objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
            MsgBox "Error merging" & vbCrLf & PDFfiles(i, 1) & vbCrLf & "and" & vbCrLf & PDFfiles(i, 2), vbExclamation
        End If
      
        objCAcroPDDocSource.Close
        objCAcroPDDocDestination.Save Acrobat.PDSaveFlags.PDSaveFull, PDFfiles(i, 3)
        objCAcroPDDocDestination.Close
      
        FileCopy PDFfiles(i, 3), outputPDF
  
    Next
  
    Set objCAcroPDDocSource = Nothing
    Set objCAcroPDDocDestination = Nothing

    MsgBox "Done"
  
End Sub

Thank you for the quick response!

The "error merging" message occurs on every PDF pair for my coworkers.
Both of them having access to read and write to the folder, yes.

I tried this modified macro (thank you so much, by the way!) and I got a debug message. I looked in that bottom section of VBA and saw this path: C:\Users\APENAL~1\AppData\Local\Temp\Save to and noticed that I didn't have a 'Save to' folder so I created one. I ran the code again and it worked perfectly for me. Not exactly sure if that is what I was supposed to do, but it worked
I haven't tested it with my coworkers yet, but will I need to tell them to create a 'Save to' folder in their temp folder?

Thank you for your time on this!

 

[John_w]

I looked in that bottom section of VBA and saw this path: C:\Users\APENAL~1\AppData\Local\Temp\Save to

That line is output to the VBA Immediate window by the Debug.Print statement, to show the full file name of each column A file in the temp folder. But the part after the '\Temp\" definitely isn't correct, unless you haven't posted the whole line.

The C:\Users\APENAL~1\AppData\Local\Temp\ is your temp folder, which is correct.

If A2 contains S:\folder\subfolder\PDF file1.pdf then the Debug.Print output should be:

C:\Users\APENAL~1\AppData\Local\Temp\PDF file1.pdf

So the code copies S:\folder\subfolder\PDF file1.pdf to C:\Users\APENAL~1\AppData\Local\Temp\PDF file1.pdf, does the same for the column B file, and merges the 2 files to the column C file in the temp folder and copies the merged result to the proper column C file.

 

[avpenaloza]

In reading your response, I just realized my data starts at A5, not A2 - complete oversight on my part.
I took a couple steps back and deleted the 'Save to' folder I initially created, changed the range to start at A5 and ran it again ... it works!!
and in even better news, I just had my coworker test it and it also works for her!!
Thank you SO much, John. I seriously cannot thank you enough! you have been so helpful

That line is output to the VBA Immediate window by the Debug.Print statement, to show the full file name of each column A file in the temp folder. But the part after the '\Temp\" definitely isn't correct, unless you haven't posted the whole line.

The C:\Users\APENAL~1\AppData\Local\Temp\ is your temp folder, which is correct.

If A2 contains S:\folder\subfolder\PDF file1.pdf then the Debug.Print output should be:

C:\Users\APENAL~1\AppData\Local\Temp\PDF file1.pdf

So the code copies S:\folder\subfolder\PDF file1.pdf to C:\Users\APENAL~1\AppData\Local\Temp\PDF file1.pdf, does the same for the column B file, and merges the 2 files to the column C file in the temp folder and copies the merged result to the proper column C file.