Multiple Links from Word 2003 table to Excel 2003

[Cyberjeff]

Stating the version of my office package for the previous post:
I have a Word template with dozens of links to an Excel spreadsheet.
Currently when I change the source data in Excel, I have to got to the Word
table, I click Edit, Link and then Change Source for every entry in the Word
Table.
Is there a way to change the source file once and have all the items in the
Excel source file be automatically updated to the links in my Word table?
The format of the Excel sheet does not change, the links always point to the
same place, it is only the name of the spreadsheet which changes, and the
content obviously.

 

[CraigV]

I have had a maro written to "redirect" the links to the new Excel file.
Here it is:

Sub RedirectLinks()
'
' RedirectLinks Macro
' Macro created 1/31/2007 by Craig
'

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
End Sub


When you run it, it prompts you for the new full path and file name to link
to. You will notice the prompt is seeded with the current full path and file
name with some oddities: the backslash directory delimiter is always input
twice ("//" instead of "/"). I dont know why that is but you have to always
use the double backslash.

I hope this helps,
Craig

 

[Doug Robbins - Word MVP]

' 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