Excel First

Excel Solutions

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:

  1. 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.
  2. 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.

Enjoy!

Catalin Bombea

45 Comments to Download Excel App to Fill Out PDF Forms

  1. Sue says:

    I need a personal lesson on this topic! are you available for hire?

  2. Michelle says:

    stumbled across this page, and it worked like a champ on my computer. now I’m needing it to work on a mac for a co-worker and was wondering if you/someone already found the solution.

  3. Andy says:

    Hi There. Great solution! Do you have a solution that would create pages with each row and fill in the row below (similar to a mail merge)?

    • Hi Andy,
      No, but you can do it in a different way: if you have a template PDF with 1 page (or more), you can fill it with data for all your table data rows, when all pdf’s are filled, it’s very easy to merge them into a single PDF.
      Regards,

  4. Kristine Smith says:

    how do you check checkboxes or radiobutton fields

    • Hi Kristine,
      Depends on the properties of the control.
      For radio buttons, if you have 10 radio buttons and they are grouped with only 1 option possible, you will have to send a value from 1 to 10.
      A checkbox might accept values like: Yes/No, True/False, Yes/Off. The easiest way to see what values that control accepts is to check the checkbox, select a value from radio buttons, and Manage Form Data-Export data – into an xml file. If you open that xml, you will see the control name and the value.
      Cheers,
      Catalin

  5. sjs says:

    how could I modify the VBA script to trigger on a button click instead of double clicking A1?

    • Hi,
      If you want to assign a button to run the code, you need also a new way to indicate what row needs to be transferred to PDF. Using the right click in that row already does that, the code knows what row triggered the code.

      You will have to insert in code an InputBox, where users should select the row that needs to be transferred.

  6. Christopher Bick says:

    Does this really save the time it takes to type the data into the PDF? We file numerous, non-uniform reports, would we need to set this up only once and it would be there for next year to auto fill the new information??

    • Hi Christopher,
      Each PDF file needs to be mapped with excel, the mapping is a one time process, once it’s set you can reuse it (until the PDF form structure is changed, this usually happens when the owner releases a new version). Mapping is easy, you can download the excel file and test it.
      Regards,
      Catalin

  7. Mel Brown says:

    There is no drop down list appearing in the source column. How do select what data should appear in the drop down list. Do I need to modify the code to specify what “sheet” contains the data? Thanks

    • Hi Mel,
      After you set the pdf file path in Templates List sheet, press the button from that page, and a new sheet will be created. That sheet will have dropdowns in Source column (first column), and the dropdown values are coming from the Data sheet table headers, this is your source data. The pdf fields will be listed in column B of the new sheet.

  8. Jonathan C. says:

    Hi there, first of all thank you so much this help to save a lot of time on my work, I have a question i tested this whit a trial version of adobe acrobat pro? it’s this still going to work after my trial ends?

    • Hi Jonathan,
      That’s related to Acrobat, no idea how they stop the trial, with just warnings or limited functionalities.
      Regards,
      Catalin

      • Jonathan C. says:

        Hello, I’m back so this happend my license trial of adobe acrobat expired two day’s ago, and I can still use this excel template, no need to buy the membership, just to let all u know. ๐Ÿ™‚
        Thanks for this tutorial save a lot of time in my work.
        Have a great day!! ๐Ÿ˜€

        • Hi Jonathan,
          Good to know, thanks for sharing.
          For excel, only the acrobat library is needed- Acrobat.tlb, which can be found in C:\Program Files (x86)\Adobe\Acrobat xx.x\Acrobat (depends on acrobat and windows versions).
          Even if the acrobat trial is expired, the library is still there and can be used. Looks like a good clean way to get the library, without hacking the Acrobat files.
          Cheers,

  9. Greg says:

    Hi,

    I had a sudden need of this, found your solution, enjoyed and relaxed ๐Ÿ™‚

    Thanks a milion for this brilliant piece of VBA. The only addition would be a button to process all the rows at once. But I can easily add it myself once I stop being lazy ๐Ÿ™‚

    Cheers

  10. saul velasquez says:

    Why i can’t add more than one form? i am not getting the option shown in the first picture, “select pdf template”

    • Hi Saul,
      It’s a demo file, built for a single form.
      If you need more forms to be filled in the same time, it needs to be modified to work as you want. The multi-form version is not free unfortunately.
      Regards,
      Catalin

  11. Molly says:

    how can I do it so that the “PDF form file can be in an existing workbook in an existing folder rather than in the “form filing tools” folder?

    • Hi Molly,
      It is possible, but the code gets more complicated, embedded objects are stored in binary format in the excel archive. Code have to read the binary and convert it back to pdf, not sure if it’s a good idea to do it this way.

  12. duhmarus says:

    This is amazing! I wish I could have it merge a bunch of documents at the same time but still, this is a major time saver. I’m teaching myself VBA code now and can’t wait until I learn how to edit this one.

    GIRLS WHO CODE!!

    • It is amazing, congrats ๐Ÿ™‚
      Good luck, and have fun!
      Well, it’s not always fun, frustrating sometimes, but when you make it through, beats everything.
      Girls power, even better.

  13. Tobe says:

    Hi,
    I stumbled on your page and this is exactly what I have been looking for. Thanks a lot for sharing the knowledge.

    I downloaded the zip and followed your instruction. I double-clicked on each name and I saw each folder was created for that particular name. What can I do to automate the creating of all PDF’s for all names instead of double-clicking one name at a time? Please advise. Thanks a lot

    • Hi Tobe,
      You can use this code that can send all rows to pdf’s:

      Sub FillAll()
      Dim Sh As Worksheet, tbl As ListObject, Cell As Range
      Set Sh = ThisWorkbook.Worksheets("Data")
      Set tbl = Sh.ListObjects(1)
      For Each Cell In tbl.ListColumns(1).DataBodyRange
      FillSelectedForms Sh, Cell.Row
      Next
      End Sub

      Q: “Also, how do I add more columns and rows on tab Employee Information. Thanks”
      The fields are extracted from the pdf form in the Employee_information sheet, which contains the mapping between the data table and the pdf form. Id does not make sense to add rows and columns in the employee information sheet, these are added automatically from the pdf, when you use the button from “Templates List” sheet to update the pdf template used. If your pdf has more fields, they will show up in the mapping sheet. If you manually add rows in the Employee Information table, this action will NOT add new fields in the PDF form…

      Please read again the article, you might be misunderstanding some things, the application is driven by what is in your pdf form, this is not a pdf form designer.
      If you want to use another pdf, go to templates list, use the button to select another pdf form, change the data table to the data structure needed for the new form, then do the mapping in the new sheet.

  14. Tobe says:

    Hi,
    Thank you very much for your reply. I just want to let you know that I have started enjoying your Excel PDF Mail Merge more and more everyday. So far I have played with different forms and they are all successfully created. As far as the codes, may I ask you to show me where to insert these codes? I really appreciate your sharing of knowledge.

  15. Jonathan says:

    Hi Catalin! Thank you so much for this. I included the code to send all rows to PDF but is there a way to disable the “ok” button confirming the creation of each pdf? Alternatively, is there a code to just click it each time? Thank you again.

    • Hi Jonathan,
      I suggest searching the code for the message you are seeing, for example, using Ctrl+F (same old search shortcut in excel works in visual basic too) to search for “created successfully!” will take you to that line. Put an apostrophe at the beginning of the line will disable it.
      Make sure you set the search to look into entire project.
      I could just indicate the position of the line, but don’t want to spoil your fun ๐Ÿ™‚
      Cheers,
      Catalin

  16. Tobe says:

    Hi Catalin,
    I have played with your EXCEL codes and a single click on a row to send to PDF or send all rows to PDF. Recently, I have thought about how to send some selected rows to PDF because let’s say there are 100 rows (records) and after sending all to PDF, we realize that only 10 rows need to be corrected due to typing errors. So, instead of sending single row at a time or sending all rows again, there should be a way to send just selected rows to PDF. If you have any code or suggestion, please share with us. Thanks a lot

    • Hi Tobe,
      There are many ways: you can add a column with values of 1 for the rows you want to send, and check that column value before sending the row to pdf.
      Or, you can filter the table to keep visible only the rows you want to send. In code, simply check the row height: if it’s 0, don’t send it.
      Cheers,
      Catalin

  17. Tobe says:

    Thank you very much for a very quick response. I decided to use a filter and it showed only 10 records and hid the other 90 records. Then I used this codes below and it sent all 100 records to PDF instead of 10 wanted records.

    Sub FillAll()
    Dim Sh As Worksheet, tbl As ListObject, Cell As Range
    Set Sh = ThisWorkbook.Worksheets(“Data”)
    Set tbl = Sh.ListObjects(1)
    For Each Cell In tbl.ListColumns(1).DataBodyRange
    FillSelectedForms Sh, Cell.Row
    Next
    End Sub

    • Hi Tobe,
      As mentioned, you have to adjust the code to check the row height:
      Sub FillAll()
      Dim Sh As Worksheet, tbl As ListObject, Cell As Range
      Set Sh = ThisWorkbook.Worksheets(โ€œDataโ€)
      Set tbl = Sh.ListObjects(1)
      For Each Cell In tbl.ListColumns(1).DataBodyRange
      If Cell.RowHeight > 0 Then FillSelectedForms Sh, Cell.Row
      Next
      End Sub

      This will allow you to use filters to send only visible data.
      Cheers,
      Catalin

  18. Tobe says:

    I just want to say Thank You Very Much for your help. Your script is very powerful. Thanks a lot.

    • Thank you for your kind words!
      Stay tuned, will launch soon a new service that will make form filling much more powerful, with features that cannot be done with current solutions.
      You can take a peak here: https://exceltoforms.com
      If you are willing to be a part of the testing team, just let me know.
      Regards,
      Catalin

  19. Tobe says:

    Yes I can do some testing. Thanks

  20. Brad Kirwan says:

    Hi Catalin,

    Great tool, I am just starting to work with it and have 2 questions:

    1. Is there any way to concatenate 2 fields in excel to 1 field in the PDF?

    2. It does not seem to be able to locate the files if stored on Onedrive, I use Office365, is there a workaround or is this an issue with VBA?

    I am not a programmer, but have some knowledge.

    • 1. Yes, best way is to add a new column in your data table, with those 2 columns concatenated, then use this new column for mapping, not the original columns.
      2. On OneDrive, ThisWorkbook.Path might return the “https://d.docs.live.net/” path instead of local path, so you have to replace the http path with the local onedrive path.
      You can find solutions on web. Basically, Environ(“OneDrive”) should provide the onedrive local path, so you can use this to replace the http path.
      Note that for OneDrive business, (office 365), there are more than 1 registry keys:
      Environ(“OneDriveCommercial”)
      For OneDrive Personal, use: Environ(“OneDriveConsumer”) if you have also a OneDrive business account.
      All onedrive keys can be found in registry: Computer\HKEY_CURRENT_USER\Environment

  21. Michael Hopkins says:

    Hi Catalin,

    I wanted to say thank you for making this code freely available to the public. I am playing around with this and have gotten the basic premise. I just have this thorny issue: I have a .xlsx file in a SharePoint library that I linked to the Fill PDF workbook. I now want to take the last line that is created in the linked sheet and update the “data” sheet. Also, I want to have the same attributes from the original three rows (2,3,& 4) for any new row added (as I type this I have a feeling I will have to adjust the code under ‘write fields to table comment to apply the same settings to any new additional row created in the “data” sheet).

    Once again, thank you for all that you have done.

    • It’s not clear what excel version you are using.
      If it’s an excel for windows, newer versions, You should be able to connect to the sharepoint file from the Fill PDF workbook using Power Query. This query should replace the data table, at each query refresh will get all data from that file, no need for additional codes to maintain data sync.

  22. Michael says:

    Hi Catalin,

    I want to first thank you for making this freely available to the public. This has really helped me start on a project for my HR department.

    I just have one issue that I am sure you will solve in a jiffy:

    How/What do I modify in the VB code to treat any new rows that are added in the โ€œDataโ€ sheet to have the same properties as the sampleโ€™s 2nd-3rd rows? Ex. I want to add a new employee โ€œJane Doeโ€, add all of her information in Row 5, double-click on her name and have that applied to the PDF.)

    As of right now, when I add information to a row below Row 4, nothing happens when I double-click the first cell in the row (I just have the normal function of editing the text in the cell when I double-click).

    Once again, thank you for all that you do.

    • Hi Michael,
      Not sure what you mean by “new rows to have the same properties”
      The data is organized into a defined table, whenever you add a new entry under the table, it should automatically autoexpand to include the new row. If it doesn’t, check if the autoexpand functionality is disabled. The setting is not very obvious:
      File->Options->Proofing->Autocorrect Options, make sure that in Autoformat as you Type you have the checkboxes checked.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

The Excel Learning Journey that I propose for you, is structured in such a way as to offer you the most practical way to learn Excel, free.

Start Your Free Excel Learning Journey!

Enter your email address to subscribe to this blog: