Pulling data from cells in Excel spreadsheet to specific cells in a Word document table

TonyD1016

Board Regular
Joined
Nov 18, 2021
Messages
59
Office Version
  1. 365
Platform
  1. Windows
Currently I am working with a database that contains employee data such as their first and last name, employee number, etc.

What I need to do is pull that data from the spreadsheet and insert it into specific cells in a prepared table in a form letter Word document. For instance, one cell in the Word document would contain their name and employee number, another cell would have their job title, data from a "Notes" cell would go further down the document, contact information would go into another cell, and so on.

What is the best way to accomplish this? I've done some scripting in Excel before (though never with Word) but I'm not overly familiar with the syntax and I've never tried to automate a transfer like this before.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
My recommendation would be the "Mail Merge" feature of word.
You would be able to save the excel document, then use it as a reference for dummy text box fields.

Rough Instructions would be.
1.In your word document --> Mailings--> Select Recipients--> Use Existing list
[dialog box] select excel document containing your employee info.
This creates the reference to the data source.

2. Using Mailings--> Insert Merge Field , insert the fields where you want them on the word document.

3. Using the Mailings--> Preview Results, navigate [arrow right and left] thru your records for desired results.
 
Upvote 0
Solution
My recommendation would be the "Mail Merge" feature of word.
You would be able to save the excel document, then use it as a reference for dummy text box fields.

Rough Instructions would be.
1.In your word document --> Mailings--> Select Recipients--> Use Existing list
[dialog box] select excel document containing your employee info.
This creates the reference to the data source.

2. Using Mailings--> Insert Merge Field , insert the fields where you want them on the word document.

3. Using the Mailings--> Preview Results, navigate [arrow right and left] thru your records for desired results.

Thank you very much, I think this will suit my needs. :)

I had some strange results when I picked the excel document from the list. I have several names defined in that document that I reference in formulas and it listed all of them as different tables. Is that normal?

Also, I like the Go to Record feature a lot but is there any way to get it to count the header row in the list so that I can type in the row number from Excel document and match it to that record in the Word document? As it stands if I type in 2 which is the first entry in Excel it points to the second entry in the sheet (which is row 3 in Excel).
 
Upvote 0
In order to keep it un-complicated. Your best option is a excel document with only 1 worksheet , with no formulas. Headers in Row#1. It will automatically pick them up as headers and your row #2 record will be #1 entry. I personally just create a copy and sanitize the document before I make the reference to word. This may not be an option for you depending on how robust your data is. So, you may need additional steps to achieve your desired results.

This becomes more of a Microsoft Word related topic.

I hope I was able to help you a bit.
 
Upvote 0
In order to keep it un-complicated. Your best option is a excel document with only 1 worksheet , with no formulas. Headers in Row#1. It will automatically pick them up as headers and your row #2 record will be #1 entry. I personally just create a copy and sanitize the document before I make the reference to word. This may not be an option for you depending on how robust your data is. So, you may need additional steps to achieve your desired results.

This becomes more of a Microsoft Word related topic.

I hope I was able to help you a bit.
I think I got it.
Thank you so much for your help!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top