Cannot change link to an excel source file in my word doc

[Flahoosier]

I am trying to use sourcefullname to change the path and
name of an excel file that is linked throughout my word
doc. Everything I can find tells me that sourcefullname is
a read/write string, but I cannot get the change to take.
It goes through the motions, but when I check the source
files in the Links window, it is still referencing the old
file. I have written my own macro using sourcefilename and
used a version from the ms web site (modified for
word/excel vs. powerpoint/excel), neither one works. In
fact, both macros often crash Word about 3/4 of the way
through the process. (There are 30+ links that have to be
changed.) If I step through the macro it doesn't crash.
Any suggestions?

 

[Doug Robbins - Word MVP]

Hi Flahoosier,

This may not be exactly what you want, but should show you how to go about
it:

' 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

Please respond to the newsgroups for the benefit of others who may be
interested.

Hope this helps
Doug Robbins - Word MVP

 

[Flahoosier]

Thanks Doug, that helped. More than half of my links are
converted with that routine, modified. Unfortunately there
are some links that did not change. Those links are to
whole sections of the spreadsheet. As far as I can tell
they are considered shapes, and I've written code based on
what I found in the help and in your code, but it doesn't
work. I find the link correctly but the value never
changes. Here's my code:

FirstTime = True
For Counter = 1 To ActiveDocument.Shapes.Count
With ActiveDocument.Shapes(Counter)
If .Type = msoLinkedOLEObject Then
If FirstTime Then
Message = "Enter the modified path and
filename following this"
Format " & linkfile"
Title = "Update Link"
Default = .SourceFullName
Newfile = InputBox(Message, Title, Default)
FirstTime = False
End If
With .LinkFormat
.SourceFullName = Newfile
End With
End If
End With
Next Counter


I'm ready to pull my hair out on this. Everything I've
read seems to indicate this should work. Am I missing
something really obvious? I rather hope its something
obscure... Thanks for any help you can provide!