Macro to change linked object source file

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
Tue Oct 31 15:13:19 CST 2006

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]


<andrewhharmon@gmail.com> wrote in message
news:1162323999.025692.178010@m73g2000cwd.googlegroups.com...
> 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
>

andrewhharmon
Tue Oct 31 15:49:38 CST 2006

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

macropod wrote:
> 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]
>
>
> <andrewhharmon@gmail.com> wrote in message
> news:1162323999.025692.178010@m73g2000cwd.googlegroups.com...
> > 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
Tue Oct 31 22:18:42 CST 2006

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]


<andrewhharmon@gmail.com> wrote in message
news:1162331378.400619.307790@f16g2000cwb.googlegroups.com...
> 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
>
> macropod wrote:
> > 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]
> >
> >
> > <andrewhharmon@gmail.com> wrote in message
> > news:1162323999.025692.178010@m73g2000cwd.googlegroups.com...
> > > 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
Tue Oct 31 23:40:37 CST 2006

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@gmail.com> wrote in message
news:1162323999.025692.178010@m73g2000cwd.googlegroups.com...
>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
>

andrewhharmon
Wed Nov 01 08:37:15 CST 2006

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

Doug Robbins - Word MVP wrote:
> 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@gmail.com> wrote in message
> news:1162323999.025692.178010@m73g2000cwd.googlegroups.com...
> >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
> >