Attempting to Automatically Transfer Specific Data from Excel to Word Files

[jharbold]

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:[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A1[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A2[/TD]
[TD]B2[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A2[/TD]
[TD]B2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A1[/TD]
[TD]B1[/TD]
[/TR]
</tbody>[/TABLE]

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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A1[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A2[/TD]
[TD]B2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Macropod]

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.

 

[jharbold]

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.

 

[Macropod]

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.