Link to Excel from Word: This object is corrupt or no longer available

Question

I have a Word 2010 document, with linked objects to pivot tables in a Excel 2012 document. Every night, a macro runs to update the underlying data for the pivot tables in Excel, and refresh these tables.

Next, a macro in Word update all the Excel links in Word, to pick up the changes in the pivot tables.

At some point, the links in Word because corrupt. When right-click on the table in Word, there is no "Update Link" option. It is replaced by a menu item called "object", and when I click on that, error pop up is "This object is corrupt or no longer available".

I tried refreshing the links by going to File -> "Edit Links to Files, then "Change Source..." and re-select the Excel file again. After this refresh, I can do "Update Link". But If i save and close the file, the link becomes corrupt again.

Not sure if this is somehow related, but when I first manually update the link in word, the link type is "Worksheet". When I save & reopen, the link type becomes "Excel.Sheet.12".

Any ideas on how to stop the links from becoming corrupt on closing/re-opening the file?

Solution

Instead of right click -> update field, select your objects and press F9. Or, if you want to update the entire document, CTRL+A and then F9.

Comments

[mike]

I'm having the same issue -- any luck on fixing this? When I close/reopen all links break and I get the "Error! Not a valid Link" when I try and update.

[Lost In Code]

Never figured it out. Rolled back to an older word doc where there was no corruption and it magically started working again.