Editing Word Excel Links

[Partho]

I have some experience with developing macros in Excel,
but have never done it in Word. I have a Word document
with a large number of links with an Excel spreadsheet. I
have now updated the Excel source spreadsheet and re-named
it. I am trying to develop a Word macro to edit the links
in the Word document, ie replacing the old Excel filename
with the new one.The Excel source cells are all named
ranges with names like Cell1, Cell2, Cell3 etc etc.

Any guidance with this would be greatly appreciated. TIA.

Regards,
Partho

 

[Doug Robbins - Word MVP]

Hi Partho,

The following macro should 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


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Cindy Meister -WordMVP-]

Hi Partho,

I have a somewhat different approach than Doug's:

display the field codes
Find/Replace the old path with the new one
hide the field codes

Better, if you know this could be happening when setting the
document up, is to put the path into a custom document
variable, and DocProperty fields into the LINK fields. Then
you need to make the change in only one place...

I have some experience with developing macros in Excel,
but have never done it in Word. I have a Word document
with a large number of links with an Excel spreadsheet. I
have now updated the Excel source spreadsheet and re-named
it. I am trying to develop a Word macro to edit the links
in the Word document, ie replacing the old Excel filename
with the new one.The Excel source cells are all named
ranges with names like Cell1, Cell2, Cell3 etc etc.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan
24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Partho]

Hi Cindy,

Thanks for your assistance. Could you please provide some
sample VBA code to display the field codes and perform the
search/replace? The only change in the linked Excel
filename is a replacement of 02 with 03 in the string (old
filename: S0212EDPLConsol.xls, new filename:
S0312EDPLConsol.xls). Your help is much appreciated.

Regards,
Partho

 

[Doug Robbins - Word MVP]

Hi Partho,

While you can create a macro to do the Edit>Replace, I believe that what
Cindy was suggesting was that you just use the Replace utility that is
available from the Edit menu in Word.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Cindy Meister -WordMVP-]

Hi Partho,

Well, you could record this in a macro:

Alt+F9
Find: S0212EDPL 'just to make sure you don't affect any other
02s in the document
Replace with: S0312EDPL
Alt+F9

Could you please provide some
sample VBA code to display the field codes and perform the
search/replace? The only change in the linked Excel
filename is a replacement of 02 with 03 in the string (old
filename: S0212EDPLConsol.xls, new filename:
S0312EDPLConsol.xls).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan
24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail