How to Merge PDF Files Using Excel VBA Without Adobe Acrobat?

Sharing is caring!

Are you looking for a way to merge multiple PDF files into a single document without needing to purchase or install expensive software like Adobe Acrobat? With some straightforward VBA (Visual Basic for Applications) coding in Microsoft Excel, you can easily automate the process of combining PDFs right from your Excel workbook. This article will walk you through the steps to merge PDFs using Excel VBA, no Acrobat required.

What You Need to Merge PDFs in Excel VBA

Before getting started, make sure you have the following:

  • Microsoft Excel (version 2013 or later recommended)
  • The PDFs you want to merge saved in an accessible folder
  • Basic knowledge of Excel and VBA

Once you have these prerequisites ready, you can move on to the VBA coding to merge your PDFs.

VBA Code to Merge PDF Files

The key to merging PDFs with VBA in Excel is to use the Windows API (Application Programming Interface) Shell function to run a command that will combine the PDF files. Here are the steps:

  1. Open your Excel workbook and press Alt+F11 to open the VBA editor
  2. In the Project pane on the left, double-click “ThisWorkbook” to create a new code module
  3. Copy and paste the following VBA code into the code window:
Sub MergePDFs()
    Dim ws As Worksheet
    Dim strPath As String, strFile As String
    Dim strPDFs As String, strMergedFile As String

    Set ws = ThisWorkbook.Sheets("Sheet1") 'Change sheet name if needed
    strPath = ws.Range("A1").Value 'Assumes folder path is in cell A1
    strMergedFile = ws.Range("A2").Value 'Assumes name of merged file is in A2

    strFile = Dir(strPath & "\*.pdf")
    While strFile <> ""
        strPDFs = strPDFs & " " & strPath & "\" & strFile
        strFile = Dir
    Wend

    strPDFs = Right(strPDFs, Len(strPDFs) - 1)

    strShell = "C:\Program Files\PDFtk\pdftk.exe " & strPDFs & _
        " cat output " & strPath & "\" & strMergedFile & ".pdf"

    Call Shell(strShell, vbHide)

    MsgBox "PDFs merged successfully!", vbInformation

End Sub
  1. Customize the variables in the code as needed:
  • ws is the worksheet variable where you’ll specify the folder path and merged file name. Change “Sheet1” to the name of your worksheet if different.
  • strPath is where you’ll enter the full folder path where your PDF files to merge are located. Put this path in cell A1 of your worksheet.
  • strMergedFile is the name you want to give to your merged PDF file. Put this name in cell A2 of your worksheet.
  1. The line strShell = "C:\Program Files\PDFtk\pdftk.exe " & strPDFs & " cat output " & strPath & "\" & strMergedFile & ".pdf" actually merges the PDFs using a free command-line tool called PDFtk. You’ll need to download and install PDFtk Server for this to work.
  2. After installing PDFtk and updating the code, click the green “Run” button or press F5 to execute the macro and merge your PDFs!

Merge PDFs Code Explanation

Here’s a quick overview of what the VBA code is doing:

  • Sub MergePDFs() defines the macro name
  • Dim statements dimension the variables used in the macro
  • Set ws = ThisWorkbook.Sheets("Sheet1") sets the worksheet where folder path and file name are specified
  • strPath and strMergedFile retrieve the folder path and file name from the worksheet
  • strFile = Dir(strPath & "\*.pdf") and While loop build a space-delimited list of PDF file paths
  • strPDFs = Right(strPDFs, Len(strPDFs) - 1) removes leading space from PDF list
  • strShell defines the PDFtk command to merge the PDFs, using the PDF list and output path/filename
  • Call Shell(strShell, vbHide) executes the PDFtk command in hidden mode to merge the PDFs
  • MsgBox displays a success message when the PDFs are merged

So in summary, the macro retrieves the PDF folder path and merged filename from the spreadsheet, builds a list of the PDFs in that folder, then uses PDFtk via the Windows Shell to merge the PDFs into the output file.

Tips for Using the PDF Merge Excel VBA Macro

