Is it possible to change the Item of a link in MS Word referencing to an Excel spreadsheet, pragmatically, through the use of VBA?
I have found discussions on how about to change the file source, but not the Item, shown below in the edit link menu from MS Word 2010:
The exact same question has been already asked here, but without any answers, about 2 years ago. I have also looked into the LinkFormat property documentation, which is used to edit the Source of a link, but led me nowhere.
Any ideas on how to solve the issue are appreciated.
Background: I have a Excel spreadsheet feeding a word document with data. I am trying to use this document as a template, where depending on the analysis I would change the spreadsheet feeding the data to the word document. The tables in the spreadsheet (not formated as such) can vary in range. However, they have the same number of columns. Since the number of rows vary, I would have to change the Item field accordingly.
Edit
I have provided 2 sample files as a MWE of the issue. They can be found here.
Best Answer
I found a work arround to this. Instead of changing the Itens, I created a VBA script to name all the ranges linked. So instead of changing the link item in Word, I change the named range value in excel. The link item remains the same, but it points to a range that can be pragmatically modified. Here's the code I put together to put names to ranges:
The downside of this approach is that I had to recreate (link) manually all the connections I did previously. To change the file Source I used the code in the question link. For completion's sake I am writing it here:
Finally, to update all the links at once: