Fill Word Template With Data From Excel

excel fill word template

We have built a VBA tool which feeds predefined Word template equipped with bookmarks with data from Excel table.

17.12.2019, Goran Dolenc
Categories: Excel VBA Word
Tags: Bookmarks Excel Merge PDF Template VBA Word

Client from US has contacted us with the following job requirement:

We have a template pdf agreement which we are sending to different clients and need to edit certain data in it each time, as first & last name, date, brand name, etc. We are looking for someone to create a simple excel sheet, where we would just input that data in there, click on a button, which would than populate that data in a saved PDF agreement.

This is how their PDF template looked like:

We advised to the client to rather use Word template and not PDF, as much more flexibility can be achieved this way. If using PDF forms, you must define all data fields properly and fields are usually fixed in size. While with using Word templates, you can create bookmarks, which only mark the place in Word document, where the data from Excel will be placed. This way, field sizes can be variable length. All you need to do is to properly name bookmarks, so you know exactly which data field from Excel should be put where in Word document. At the end, Word document is saved as PDF file.

This is how we have build our Word template:

And this is how we have setup the Excel data tab:

We have used same bookmark names as Excel table header row. This way it is easier to track everything.

And here is our final output - the PDF file, where data from Excel is filled in Word template and this one is saved as PDF at the end: