Word/Excel & relative linking?

[Guest]

Hello everyone,

We have a Word doc linked to an Excel spreadsheet. This allows the
spreadsheet to perform calculations, which will then be displayed in the
Word doc (and subsequently sent to the client).

Our document templates use absolute paths. This means the users will need
to relink the Office document pairs each time they make copies. Since there
are hundreds of these pairs on the network, users could easily link the
wrong documents.

I need to change the link properties in Word to be relative; the goal is to
have the DOC look for a XLS with the same name, both residing in the same
folder.

We Currently have:
C:\My Documents\MyDoc.doc links to C:\My Documents\MyDoc.xls

We Want:
<current location>\MyDoc.Doc links to <current location>\MyDoc.xls

Does anyone have suggestions? I'm comfortable writing VBA/macros, but am
unsure of how to do this specifically, or where to look for tips. Thanks
for the help in advance

 

[Doug Robbins]

You can get the path of the document by stripping the filename from the
..FullName function and then use that in a modified version of the following
macro:

' 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

 

[Guest]

Hi Doug,

Thanks for the response - it's exactly what i was looking for

JPR