Oli
Wed Aug 01 09:36:10 CDT 2007
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
"Doug Robbins - Word MVP" wrote:
> 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
>
>
> --
> 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
>
> "Oli" <Oli@discussions.microsoft.com> wrote in message
> news:E67DD8C3-5854-4C40-86A3-06F8760450F8@microsoft.com...
> >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!
> >
>
>
>