Linked Word/Excel: Changing Excel Source File path

Question

Greetings,  

I use a linked word/excel document with 140 or so linked places.  When I email the Word and Excel or move them to a new location, the links no longer work (of course).  The way I change it is hitting Alt-F9 then Find/Replace the file name for the old excel document with the file name for the new excel doc.

So the excel name when I hit Alt-F9 is something like: C:\Users\vm\desktop\template\template.xlsx.  

Is there somewhere I can go that shows the source path for the Excel document that uses two backslashes like the above?  Currently, I right click on the Excel file, click Security, then copy the "Object name".  However, that only includes one backslash (i.e. it would be like the above with single backslashes: C:\Users\vm\desktop\template\template.xlsx), so then I have to add a second backslash when I paste to the find/replace box in my Word document.  A minor inconvenience, but I thought perhaps there is a way around it...

Thanks in advance!

Answer 1

See fellow MVP Macropod's article at:

http://windowssecrets.com/forums/showthread.php/154379-Word-Fields-and-Relative-Paths-to-External-Files

However, if that method does not work for you, you could use the following:

' 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

Answer 2

See the article "What do I do with macros sent to me by other newsgroup readers to help me out?” at:

http://www.word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

Answer 3

Thanks, Doug.  I have never created a Macro (I hang my head in shame); this solution is a little beyond me at the moment.

However, I intend to learn - I'll definitely save this and come back to it.

thanks again,

Victor