I have a love/hate relationship with Adobe Acrobat. I love the product’s ubiquity, but it’s always frustratingly difficult to work with it. Have you ever had to fill a PDF form from Excel, like this one:
Since tomorrow is tax day in the USA (April 15), I thought it only appropriate to use a PDF form from the zillions available at IRS.gov.
There’s quite a few ways to go about this. Most involve using 3rd party controls, DLLs, or automating Acrobat itself, which will only work with the full (and very expensive) version of Acrobat, not the Reader. But there is a very old, very light weight and very free technology from Adobe that will allow you to populate such forms. It’s called FDF, which stands for Forms Data Format.
FDF is lightning fast and was developed for servers to send PDF form data back and forth and to enable the creation of complex workflow routing applications. We will be using it in a basic fashion, because all we need to do is fill a master pdf form with a selected record on a worksheet. Adobe has a free toolkit (DLLs and documentation) you can use to create the FDF files, but the format for our purposes is trivial and as a result we can create the FDF files with straight VBA.
If you download the attached ZIP archive, you will find an Excel workbook (.xls) and the IRS form from above. Just enable macros, click on any record from the Clients worksheet and then click the Make FDF button. Acrobat will open with the IRS form populated with the fields from the record you selected!
The macro creates the .FDF file and writes it to the workbook folder, and then shells out to Windows to open that FDF file. Adobe Acrobat or Acrobat Reader respond and open the PDF file that the contents of the FDF file specify. The FDF file also has the field values and with that information Acrobat or Reader does the magic of merging the field values into the specified PDF, automatically.
The format of the FDF file is simple, with a header and footer and a middle section that specifies all of the fields and their values. Here’s the contents of the FDF file that resulted in the IRS form (image) above:
%FDF-1.2 %âãÏÓ 1 0 obj<</FDF<</F(f8655.pdf)/Fields 2 0 R>>>> endobj 2 0 obj[ <</T(f1_01(0))/V(Daniel Ferry)>> <</T(f1_02(0))/V(12)>> <</T(f1_03(0))/V(3456789)>> <</T(f1_06(0))/V()>> <</T(f1_04(0))/V(Excel Hero)>> <</T(c1_1(0))/V(No)>> <</T(f1_05(0))/V(123 Excel Dr)>> <</T(f1_07(0))/V(Highwater, KS 43567)>> <</T(f1_08(0))/V(Daniel Ferry)>> <</T(f1_09(0))/V(555)>> <</T(f1_10(0))/V(123-4567)>> <</T(f1_11(0))/V(555)>> <</T(f1_12(0))/V(123-4568)>> ] endobj trailer <</Root 1 0 R>> %%EO
You can specify values for as few or as many form fields as you like, and in any order; just put one line for each in the middle section. There are only two types of Acrobat fields, Text and Check Box. The Check Box values can be Yes or No. It’s really that simple.
In the above example, f1_01(0) is the name of the first field on the Acrobat form; f1_02(0) is the second and so forth (hey, the IRS named them, not me!).
One hurdle you will need to pass, if you did not author the PDF form yourself, is to figure out the names of the fields. If the form is not protected you can easily do this from Acrobat by going into form editing. If the form is protected, as the IRS forms are, you need a different approach. I’ve included an old VB routine to list all of the fields and their types in Module 2 of the workbook. You will need Acrobat and not just Reader for it to work. Once you know the names of the Acrobat form fields, it just takes a little trial and error to see which names go with the actual fields you see displayed. Of course, a more descriptive naming convention (from the form’s author) would help!
The macro in Module 1 has been coded for simplicity and ease of understanding, not efficiency. The method could be enhanced in any number of ways, to allow for outputting a PDF for every record in batch mode for example, and many other things. As it stands now, you need to click on one record in the Clients worksheet and then click the button – and that will make one merged PDF.
Let me know how you use it. Here’s the archive: