Change hyperlink address

[Sibilia]

Hi all,

I have in my excel document an hyperlink to a word document.
If the word document is moved to another place in the computer, the
hyperlink doesn't work anymore.
If the hyperlink cannot be find, is there a way to change its address
using VBA ? with a window like "open document" where you can search the
file that you want to open?

Many thanks


Sibilia

 

[Guest]

I don't know how you would trap the error when a file has been moved but what
you could do is check all hyperlinks on a sheet when it is activated. If any
are found which wil fail then a prompt will allow you to assign a new file.

Private Sub Worksheet_Activate()
Dim Hpr As Hyperlink
Dim hAdr As String
Dim newLink As Variant
Dim fndAdr As String
Dim nTitle As String

For Each Hpr In Me.Hyperlinks
hAdr = Hpr.Address
fndAdr = Dir(hAdr)
If fndAdr = "" Then
nTitle = "Select file for hyperlink: " & Hpr.Name
newLink = Application.GetOpenFilename(, , nTitle)
If newLink = False Then
Else
Hpr.Address = newLink
End If
End If
Next Hpr
End Sub

This is worksheet event code: right click the sheet tab, select view code
and paste in there.

Hope this helps
Rowan