Here are a few tips and things to keep in mind when using this macro to merge your PDFs in Excel:

  • Make sure the folder path in cell A1 is the full, absolute path (not relative)
  • Include the “.pdf” extension in cell A2 for the merged file name
  • The macro will combine the PDFs in alphanumeric order, so you may want to add numeric prefixes to your PDF names to control the order (e.g. “01 Intro.pdf”, “02 Chapter1.pdf”, etc.)
  • If you get an error, double check that the folder path is correct, the PDFtk executable is installed in the expected location, and that you have permission to write files to the output folder
  • The free PDFtk Server tool only works on Windows. For Mac users, you could try using the cpdf command line tool instead which has similar functionality. Just modify the strShell line in the macro to use the appropriate cpdf command.
Operating SystemPDF Merge ToolDownload Link
WindowsPDFtk Serverhttps://www.pdflabs.com/tools/pdftk-server/
Maccpdfhttp://community.coherentpdf.com/

With either of these free PDF tools installed, the Excel VBA code makes it easy to create a simple macro to automatically merge multiple PDFs files into a single PDF document with just a click.

Why Use Excel VBA to Merge PDFs?

You might be wondering, why bother using Excel VBA to merge PDFs instead of just using Adobe Acrobat or another PDF tool? Here are a few reasons this macro approach can be useful:

  • Cost savings – Adobe Acrobat, Nitro PDF, and other full-featured PDF software can be quite expensive, especially if you only need to do basic tasks like merging files. The Excel VBA method lets you merge PDFs for free using tools you likely already have.
  • Flexibility – With the VBA code, you have a lot of flexibility and control over the merging process. You can easily specify the input folder, get the PDFs in the right merge order, and name the output file whatever you want. The sky’s the limit in terms of customizing the macro.
  • Batch processing – Need to merge a whole bunch of PDFs? No problem – with a quick tweak, you could adapt the macro to loop through multiple sets of PDF files and merge them all in one go. Try doing that easily with the manual Acrobat method!
  • Integration with other data – Since the macro is run right in Excel, you could build in additional functionality to read PDF merge lists from your worksheet, save information about the merged file to another sheet, or tie the PDF functionality in with other Excel data processing. It’s really handy to have it all in one place.

Of course, there are still times when a dedicated PDF tool is the better choice – for example, if you need to heavily edit the PDFs, deal with PDF forms, or do other advanced tasks. But for quick and easy PDF merging, Excel VBA is a great option to have in your toolkit.

Final Thoughts

As you can see, merging multiple PDF files into one document is easy and free using VBA in Microsoft Excel, thanks to the Windows Shell command and free PDF tools like PDFtk Server or cpdf. With a short snippet of code and a few variables set in your spreadsheet, you can build a custom PDF merge macro that will save you time and effort. Hopefully this guide has helped de-mystify the process of combining PDFs in Excel without Adobe Acrobat. Give it a try and see how it can streamline your PDF workflow!

FAQs

What is the benefit of using Excel VBA to merge PDFs?

Using Excel VBA to merge PDFs offers several benefits, including cost savings (as you don’t need expensive software like Adobe Acrobat), flexibility in customizing the merge process, the ability to batch process multiple sets of PDFs, and integration with other Excel data and functions.

What do I need to merge PDFs using Excel VBA?

To merge PDFs using Excel VBA, you’ll need Microsoft Excel (version 2013 or later recommended), the PDFs you want to merge saved in an accessible folder, and basic knowledge of Excel and VBA. You’ll also need to install a free PDF command-line tool like PDFtk Server (for Windows) or cpdf (for Mac).

How do I install and use PDFtk Server for merging PDFs?

To use PDFtk Server for merging PDFs with Excel VBA on Windows, first download and install PDFtk Server. Then, in your VBA code, make sure the strShell line points to the correct installation path for the PDFtk executable (usually "C:\Program Files\PDFtk\pdftk.exe").

Can I merge PDFs with Excel VBA on a Mac?

Yes, you can use Excel VBA to merge PDFs on a Mac, but you’ll need to use a different command-line tool since PDFtk Server only works on Windows. One option is cpdf, which has similar functionality. Just modify the strShell line in the VBA code to use the appropriate cpdf command for merging PDFs.

How can I customize the PDF merge macro in Excel VBA?

You can easily customize the PDF merge macro in Excel VBA to fit your needs. Some options include specifying the input folder path and merged PDF filename in specific cells in your workbook, adding numeric prefixes to your PDF names to control the merge order, looping through multiple sets of PDFs for batch merging, or integrating the macro with other Excel data and functions.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *