Combine/Merge PDFs from different folders using the file names in worksheet cell references

[rajeshn65]

Hi everyone,

I am trying to merge two PDFs in two different folders based on the PDF file names in the excel range A2:A & B2:B.
For Example:
D:\Project 3\Sample RV PDFs is folder for 1st part of PDF
and
D:\Project 3\Sample DR PDFs is the folder for 2nd Part of PDF

The 1st PDF to be Picked based on the PDF name from range A2:A and 2nd PDF to be picked based on the PDF name from range B2:B and they are to be combined in the same sequence and can be placed in first folder i.e. D:\Project 3\Sample RV PDFs. with the merged name being name of the first PDF or with a prefix or suffix with any letter to avoid errors.

From this forum I have found VBA code to merge 2 pdfs based on the only one file path and name strings being fixed and not based on the cell references.

Will be very grateful if anyone could help me with the above.

Thanks in advance

the VB code which I got is:

Sub MergePDFs()


' --> Settings, change to suit
Const MyPath = "D:Project 3\Test PDFs" ' Path where PDF files are stored"
Const MyFiles = "p1.pdf,p2.pdf" ' List of PDFs to ne merged
Const DestFile = "MergedFile.pdf" ' The name of the merged file
' <-- End of settings

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

If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
a = Split(MyFiles, ",")
ReDim PartDocs(0 To UBound(a))

On Error GoTo exit_
If Len(Dir(p & DestFile)) Then Kill p & DestFile
For i = 0 To UBound(a)
' Check PDF file presence
If Dir(p & Trim(a(i))) = "" Then
MsgBox "File not found" & vbLf & p & a(i), vbExclamation, "Canceled"
Exit For
End If
' Open PDF document
Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
PartDocs(i).Open p & Trim(a(i))
If i Then
' Merge PDF to PartDocs(0) document
ni = PartDocs(i).GetNumPages()
If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
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
' Calc the number of pages in PartDocs(0) document
n = PartDocs(0).GetNumPages()
End If
Next

If i > UBound(a) Then
' Save the merged document to DestFile
If Not PartDocs(0).Save(PDSaveFull, p & DestFile) Then
MsgBox "Cannot save the resulting document" & vbLf & p & 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 is created:" & 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

End Sub