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?
