How to auto-populate Word Document from Excel

Question

Hi,

I have an Excel spreadsheet with client number, name,e-mail, so forth. I would like to auto-populate a Word document with the client's name, e-mail and other information based on the client number that is typed in the Word document.

What I would like to see happen is after opening the Word document template, I would type in the client number. Word would then fill in the remaining areas (name, e-mail, phone) from the Excel spreadsheet based on the client number that was entered.

How would I go about doing this? Is there a way to make Mail Merge auto fill based on an entered value?

Thanks!

Answer 1

Hello 1234Pete,

Thanks for contacting us, Yes, you can use Mail Merge in Microsoft Word to auto-populate a Word document from an Excel spreadsheet. Here's how:

In Excel, make sure your data is organized into columns with headers, such as "Client Number", "Name", "Email", and "Phone".

Save your Excel spreadsheet and close it.

Open a new Word document and create a template with the placeholders for the data you want to insert, such as <<ClientName>>, <<ClientEmail>>, and <<ClientPhone>>.

Go to the "Mailings" tab in Word and select "Start Mail Merge" and then "Step-by-Step Mail Merge Wizard".

In the Mail Merge Wizard, choose "Letters" as the document type.

In the "Select Starting Document" section, choose "Use the current document".

In the "Select Recipients" section, choose "Use an existing list" and browse to find your Excel spreadsheet.

Select the sheet with your data and choose "OK".

In the "Write Your Letter" section, place your cursor where you want to insert the first placeholder.

Choose "Insert Merge Field" from the Mail Merge menu and select the corresponding column header from your Excel spreadsheet, such as "Name".

Repeat step 10 for all of the placeholders in your template.

Preview your letters to ensure that the placeholders are being populated with the correct data.

Complete the Mail Merge by choosing "Finish & Merge" and selecting "Edit Individual Documents". This will create a separate Word document for each row of data in your Excel spreadsheet.

In each individual document, you can type in the client number and then choose "Update Labels" from the Mail Merge menu to update the placeholders with the correct data for that client.

I hope this helps! Let me know if you have any further questions

Warm Regards, Akande.

Answer 2

Hi,

Thank you for your response. I was able to get everything working up until the "Update Labels". From the template and "Finish & Merge" it made a new document with the filled in fields. When I overwrote the client number, the "Update Labels" stayed greyed out. Changing any of the fields still kept it greyed out. What do I need to do so that the document can be updated?

Thank you!

Peter

Answer 3

Mail Merge is the "poor man's" method and the best way is to make use of a UserForm.

See the following pages of Greg Maxey's website

http://gregmaxey.mvps.org/Create\_and\_employ\_a\_UserForm.htm

http://gregmaxey.mvps.org/Populate\_UserForm\_ListBox.htm

Answer 4

Hi,

Thank you for your response. I was able to get everything working up until the "Update Labels". From the template and "Finish & Merge" it made a new document with the filled in fields. When I overwrote the client number, the "Update Labels" stayed greyed out. Changing any of the fields still kept it greyed out. What do I need to do so that the document can be updated?

Thank you!

Peter

Doug has more expertise than just about anyone answering questions here. There is certainly no one who has more expertise.

I agree that UserForms are both powerful and flexible and would meet your needs. The links he gave you lay everything out.

That said, I've been using the Mail Merge method for more than 30 years to successfully prepare draft documents for clients.

If you do Finish and Merge you have a plain document that no longer has any reference to the data. What was filled in during the merge.

My primary merge document is a template, not a document. From that template, a new document is created. That new document is also a primary merge document. I attach the data source and identify the record. There is a macro in the template to do this.

Then I preview for that record. I make changes to the document adding and deleting information.

I run a macro that locks all of the merge fields and changes that document from being a merge document and save it.

If I need to make changes that involve the data source, I re-run the first macro that (1) unlocks the merge fields, re-attaches the data source, and again has me identify the appropriate record. I am back to the preview part at this point.

I would be happy to share my macros.

I developed this method prior to using UserForms much. It stretches what Mail Merge is intended to do.

Both methods involve vba programming.

Doug may be correct in characterizing this as the "poor man's method."

I am happy with my method. I've trained multiple secretaries and assistants in my office to use it.