Moving Excel->Word Links

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pdberger - 07 Aug 2007 22:36 GMT

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.

Reply to this Message

Doug Robbins - Word MVP - 08 Aug 2007 00:22 GMT

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

Signature

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

> Good afternoon.
> I have a Word document that consists of almost nothing but about 300 links
[quoted text clipped - 16 lines]
>
> Thanks in advance.

Reply to this Message

pdberger - 08 Aug 2007 05:28 GMT

Senor Robbins --

Many thanks. I appreciate the help.

Peter

> The following macro can be used to update the links in the document to the
> new location of the spreadsheet:
[quoted text clipped - 52 lines]
> >
> > Thanks in advance.

Reply to this Message