Macro to change linked object source file

[andrewhharmon]

I have an word document with 84 inline linked objects from an excel
file. I need to be able to run a macro that will change the source file
of all the linked objects. My solution was as follows but has two
issues.

Sub test()

Dim i As Integer
With ThisDocument
For i = 0 To .InlineShapes.Count
If .InlineShapes(i).Type = 2 Then
.InlineShapes(i).LinkFormat.SourceFullName = "C:\test.xls"
End If
Next i
End With

End Sub

Issues:

1. If the object i linked to is an excel chart, it incorporates the
original excel file name in the item section of the linked object. (if
you go to edit...links... its the item column). Changing the source
name does not change the item section, so charts give an error.

2. Four of my linked items are in a header. I have no idea how to
access those objects. when i do a ?thisdocument.inlineshapes.count, in
the immediate window, it returns 80.... but i have 84 linked objects.

I also tried the above code using fields instead of inlineshapes.. no
luck. I'm using Word 2002 and excel 2003

Any help would be gretaly appreciated. Thanks

 

[macropod]

Hi Andrew,

Check out the code in the document at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=261488
It may do what you want.

Cheers

 

[andrewhharmon]

Hey,

Thanks for the help but that didn't seem to do it for me. That allowed
me to specify a folder which the links would be reset too. I need to
specify a new excel file name, more than likely in a folder with lots
of similar excel files. Anyway, it left all my links as invalid links.
Thanks anyway though.

-Andrew

Hi Andrew,

Check out the code in the document at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=261488
It may do what you want.

Cheers

--
macropod
[MVP - Microsoft Word]

I have an word document with 84 inline linked objects from an excel
file. I need to be able to run a macro that will change the source file
of all the linked objects. My solution was as follows but has two
issues.

Sub test()

Dim i As Integer
With ThisDocument
For i = 0 To .InlineShapes.Count
If .InlineShapes(i).Type = 2 Then
.InlineShapes(i).LinkFormat.SourceFullName = "C:\test.xls"
End If
Next i
End With

End Sub

Issues:

1. If the object i linked to is an excel chart, it incorporates the
original excel file name in the item section of the linked object. (if
you go to edit...links... its the item column). Changing the source
name does not change the item section, so charts give an error.

2. Four of my linked items are in a header. I have no idea how to
access those objects. when i do a ?thisdocument.inlineshapes.count, in
the immediate window, it returns 80.... but i have 84 linked objects.

I also tried the above code using fields instead of inlineshapes.. no
luck. I'm using Word 2002 and excel 2003

Any help would be gretaly appreciated. Thanks

 

[macropod]

Hi Andrew,

If all you need to do is to change *all* the filenames to the same new
filename, then a quick & dirty change to the code I referred you to should
do it.

The code has a Function named 'ParseOldField()', with the line
'SourceFileName = OldField'. Change that line to 'SourceFileName =
"test.xls"', using whatever the real filename is, minus the path.

Cheers

--
macropod
[MVP - Microsoft Word]

Hey,

Thanks for the help but that didn't seem to do it for me. That allowed
me to specify a folder which the links would be reset too. I need to
specify a new excel file name, more than likely in a folder with lots
of similar excel files. Anyway, it left all my links as invalid links.
Thanks anyway though.

-Andrew

Hi Andrew,

Check out the code in the document at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=261488
It may do what you want.

Cheers

--
macropod
[MVP - Microsoft Word]

I have an word document with 84 inline linked objects from an excel
file. I need to be able to run a macro that will change the source file
of all the linked objects. My solution was as follows but has two
issues.

Sub test()

Dim i As Integer
With ThisDocument
For i = 0 To .InlineShapes.Count
If .InlineShapes(i).Type = 2 Then
.InlineShapes(i).LinkFormat.SourceFullName = "C:\test.xls"
End If
Next i
End With

End Sub

Issues:

1. If the object i linked to is an excel chart, it incorporates the
original excel file name in the item section of the linked object. (if
you go to edit...links... its the item column). Changing the source
name does not change the item section, so charts give an error.

2. Four of my linked items are in a header. I have no idea how to
access those objects. when i do a ?thisdocument.inlineshapes.count, in
the immediate window, it returns 80.... but i have 84 linked objects.

I also tried the above code using fields instead of inlineshapes.. no
luck. I'm using Word 2002 and excel 2003

Any help would be gretaly appreciated. Thanks

 

[Doug Robbins - Word MVP]

Try the following:

' 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

 

[andrewhharmon]

Thanks for the help guys, but neither one of thse solutions address the
two issues I had in my original post. These solutions make any charts
that were linked invalid because it deosn't change the item property
and they don't affect any linked objects in the header. Thanks

-Andrew