Download Excel App to Fill Out PDF Forms
Excel App to Fill Out PDF Forms
In an older article: Use Excel to populate PDF Form Fields, I presented a manual method to transport data between Excel and PDF forms, that can be used without a paid version of Adobe Acrobat.
If you have Adobe Acrobat Pro installed, then you can use an Excel App to Fill Out PDF Forms automatically, directly from Excel.
First, download the sample PDF form and the Excel Form Filling Tool from the link below:
Download sample files: Form Filling Tools.zip
How Data is Organized:
First of all, there is a main folder, named Form Filling Tools, that will contain a folder with Templates, all your PDF templates must be stored in the Templates folder.
Inside the Form Filling Tools folder, there is an Excel file named Fill PDF Form.xlsm, this will be our Excel tool that will help us Fill PDF Forms automatically.
When the Excel App will fill a form, it will save it in a folder named Filled Forms, which will be created by code inside the main folder – Form Filling Tools.
The Filled Forms folder is not in the archive you downloaded from the above link, it will be created automatically.
Also, the code will create a sub-folder in Filled Forms for each row of data.
If you have a PDF template to fill for John Doe for example, a sub-folder named John Doe will be created in Filled Forms folder, and that PDF template will go into John Doe folder.
Each PDF created by code will have a time stamp in the file name, to avoid overwriting older forms and to allow you to find the most recent versions.
How to Use the Excel App to Fill Out PDF Forms
I tried to build a flexible structure, to allow users to use this tool with their own PDF Forms. As a result, there are 2 important sheets in the Fill PDF Form.xlsm:
- Data, which holds a defined table with all your source data, that needs to be sent to PDF forms.
- Templates List. This sheet will have the details of the selected PDF Template from the Templates folder, the table must be updated using the button named Update Templates List from this sheet. In this version, only 1 PDF Form will be listed.
Important settings you need to make before Starting to Fill Out PDF Forms:
In order to start using the tool, you have to do the following steps:
- Press the button named Update Templates List from Templates List sheet, the code will ask you to select a PDF from the templates folder, and for that PDF form will create a new worksheet, with all the form fields listed: PDF field Names, field User Names and field Type.
- After the first step, a new sheet will be created for the selected form. The first column in the template sheet has drop-downs, referring to the Data table Headers, you will have to map each field from Data table to the corresponding PDF Field Name from column B: look at column B (PDF field name, and select from column A drop-down the value that should be sent to that field).
Because the PDF Field Names can be different from your Data Table Headers, without this mapping, no data will be sent to PDF form, it’s the only reasonable way to make an association between your Data Headers and the PDF Field Names.
Start Filling the PDF Forms
Finally, we can start filling those Forms! Go to Data sheet and double-click on a name from column A, this is the event that triggers the Form Fill Action.
The code will create a copy of the PDF Form from the Templates folder and it will fill it with data from the selected row from Data sheet.
Other things you should know:
In conclusion, I have to emphasize a few issues that might raise errors in this process:
- This Excel App to Fill Out PDF Forms will work only with AcroForms, you will not be able to fill forms that were created with Live Cycle Designer (those are XFA Forms).
- If you did not create the PDF Form, or you created the form automatically with Acrobat Tools, most probably you will find a few ambiguous PDF Fields Names, like TextBox1, TextBox2.
It will not help when you do the data mapping. You will have to edit the form to rename those fields, then use the button to Update the Templates List.
There are many improvements that can be made. For example, you may want to:
- pre-fill the PDF templates from your Templates folder with data, and fill only some fields from Excel.
- Or, you may want to type default values for some fields, because you don’t have data for those fields in your Data table.
- Also, a user may want to fill lots of PDF Forms, stored in Templates folder and in subfolders.
- Another frequent operation some user may need to do is to fill forms for each row in Data sheet, not just one row at a time.
You can do these changes by editing the code, if you have experience with VB programming.