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