Attempting to Automatically Transfer Specific Data from Excel to Word Files

jharbold

New Member
Joined
Feb 17, 2018
Messages
12
I currently have an excel document that is acting as my database with data arranged in rows. From time to time I need to export particular data to word documents that act as my templates. My current solution for this was to link each cell of my table in word to the appropriate cell in my excel document. However, I have discovered that this link is an absolute link and does not move with my excel file. For example, I have the following table:
AB
1A1B1
2A2B2

<tbody>
</tbody>

Sometimes I may sort my excel data in different ways and the rows that my data appears in may change, such as:

AB
1A2B2
2A1B1

<tbody>
</tbody>

My intent is that I will link my word document such that I can always retrieve the value of the cell next to "A1" in my Word document. However, my word document is linked such that it only cares about the absolute cell reference. The way that Word handles this is that in the first table, if I linked to B1, I would receive "B1". After the table is sorted though, I receive "B2" since that is the entry in cell B1.

The Word templates (and there are many different template files that all feed off of the same excel spreadsheet) are composed of several individual tables, so I don't believe linking an entire table will work either. Would a macro be able to solve this for me? Could the macro be generated from Word to then call on the other excel file and the correct cells (the value next to "A1"). Any help on this issue would be much appreciated.
AB
1A1B1
2A2B2

<tbody>
</tbody>
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
If you name the range(s) before setting up the link, Word will link to the named range(s), regardless of where you move them to. If the named range includes multiple cells and you expand/contract that range by inserting/deleting cells, or by applying the name to an entirely different range, the edits will likewise be reflected in Word.
 
Last edited:
Upvote 0
I did a simple test where I defined names of cells and then sorted the rows that those were in. When I sorted, the values within the cells rearranged themselves so that the data did not stick with the row that I wanted it to. Perhaps I am not doing what you suggested correctly.

I had a thought last night that I think will work. I am going to add a new column "A" that is simply numbered 1 through XXX. That way, as long as I always sort by column A before I update my link, the correct data in each row will be associated with the intended cell in Excel.
 
Upvote 0
I did a simple test where I defined names of cells and then sorted the rows that those were in. When I sorted, the values within the cells rearranged themselves so that the data did not stick with the row that I wanted it to.
You may need to ensure the name still applies to the correct range.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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