Moving Excel->Word Links

[pdberger]

Good afternoon.
I have a Word document that consists of almost nothing but about 300 links
from an Excel worksheet. I use the Excel file to store information, and
translate short answers into longer textual comments (sometimes a couple of
sentences). I use the Word document to organize and format those longer
comments into a document that's ready for a final review and release.

Currently, these two files sit in the same directory on my office PC. I'd
like to move them to a central server, or another user's PC, and have the new
copy of the Word document look to the NEW copy of the Excel source document,
not the one on my office PC.

How can I do that? Does it have to do with having them both open and active
as I save them where they need to be?

Thanks in advance.

 

[Doug Robbins - Word MVP]

The following macro can be used to update the links in the document to the
new location of the spreadsheet:

' Macro created 26/10/01 by Doug Robbins to update links in a document
'
Dim alink As Field, linktype As Range, linkfile As Range
Dim linklocation As Range, i As Integer, j As Integer, linkcode As Range
Dim Message, Title, Default, Newfile
Dim counter As Integer


counter = 0
For Each alink In ActiveDocument.Fields
If alink.Type = wdFieldLink Then

Set linkcode = alink.Code
i = InStr(linkcode, Chr(34))
Set linktype = alink.Code
linktype.End = linktype.Start + i
j = InStr(Mid(linkcode, i + 1), Chr(34))
Set linklocation = alink.Code
linklocation.Start = linklocation.Start + i + j - 1
If counter = 0 Then
Set linkfile = alink.Code
linkfile.End = linkfile.Start + i + j - 1
linkfile.Start = linkfile.Start + i
Message = "Enter the modified path and filename following this
Format " & linkfile
Title = "Update Link"
Default = linkfile
Newfile = InputBox(Message, Title, Default)
End If
linkcode.Text = linktype & Newfile & linklocation
counter = counter + 1
End If
Next alink


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[pdberger]

Senor Robbins --

Many thanks. I appreciate the help.

Peter