Linking Data from Excel to Word

**JPCrow** · 12-27-2010, 04:07 PM

Hello!

I have a Word document that I fill out over and over again every day. I started this job last week, and I know there must be an easier way.

What I want to do is auto-fill all of the information I type in over and over in the same document.

For example:
My document has the same Name, Date and Customer Address repeated multiple times throughout the document. The Name is repeated about 10 times throughout the document. Date and Address are about 5 each.

It also has about 3 other pieces of info repeated, one of which is typically 3 paragraphs long and repeated 3 times throughout the document.

I found the following from a google search:
http://vbaexpress.com/kb/getarticle.php?kb_id=381

This is exactly what I want, but with one BIG problem. Bookmarks must be unique. So, this auto fills the first instance of each piece of data, but, because each bookmark can only occur once in a single word document, it doesn't fill in the other 9 places where the name needs to be inserted.

What I would like to do is use this same script but modify it in some way so as to populate multiple places throughout the document, and not simply one.

Thanks in advance,

Josh

**Leith Ross** · 12-27-2010, 04:30 PM

Hello JPCrow,

It would be easier to answer your question if I could see both the Excel file and the Word file (as you want it). Can you post copies of these files?

**JPCrow** · 12-27-2010, 06:13 PM

I'm afraid I can't post those files on a public forum, but, if you follow the link in my OP you can find a zip file with an example excel and word file that work with the vb script provided.

The only problem with that script is it relies upon bookmarks in word which are unique values which can only exist at one point in any given word document.

What that script does is take the values in cells in the excel workbook and propagates a word document with those values, ie there is a field in the excel sheet that holds the value of the name of the person whose name appears in my word document. By placing a bookmark in the word template, and running the macro, the content of the name field in excel appears in the word document at the point in the word document where the bookmark was placed.

So I can open excel and fill in as many input fields as I want, for instance NAME, ADDRESS, INFO and TOTAL, and when I run the macro, the script will open the template, generating a new word document, and automatically fill all of those values.

My issue is that the name might appear on the word document more than once, and I can't tell the script to insert the value of the NAME cell in excel in more than one place in my word document.

My word document isn't a simple letter to a client, it is a pages long legal document with multiple places where the name of the person appears as well as their address and other repeated info.

I want to fill out an excel spreadsheet one time and generate a new word document that has everything filled in and then save it as I please.

Hope that helps describe the two files I want, one a word template and one an excel spreadsheet. The spreadsheet values are used to populate the word document when I run the macro.

Thanks again.

**JPCrow** · 12-27-2010, 06:31 PM

Here is an example of what a similar word document might look like, perhaps this will help:

All know by there presents that [NAME], whose address is [ADDRESS], did appear before the honorable judge blah blah on [DATE], and that [NAME], whose address is [ADDRESS] blah blah, who has appeared on [DATE] did attest to the following:

[ATTESTATION]

Sworn this [DATE] that [NAME] whose address is [ADDRESS] did attest [ATTESTATION] and signed

------------------------------------------
[NAME]
[ADDRESS]
[DATE]

and it goes on for 6 pages like that with repetition of the name address date and attestation etc.

The excel file might look like this

Name: ____________
Date: _____________
Address: ___________________________________
Attestation: ________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

What I want to do is just have a simple excel file with 5 or 6 fields (Name, Date, Address, Attestation in the example) and a corresponding Word Template that resembles the above example. And when I run the macro, it searches the template, looking for something like [NAME] and replacing that with the name I have typed in the excel field. Then I could save the new word document as Mickey Mouse Affidavit.doc, print it out, change the excel file, by filling in new values, run the macro, thereby creating a new word document, save that as Donald Duck Affidavit.doc, print it out, rinse and repeat.

Hope that helps

Thanks again,

Josh