Linked Excel Table in Word from absolute ref to range ref

Question

I need to switch a linked spreadsheet embed from using reference to using a range to specify the region.

I have a spreadsheet table that will change size and content (nothing fast, maybe weekly changes) I would like to make sure that when the table is updated in size (new rows/removed rows), the word documents embed also is changed in size and content.

I currently have a link set up by copying the current table to the clip board, then special pasting ("Paste link", Excel) I get the following as the embedded code when I do alt+F9,

{ LINK Excel.Sheet.12 C:\PowerQueryLink.xlsx Desktop!R1C1:R7C3 \a \p }

The sheet name is "Desktop". The Table name is "Desktop_Query".

I have tried editing it to be 

{ LINK Excel.Sheet.12 C:\PowerQueryLink.xlsx Desktop!Desktop_Query \a \p }

But get the error "Word is unable to create a link to the object you specified. Please insert the object directly into your file without creating a link." 

I have tried the object path (insert -> text -> Object) but that does not let me select the table or sheet I want it to pull from. I have tried special pasting using "Paste" not "Paste link", but that does not allow the data to be updated from the original sheet.

I need the area to expand and contract when the spreadsheet table changes size.

Thanks,

Answer 1

Hi Darrick,

In the Excel workbook, you may navigate to the Formulas menu, click Define Name, input a new name, and select the range of the table behind the Refers to option, OK. After that, you may use the defined name in the embedded code in the Word document and check the results.

Per my tests, you may need to manually Update Link for the first time after updating the code, later changes made in the Excel workbook will be automatically updated to the document.

Best Regards,

Hugo

Answer 2

Based on what you have described, your link should be expressed as:

{LINK Excel.Sheet.12 "C:\PowerQueryLink.xlsx" "Desktop!Desktop_Query" \a \p}

Note: If you name the range before copying & pasting the entire named range, Word will automatically use the range name instead of the cell ranges.

Answer 3

Thank you so much! I did not know that a table was different from a range.