How can I programmatically relink my linked Excel worksheets in MS

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Oli - 31 Jul 2007 21:52 GMT

I created a Microsoft Word document that contains dozens of linked Excel
worksheets. I am searching for a method to programmatically search and
replace all these links.

In searching online, I found code to perform this task for both Access and
PPT ... but not for MS Word.

I have also found a program called "ReplaceMagic," but I need this solution
for the workplace and they'll never let me download third-party software for
this. I need some Visual Basic code.

Here are the solutions I discovered for Access and PPT, available on the
Microsoft website:

Access: "ACC2000: How to Programmatically Link or Embed an Object on a Form
(Article ID 209990)"

PowerPoint: "PPT2000: Sample Code to Change Source of Linked Excel Worksheet
(Article ID 222708)"

If anyone has a solution for Word, I'd really appreciate it!

Reply to this Message

Doug Robbins - Word MVP - 01 Aug 2007 04:36 GMT

Maybe the following macro will do what you want:

' 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

>I created a Microsoft Word document that contains dozens of linked Excel
> worksheets. I am searching for a method to programmatically search and
[quoted text clipped - 21 lines]
>
> If anyone has a solution for Word, I'd really appreciate it!

Reply to this Message

Oli - 01 Aug 2007 15:36 GMT

Thank you for the response, Doug. However, updating the "field" links in
word is not what I was looking for. I am trying to change the path name in
the "embedded objects" within the Word. I want the macro to scan through
each page in the document and find each embedded object (which are linked to
an excel file), then find the path name: "C:\drive\documents\file1.xls" and
replace it with "C:\drive\documents\file2.xls". Every object in my word file
is linked to a couple of different Excel files. I want to link these objects
to other two Excel files which contain the similar information in identical
sheets and cells. However, the file names are different. Any help you could
provide will be greatly appreciated.

In case it helps, somebody gave me the following code as a starting point.
If someone can help me to complete it, I'd really appreciate it:

Sub ChangeSource()

Dim k As Long
'Create a variable to store the object reference string.
Dim strLink As String

With ActiveDocument
' Loop through all the floating shapes in document.
For k = 1 To .Shapes.Count
With .Shapes(k)
' If the shape's type is an OLE object then...
If .Type = msoLinkedOLEObject Then
' Change the path to new source and set the update
' type to Automatic.
With .LinkFormat
' Get the source path in a string
strLink = .SourceFullName
' Do something to strLink to modify it as you wish:

'Code to modify strLink

.SourceFullName = strLink
.Update
End With
End If
End With
Next k

End With

> Maybe the following macro will do what you want:
>
[quoted text clipped - 56 lines]
> >
> > If anyone has a solution for Word, I'd really appreciate it!

Reply to this Message

Helmut Weber - 01 Aug 2007 16:09 GMT

Hi Oli,

the sample code works alright.
Note that it applies to shapes.
You may need a second loop,
for accessing inlineshapes.

For generating from the old sourcefullname
"C:\drive\documents\file1.xls"
the new sourcefullname
"C:\drive\documents\file2.xls"
you need some string processing,
if there is an underlying rule to be found.

Easy, if it would be like in your sample.

[snip]

With .LinkFormat
strLink = .SourceFullName ' e.g. "c:\test\excel\book2.xls"
' your string processing
.SourceFullName = "c:\test\excel\book2.xls"
.Update
End With

[snip]

Of course, there is no string processing at all in my example.
But there is the slot, where your string processing
must take place.

HTH

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Reply to this Message

Oli - 01 Aug 2007 17:14 GMT

Thank you, Helmut. I copied the macro hoping that all path names of
"C:\folder\book1.xls" would be replaced with "C:\folder\book2.xls" in my
embedded objects as in the following. However, it didn't work. When I used
Debug\Step Into in the VBA menu, it showed the following commands as errors:

- Sub ChangeSource()
- With ActiveDocument
- For k = 1 To .Shapes.Count
- End With (very last one)
- End Sub

I am trying to write a VBA code for the fist time in my life, and I can't
understand what is wrong with it. Any help?

Thank you!

---------------

Sub ChangeSource()

Dim k As Long
'Create a variable to store the object reference string.
Dim strLink As String

With ActiveDocument
' Loop through all the floating shapes in document.
For k = 1 To .Shapes.Count
With .Shapes(k)
' If the shape's type is an OLE object then...
If .Type = msoLinkedOLEObject Then
' Change the path to new source and set the update
' type to Automatic.
With .LinkFormat
' Get the source path in a string
strLink = .SourceFullName
.SourceFullName = "C:\folder\book2.xls"
.Update
End With
End If
End With
Next k

End With

End Sub

______________________

> Hi Oli,
>
[quoted text clipped - 28 lines]
>
> HTH

Reply to this Message

Helmut Weber - 02 Aug 2007 09:22 GMT

Hi Oli,

some newsreaders delete leading blanks in lines,
which makes the code difficult to read and to understand.

> - Sub ChangeSource()
> - With ActiveDocument
> - For k = 1 To .Shapes.Count

"next" is missing here

> - End With (very last one)
> - End Sub

The structure in principle should be like that,
whereby I use underscores to indicate leading blanks.

Sub ChangeSource()
___With ActiveDocument
______For k = 1 To .Shapes.Count
_________If .Type = msoLinkedOLEObject Then
_________end if
______Next k
___End With
End Sub

HTH

Signature

Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000 (german versions)

Reply to this Message

Oli - 02 Aug 2007 14:16 GMT

Thank you, Helmut!

Thank you all who responded me in this thread. I have two threads going on
at the same time for the same question. Therefore, I am going to abandon
this one. In the original thread, Jean-Guy has been helping me a great deal.
I'll continue to post my comments in that thread instead. If you have any
additional ideas, I would appreciate it if you could post it in the first
thread instead. Again, thank you all for your help and I apologize for any
inconvenience I may have caused.

------------

> Hi Oli,
>
[quoted text clipped - 21 lines]
>
> HTH

Reply to this Message