Did you ever need to fill out multiple PDF forms faster and without mistakes? I’m sure that, if you’ve done this manually, on regular basis, you agree that it is tedious and time consuming.
If you have Adobe Acrobat Pro installed OR a trial version (even if it expires and you don’t want to purchase a license), then you can use an Excel App to Fill Out PDF Forms automatically, directly from Excel. Read the article and download the tool from here: excel-app-to-fill-out-pdf-forms
If you prefer the manual method, then continue reading this page for an alternative method.
PDF forms are very powerful and widely used. Unfortunately, many users are not aware of a simple but useful feature of Excel: Export XML Data. However, this is about to change, right? 🙂 This feature can be accessed from the Developer Tab, XML Group from Ribbon. (If the Developer tab is not visible in Excel’s Ribbon, you have to activate it first, from Excel Options, Customize Ribbon – check the Developer check box on the right side of the screen)
Even for users that are aware of this feature, setting the correct export structure is not so obvious, this is the very reason I chose to write this article, which, by the way, is also my first article about Excel.
To be able to do what you just have seen in the above images, you have to successfully complete 3 steps:
Let’s start with Excel, as it’s probably the most familiar step for you. The data structure I prepared for this example is very simple, a defined table with Employees information in one sheet, and in another sheet, I’ve prepared a simple list, where data is populated from Employees table using very simple formulas, based on a drop-down selection. You can start by downloading the files used in this example:
The data structure requested by the PDF Form may vary; such as, if the PDF Form has simple fields, then you can set any structure you want, in a range of contiguous or non-contiguous cells. If the PDF Form has a table form, then you have to prepare data to be exported from a defined table.
This xml file will be imported into Excel and mapped with the workbook cells.
This step has nothing to do with Excel, you will need Adobe Acrobat Pro for this stage. If you do not have Acrobat Pro, and you do not intend to buy that software, you can use FoxitReader , it’s a free PDF Viewer software that you can use to import or export data from a PDF Form. Another free PDF Viewer that enables users to import – export form data in the Free version comes from: PDF XChange Viewer. However, this last PDF XChange Viewer can export form data in XML format, but can import only AcroForms data in FDF and xFDF format, not in xml format. This PDF form used for this example is not a simple AcroForm, it’s an XFA form created with LiveCycle Designer. AcroForms are the original PDF forms technology. If your form is created with Adobe Acrobat, you’re lucky, many 3rd party vendors support AcroForms.
To Extract the XML structure of the PDF Form using Foxit Reader:
Export PDF Form Data – Adobe Acrobat Menu
Export PDF Form Data – Foxit Reader
That was easy 🙂 Now, let’s see the last step:
Here we are at the last step, I’m sure you’ll find it as easy as the previous steps…
First, you should delete the existing XML map from the downloaded file, this way you can practice the following steps. (press the XML Maps button, select the Map and click Delete.)
From the Develover tab in Excel, click on Source Button, this should open the XML Source panel. At the bottom of this panel, you should see the XML Maps button; click on it, the XML Maps dialog window is now open; choose Add and browse for that .xml file exported in step 2 (you will receive an error message like: “ The specified xml source does not refer to a schema. Excel will create a schema based on the xml source data ” – you may ignore this message, Excel expects an XSD validation schema, but it will accept your xml file, and it will automatically create a schema based on the structure found on xml file)
After adding the xml source, the mapping is very easy: simply drag xml fields to the corresponding worksheet cells. You have 3 ways to map a cell: drag the element to cell, right click the element and choose to Map Element, or select the cell and double click the element. The following video will illustrate this process (I’ll use all those 3 methods to map a cell, use the one you like):
At this point, the Excel file is ready to produce any number of xml files with employees data, in seconds… From the Developer Tab, use the export button to create the output xml files; select another employee from dropdown and press export button again, and so on. Of course, you can automate this process with a simple macro, if you want even faster results.
You can send the data in xml format to your recipients (the xml type is one of the most used data transportation formats), you don’t have to send very large PDF files, if they have the PDF form, they can load the data themselves using the Import Data command.
Your opinion matters, if this article was helpful for you, or if you want to share other techniques, you can leave a reply.
