Changing Excel links inside Word Documents

[mbowersox]

Hello,

Currently the company I am working for has lots of Word documents with
embedded Excel objects in them which were linked using Word's Edit->Paste
Special. We recently switched to a new server so I am in the process of
writing a script that will allow changing of these links to be trivial to our
word processing department.

I did a search and saw a lot of posts about this topic, but none that really
hit the problem I'm having on the head. I am currently using a VB Script
that simulates keypresses to show field codes, do a find replace on the
location, and hide the field codes. The problem is after I have changed the
links, I cannot update any of the links in the file by selecting the link and
pressing F9. It just tells me that the object could not be found.

Now the weird part is, after I have changed the link code to the new
location and try to update the link, the link changes back to the old
location!

I'm just wondering if anyone has seen something like this before and whether
or not there is a way to fix it. The link paths are all UNC paths, not
mapped drive letters and I'm pretty sure the files are in Word 95/6.0 format,
but we are currently using Office XP Small Business. I'm not sure if that's
the problem but I am hoping there is a way to fix it. This is the code I am
using:

Set WshShell = CreateObject("WScript.Shell")
Dim wordFilePath
wordFilePath = BrowseForFile()
Set wordApp = CreateObject( "Word.Application" )
wordApp.Visible = True
wordApp.Documents.Open wordFilePath
WshShell.AppActivate( "Microsoft Word" )
WScript.Sleep 5000
WshShell.SendKeys "%{F9}"
WScript.Sleep 5000
WshShell.SendKeys "^h"
WScript.Sleep 3000
WshShell.SendKeys "Old UNC Path"
WScript.Sleep 1000
WshShell.SendKeys "{TAB}"
WScript.Sleep 500
WshShell.SendKeys "New UNC Path"
WshShell.SendKeys "{TAB}"
WScript.Sleep 200
WshShell.SendKeys "{TAB}"
WScript.Sleep 200
WshShell.SendKeys "{TAB}"
WScript.Sleep 200
WshShell.SendKeys "{TAB}"
WScript.Sleep 200
WshShell.SendKeys " "
WScript.Sleep 1000
WshShell.SendKeys "{ENTER}"
WScript.Sleep 1000
WshShell.SendKeys "%{F4}"
WScript.Sleep 1000
WshShell.SendKeys "%{F9}"
WScript.Sleep 2000
MsgBox "This Word document is fixed. Click ""OK"" to begin using it!"

Function BrowseForFile()
Set objOpenDialog = CreateObject( "UserAccounts.CommonDialog" )
objOpenDialog.Filter = "Microsoft Word Documents | *.doc"
objOpenDialog.InitialDir = "C:\DOCUMENTS AND SETTINGS\ADMINISTRATOR\DESKTOP"
objOpenDialog.Flags = &H80000 + &H4 + &H8
intResult = objOpenDialog.ShowOpen
BrowseForFile = objOpenDialog.FileName
End Function

Many thanks in advance for all the help I receive.

Best Regards,
Mike

P.S. - I have tried doing this as a Macro inside of Word with some code that
I found in the groups, but the same things happens with VBA.

 

[Doug Robbins]

See if this helps:

' 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

 

[mbowersox]

Thank you for you help Doug. Unfortunately the VBA code resulted in the same
problem I was experiencing with my VB Script. Forunately though, I found
that the links have to be replaced and updated twice before they will
actually stay in the file and work. Quite odd huh? Thank you for your help
though.

Best Regards,
Mike

 

[macropod]

Hi Mike,

For another macro solution, the utility at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=261488
(url all one line) may help.

Cheers