Excel Acrobat PDF Form Filler

| 11 Comments | 0 TrackBacks

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:

PDF_Tax_Form.png
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:

Enhanced by Zemanta
If you liked this article, please share it!



Your Ad Here

No TrackBacks

TrackBack URL: http://www.excelhero.com/cgi-bin/mt/mt-tb.cgi/32

11 Comments

WOW!!!
Been working a lot with acrobat forms.
This is definitely THE most useful tip I have come across.
Thanks very much.

This MAY be the most helpful thing in the world... I'll let you know how i feel once i've populated a few hundred IRS forms!!!
If only i knew what all the fields were called.

Anybody done similar with an IRS 1042-S form from 2008 or 2009?

Excellent article.

Do you know how flatten a PDF file from Excel?
Thanks

This article has been extremely helpful to me as well! Thank you so much! I borrowed your macro code and modified it for the excel spreadsheet and pdf file I needed it for. It works perfectly with the single cells with names and numbers and things like that, but I want to try to take a range of cells out of the spreadsheet (a table) based on the input in column N for example and populate a field in the pdf and cannot figure out how to write the code for this. Any thoughts???

I'm really interested in using this... I have Excel 2007 and Adobe Acrobat 9. The .pdf I'm working with is protected, so I'm trying to determine the field names using the macro in Module 2.

As soon as I run that macro, Excel shuts down.

This tip was really great and saved me a huge amount of time. I was going around in circles looking for a simple way of uploading and modifying data from a MSWORD 'form' into an Adobe X Pro form now I just cut/paste to excel and run a script based upon your excellent example.
Thank You !!!!

Hey guys! I see you have a problem converting excel to pdf. I ran into that awhile ago, and I found a similar program to be quite useful, especially in business. At my work we use this program that quickly and painlessly converts my excel documents into pdf. It provides my business a simple way to make individualized statements for our business associates. Check it out here: http://www.nirvaha.com/excel-to-pdf.html

Daniel, to echo what's been said already, this is an extremely useful bit of information. I'll be honest, I don't entirely understand how it works, but after some experimentation I was able to exploit the code for personal use. I'm hoping to eventually learn how to write code like this (I'm strongly considering EHA as my preferred method of learning).

Many thanks for the excellent content on your website.

this is a great article, with usefull information. but i got a little problem... i was trying to change the excell file and the pdf file by my needs, but don-t know how to sort the macro.

Anyone could help me plz?

Kind regards.

I need help with this macro:

%FDF-1.2
%âãÏÓ
1 0 obj>>>
endobj
2 0 obj[
Am I correct in assuming I would just replace “f8655.pdf” with “Commissioning Report.pdf”  the name of my PDF and the rest would stay the same?

Next you have:

>
>
>
I get that the “f1-01(0)” is the name of the first field on your PDF, so I would replace that with let’s say “PCS #” the name of the first field I want filled in, correct?

The next part of the command is “/V(Daniel Ferry)>>” I’m pretty sure I’m not going to put that because I want to pull whatever data is in the “PCS #” field of my spread sheet, which is column 1, so what do I write there to tell it to pull the data from column one of the Excel file for that spot on the PDF

Then this is the end of the macro:
]
endobj
trailer
>
%%EO
Am I correct that I would leave this unchanged for my macro as well?

So in the end here is your whole code (I shortened it a little to save space:

%FDF-1.2
%âãÏÓ
1 0 obj>>>
endobj
2 0 obj[
>
>
>
]
endobj
trailer
>
%%EO

So I would take my stuff and put it where “f1_01(0)” is and put whatever goes int the place of “Daniel Ferry” Then I would copy the whole thing. Click on “Visual Basics”  Sheet 1  and paste the code into there and it should work?

Thanks in advance for your help.

This is a really sexy article.

Been ages really since I have done a lot of Office/VBA programming and combining that with other needs, well you saved me a lot of time.

Thank you.

I guess the only thing left is to find a deeper reference on FDF

Leave a comment

About this Entry

This page contains a single entry by Daniel Ferry published on April 14, 2010 3:26 PM.

Excel Optical Illusions #8 was the previous entry in this blog.

Excel Location Mapping is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.