update links in Word from Excel

[Valeria]

Dear experts,
I have many, many Word documents where some fields are linked to cells on an
Excel worksheet.
Unfortunately I need to change the path of the Excel worksheet and just
realized that all links will break!!!!
Is there an easy way to replace the path of the Excel worksheet in the Word
documents without having to do it manually (4/5 links per document.... 200
documents...)
Many thanks in advance for your precious help.
Kind regards,
Valeria

P.S.: i had posted this in the Excel forums but I realized I got it wrong as
it concerns more word than excel... sorry for the double posting.

 

[Jay Freedman]

Dear experts,
I have many, many Word documents where some fields are linked to
cells on an Excel worksheet.
Unfortunately I need to change the path of the Excel worksheet and
just realized that all links will break!!!!
Is there an easy way to replace the path of the Excel worksheet in
the Word documents without having to do it manually (4/5 links per
document.... 200 documents...)
Many thanks in advance for your precious help.
Kind regards,
Valeria

P.S.: i had posted this in the Excel forums but I realized I got it
wrong as it concerns more word than excel... sorry for the double
posting.

You can use the macro in http://www.word.mvps.org/FAQs/MacrosVBA/BatchFR.htm
with slight modifications.

First, be sure to change the line

PathToUse = "C:\Test\"

to point to the folder containing your documents. (If they're in many
different folders, you can run the macro separately on each folder, and you
could reprogram the macro to ask for the folder name each time.)

Next, just after the line

Set myDoc = Documents.Open(PathToUse & myFile)

add the line

ActiveWindow.View.ShowFieldCodes = True

to display field codes instead of field results. Also add

myDoc.Fields.Update
ActiveWindow.View.ShowFieldCodes = False

before the myDoc.Close line.

When you run the macro, the Replace dialog will open. In the Find What box,
enter the old path of the Excel worksheet, using two backslashes instead of
one between folder names, like

C:\\Documents and Settings\\myname\\My Documents\\sheet1.xls

In the Replace With box, enter the new path, also with two backslashes per
folder.

It would be a good idea to copy a few of the Word documents to a temp folder
and try out the macro there, to make sure it works correctly before
committing mayhem on a couple hundred documents.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Valeria]

Hi Jay,
this really made my day, I actually used the second version of the macro on
the link you sent - the one which opens subfolders, too, as of course my 200
+ documents were all in different folders!
Again thanks a lot!
Kind regards