I have a pivot table in Excel 2007 which I’ve pasted it into Word 2007 using Paste Special (Paste link) -> Microsoft Office Excel Worksheet Object.
The pivot table appears in Word and the link to Excel is working. The problem is that if the pivot table expands (for example, due to showing 12 months of data rather than six months) the link to the pivot table in Word will only show the range cells that were originally copied over with the pivot table.
I understand why this happens. When I paste as a link to Word the underling field codes look like this:
{LINK Excel.Sheet.8 "C:\Users\myAccount\Documents\testexcel.xlsx"
"Sheet2!R1C1:R8C2" \a \p}
The codes refer to a fixed area (e.g., Sheet2!R1C1:R8C2 ) of the Excel spreadsheet, and so when the pivot table expands, the expanded cells fall outside the area that is defined in the field codes.
Is there some way to have the link refer to the pivot table itself rather than the cell range that happened to be originally copied over from Excel?
Best Answer
Name the range,
Sheet2!R1C1:R8C2, as an Excel table (Insert->Tables-Table). Let's call this tbSheet2Create a name that points to the table(Formulas->Defined Names->define Name). Let call it sh2Table and point it to tbSheet2[#All]
Change the file code to the table name. With the example below, the change is:
{LINK Excel.Sheet.8 "C:\Users\myAccount\Documents\testexcel.xlsx" "Sheet2!**sh2Table**" \a \p}