Merging Multiple PDFs in a folder based on file names entered in excel

[hemanthrr]

Hi I have close to 300 different PDF's in a folder. What i want is when i enter the file names in excel (could be only 2 files or could be even 100 files as well) , these files should get merged into one PDF in the same sequence as mentioned in the excel.

I would be really grateful if someone could help me on this .

Thanks in advance.

[Kenneth Hobs]

Welcome to the forum!

There are 3 ways that I can think of.
1. Needs Acrobat, not Adobe Reader.
2. Needs PDFCreator.
3. Needs a 3rd party application that can be command-line automated using Shell(). e.g. PDFSam, PDFfill, etc.
a. PDFfill example command lines: http://www.pdfill.com/pdf_batch_command.html

[hemanthrr]

Thanks for your prompt response. I do have acrobat . Could you assist me with the actual code I need to enter in excel .

thanks

[Kenneth Hobs]

Sub Test_MergePDFs()  
  Dim a As Variant
  a = WorksheetFunction.Transpose(Range("A2:A" & Range("A2").End(xlDown).Row).Value)
   MergePDFs Range("C2").Value2, Join(a, ","), Range("B2").Value2
End Sub


Sub MergePDFs(MyPath As String, MyFiles As String, Optional DestFile As String = "MergedFile.pdf")
' ZVI:2013-08-27 http://www.vbaexpress.com/forum/showthread.php?47310-Need-code-to-merge-PDF-files-in-a-folder-using-adobe-acrobat-X
' Reference required: VBE - Tools - References - Acrobat
 
    Dim a As Variant, i As Long, n As Long, ni As Long, p As String
    Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc
    Dim fso As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
 
    ' Adjust MyPath string if needed.
    If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
    a = Split(MyFiles, ",")
    ReDim PartDocs(0 To UBound(a))
    
    ' Save to MyPath folder if target folder for merged PDF file was not input. (ken)
    If InStr(DestFile, "\") = 0 Then DestFile = p & DestFile
 
    On Error GoTo exit_
    If fso.FileExists(p & DestFile) Then Kill p & DestFile
    For i = 0 To UBound(a)
        ' Check PDF file presence
        If Not fso.FileExists(p & a(i)) Then
            Debug.Print "File not found" & vbLf & p & a(i)
            GoTo ElseI
        End If
        ' Open PDF document
        Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
        PartDocs(i).Open p & a(i)
        ni = PartDocs(i).GetNumPages()
        If i > 0 And ni > 0 Then
            ' Merge PDF to PartDocs(0) document
            If Not PartDocs(0).InsertPages(n, PartDocs(i), 0, ni, True) Then
                Debug.Print "Cannot insert pages of" & vbLf & p & a(i)
            End If
            ' Calc the number of pages in the merged document
            n = n + ni
            ' Release the memory
            PartDocs(i).Close
            Set PartDocs(i) = Nothing
        Else
ElseI:
            ' Calc the number of pages in PartDocs(0) document
            n = PartDocs(0).GetNumPages()
        End If
    Next i
 
    If i > UBound(a) Then
        ' Save the merged document to DestFile
        If Not PartDocs(0).Save(PDSaveFull, DestFile) Then
            MsgBox "Cannot save the resulting document" & vbLf & DestFile, vbExclamation, "Canceled"
        End If
    End If
 
exit_:
 
    ' Inform about error/success
    If Err Then
        MsgBox Err.Description, vbCritical, "Error #" & Err.Number
    ElseIf i > UBound(a) Then
        MsgBox "The resulting file was created in:" & vbLf & p & DestFile, vbInformation, "Done"
    End If
 
    ' Release the memory
    If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
    Set PartDocs(0) = Nothing
 
    ' Quit Acrobat application
    AcroApp.Exit
    Set AcroApp = Nothing
    Set fso = Nothing
End Sub

[hemanthrr]

Dear Sir,

Thanks for your prompt response.

I must admit that i am a newbie to VBA. Hence if its possible, i kindly request you to provide a more step wise procedure to carry out this process. I have attached the excel sheet which i would be using. Under column A, i would be entering the PDF file names. Excel should merge the PDF's entered in column A in the same sequence. Currently i am getting a error saying that "Object variable or With block variable not set"

Thanks

[Kenneth Hobs]

Did you try stepping through each line with F8 to debug and find where it errors?

[Kenneth Hobs]

I see that you did not add the path in C2 nor a filename in B2 for the merged file. Or, just pass the values if you want to hard code the path and base filename.

Your base filenames did not include the file extension. That can easily be added. Ask if you need help with that.

Add the "- 1" in this line.

If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then

[hemanthrr]

Sir,

As mentioned in my previous post earlier, i am a newbie. Could you please give me directions on what exactly to do and i would follow the same.

Thanks

[Kenneth Hobs]

I am not sure what part you need help with. All I can do is to repeat the same instructions.

1. Put the code into a Module.
2. Change one line of code:

'from
If  Not PartDocs(0).InsertPages(n, PartDocs(i), 0, ni, True) Then
'to
If  Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then

3. Pass values to the procedure. You can add the values to cells as I demonstrated in the Test sub or pass directly. e.g.

Sub Test_MergePDFs2()  
  Dim a As Variant
  a = WorksheetFunction.Transpose(Range("A2:A" & Range("A2").End(xlDown).Row).Value)
   MergePDFs ThisWorkbook.Path, Join(a, ",")
End Sub

4. If your base filenames in column A don't have the .pdf file extension, it can be added in a macro. e.g.

Sub Test_MergePDFs3()  
  Dim a, i As Long
  
  a = WorksheetFunction.Transpose(Range("A2", Range("A2").End(xlDown)))
  
  For i = LBound(a) To UBound(a)
    a(i) = a(i) & ".df"
  Next i
  
  MergePDFs ThisWorkbook.Path, Join(a, ",")
End Sub