Macro to Merge PDFs from Excel

Posted to this site, is a macro that will allow you to select a folder and merge all of the PDFs inside. The macro requires that you have Adobe Acrobat installed. I've tested it out and it works quite well. On the line beginning, Const DestFile As String = you can specify what you want the merged file to be named.

In the Tools menu in Visual Basic you need to select References . . . Adobe Acrobat Library, or just Acrobat, before you can run the macro.

After pressing play the macro will prompt you to select a folder. When the newly merged file is created, you'll receive a message.

I have posted the code below, but if you have trouble copying and pasting this into Visual Basic, try the link above. The Wix editor can mess with the formatting of VBA code.

Sub Main() Const DestFile As String = "MergedFile.pdf" ' <-- change to suit Dim MyPath As String, MyFiles As String Dim a() As String, i As Long, f As String ' Choose the folder or just replace that part by: MyPath = Range("E3") With Application.FileDialog(msoFileDialogFolderPicker) '.InitialFileName = "C:\Temp\" .AllowMultiSelect = False If .Show = False Then Exit Sub MyPath = .SelectedItems(1) DoEvents End With ' Populate the array a() by PDF file names If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\" ReDim a(1 To 2 ^ 14) f = Dir(MyPath & "*.pdf") While Len(f) If StrComp(f, DestFile, vbTextCompare) Then i = i + 1 a(i) = f End If f = Dir() Wend ' Merge PDFs If i Then ReDim Preserve a(1 To i) MyFiles = Join(a, ",") Application.StatusBar = "Merging, please wait ..." Call MergePDFs(MyPath, MyFiles, DestFile) Application.StatusBar = False Else MsgBox "No PDF files found in" & vbLf & MyPath, vbExclamation, "Canceled" End If 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 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

Adobe Action to Remove PDF/A Formatting

Rick Borstein has posted an action for Adobe Acrobat to this page, which you can use to convert PDF/A archive files to the standard PDF format. The action will convert multiple files en masse.

The action uses Acrobat's Preflight functions. In Adobe Acrobt XI Pro these are found in the Tools menu in the Print Production section.

On the Profiles tab you'll see a function to remove PDF/A information.

Borstein's action allows for this Preflight function to process multiple PDFs. Borstein has posted instructions on how to install the action which can be downloaded as a PDF. The actual action .sequ file is embedded in the PDF file. You access it by clicking on the paper clip icon on the left. Just save the .sequ file on your hard drive and then double-click on it from Windows Explorer to install it.

If you don't have any PDFs open when you activate the action, 'Remove PDFa Information' from the Action Wizard menu it will prompt you add multiple files in the second step.

Adding Links to PDF Attachments

A single PDF file may have attachments of other PDFs (or Word, Excel, and other types of files) embedded in it. Tonight's tip shows how to add in links to these attachments from the main PDF.

In Adobe Acrobat, clicking on the paper clip icon on the left side menu will open a pane listing attachments. Click on Add to insert multiple attachments.

Click on the Tools tab in the right side menu, and click down on the Content Editing section. Then click on Add or Edit Link. Draw a box around the citation in the main PDF that you want to add a link to.

In the dialog box that appears, choose the Link Action - 'Go to a page view', and then click 'Next'

The next step is to set the link. To do this click open the attachment from the pane on the left, and then press the Set Link button.

The final step ensures that the link will open in a separate Adobe application window and not close the main file. Right click on the link box, choose Properties, click on the Actions tab, select where it says 'Go to a page in another document' and then click Edit. In the resulting dialog box, change the Open to setting to 'New Window' and click 'OK'.

A PDF created in Adobe Acrobat with such links will function in Adobe Reader allowing a user to open and close attached files by clicking on the hyperlinks.