Macro to update links

[Tiffany]

Hi there,

I have a word doc that has about 30 links to an excel file. Each month when
a report is written the links need to be updated to the next month's file. I
was wondering if I get help in writing a macro to prompt the user to select
the file to update the links from.

Thanks, any help would be much appreciated.

 

[Doug Robbins - Word MVP]

' 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

 

[Tiffany]

Do I just paste this into the VB editor??
How do I get the user to select the file that they want the file to be
updated from??

 

[Tiffany]

I tried copying this into VB editor and it doesn't do anything, I went to
call the macro to run, but there was no macro to run.
I am quite new to VB.

 

[Doug Robbins - Word MVP]

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

Run the macro when the document that contains the links that you want to
update is the active document.


--
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

 

[Tiffany]

Ok, I got it now, thanks
But this macro makes the user type in the path of the filename.
Is there a way that the user can select the file??
I just tried using it, but it gave me the following error message:
"Word is unable to create a link to the object you specified. Please insert
the object directly into your file without creating a link". So I clicked ok,
then got:
Error! Not a valid link.

 

[Graham Mayor]

The following modification of Doug's macro will allow the user to pick the
new link file and will update the filed to show the new result.
The error message is caused when during the replacement the double slashes
in the path are replaced by single slashes. The modification also addresses
that issue.

Sub UpdateLinks()
' Macro created 26/10/01 by Doug Robbins to update links in a document
' With mods 21/10/08 by Graham Mayor to select file.
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, Newfile
Dim fDialog As FileDialog
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 = "Select new link file and click OK"
Title = "Update Link"
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.Title = Message
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
If .Show <> -1 Then
MsgBox "Cancelled By User", , Title
Exit Sub
End If
Newfile = fDialog.SelectedItems.Item(1)
Newfile = Right(Newfile, Len(Newfile) - InStrRev(Newfile,
"\"))
End With
End If
linkcode.Text = linktype & Newfile & linklocation
counter = counter + 1
alink.Update
End If
Next alink
End Sub

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Tiffany]

This was exactly what I was looking for, I am soooo happy, thank-you heaps

Tiffany

 

[Graham Mayor]

On further reflection I would change the line

Newfile = Right(Newfile, Len(Newfile) - InStrRev(Newfile, "\"))
to
Newfile = Replace(Newfile, "\", "\\")

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>