Change source for all links from Word 07

[Zacharias]

Fair warning - this may be a bit of a Word question.

I have many links from a Worksheet pasted as linked objects into word. I can change the source of these objects one at a time by right clicking one, selecting "Linked Worksheet Objects" > "Links" > "Change Source" and then browsing for the new source. I do not change the item for any objects (i.e. the links all refer to the same cell in the same sheet, in a different workbook).

The problem is I have to do this for every individual link, and there are hundreds.
Is there a way to change the source file for all of my linked objects at once?

 

[sijpie]

This might help:
Update linked fields in Word document from Excel VBA - Stack Overflow
Faster way to update external links in a large document
http://www.mrexcel.com/forum/excel-...lications-resets-update-method-automatic.html

 

[Zacharias]

The code in the third link was EXACTLY what I was looking for.
Thank you so much!!

 

[jorgon]

There are a couple of other ways to do this.

I had to look into this because I wanted to have a word file containing numerous links to the cells in an Excel data source file. The data in the Excel data source file would be different for each client. The idea was that each client would have a folder containing the two files, so that on opening the word file it would be filled by client's data from the Excel data file in the same folder and not data for some other client.

The trouble is that the links in the word document are absolute (that is, containing the full path). This means that the data filling the word file will always be loaded from the Excel data source file in that path and not from an Excel data file in the client's folder.

I found the following solutions.

Method 1
In the word file, the links can be seen by pressing Alt-F9 making them visible.
Then you can use "replace" to change all the links to the absolute address of the Excel data source file in the client's folder.

Method 2
I found that if you click "yes" to "update the document with the data from the linked files" on opening a word file, and word cannot file the data source file in the path given in the link, then it will look for a file of the same name in the current folder (that is, the folder containing the word file). On finding that file, word will update all the links automatically to point to that file instead of the original file. In other words, word will automatically perform method 1 for you.
The trick therefore is to ensure that word cannot find the original file.
The easiest way for me to achieve this with my arrangement, was to prepare and develop the word file and the original Excel data source file in folder 1. The result was that the links in the word file pointed to folder 1. Then when I had finished preparing and developing the word file and the original Excel data source file, I moved the Excel file into a "final" folder (so it was no longer in folder 1). For a new client I created a folder and copied into it the word file and the original Excel data source file. Then on opening the word file, the links were automatically updated to point to the Excel data file in the client's folder because the original Excel data source file had been removed from folder 1.
If I needed to do more preparation and development work on the word file and original Excel data file I would move the Excel file back into folder 1, moving it into the "final" folder when finished.