How can I automatically change the path names of Excel OLE objects in Word Document

[Cevriye Kuntay]

Hi!
I created a Microsoft Word document that contains dozens of linked objects
(OLE's). 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 pathname: "C:\drive\documents\file1.xls" and replace it with
"C:\drive\documents\file2.xls". I have the following as a beginnig point
but I could not finish it. This is my first attempt to write a VBA code. Any
help you could provide will be greatly appreciated. Here is my incomplete
code:

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:

'Code to modify strLink

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

End With

 

[Jean-Guy Marcil]

Cevriye Kuntay was telling us:
Cevriye Kuntay nous racontait que :

Hi!
I created a Microsoft Word document that contains dozens of linked
objects (OLE's). 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 pathname: "C:\drive\documents\file1.xls"
and replace it with "C:\drive\documents\file2.xls". I have the
following as a beginnig point but I could not finish it. This is my
first attempt to write a VBA code. Any help you could provide will be
greatly appreciated. Here is my incomplete code:

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:

'Code to modify strLink

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

End With

This is the third thread you have started on the same topic. It is not the
way to do things in the newsgroups.

And by the way, this is not your code... It is code I modified from a KB
article, which I got by using a link you posted in your first thread on the
topic.

Helmut, Doug and I have been trying to help and have provided some answers,
comments as well as questions, which you must answer if more help can be
provided.

Starting a new thread is just a waste of time for all involved.

Please, go back to the other two thread and answer the questions or reply to
the comments that were made there.

Thanks.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Oli]

So sorry!!! Please forgive my ignorance. This is the first time in my life
that I ever asked for a help from a discussion group and I did not realize
they all go to the same place. If there's a way of deleting redundancies,
please delete them. Thanks.

 

[Jean-Guy Marcil]

Oli was telling us:
Oli nous racontait que :

So sorry!!! Please forgive my ignorance. This is the first time in
my life that I ever asked for a help from a discussion group and I
did not realize they all go to the same place. If there's a way of
deleting redundancies, please delete them. Thanks.

Don't worry about it...Now you know!

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org