Changing Excel links inside Word Documents

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
Tue May 03 13:18:08 CDT 2005

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" <mbowersox@discussions.microsoft.com> wrote in message
news:4E611FA8-A382-49C2-8088-B828EC0B414A@microsoft.com...
> 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. :(

mbowersox
Wed May 04 09:08:08 CDT 2005

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
Mon May 09 07:48:58 CDT 2005

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


"mbowersox" <mbowersox@discussions.microsoft.com> wrote in message
news:165FF629-E4BC-4F44-B5B0-2497A118BF96@microsoft.com...
> 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