Test a Hyperlink

[Guest]

I have a large file w/ Hyperlinks to various PDFs.
I am wanting to try and run a MACRO that will go through and
test to make sure all of the links work correctly.
Is there a way to do this?
Ben

 

[Guest]

Ben, I had a similar requirement some months ago and came up with the
following.
Note that this is to test for hyperlinks to PDFs residing on the LAN you are
woking on. I have also found it necessary to set Files, Properties,
Hyplerlink Base to "\\" (without the inverted commas). This gets around
problems created when different users open the same file when they have a
different relative path for their logon.

Private Sub CheckAllPDFHyperlinks()
Dim test As Boolean
Dim count As Integer
Dim h As Hyperlink

count = 0

test = True
On Error GoTo OnError

For Each h In ActiveSheet.Hyperlinks
If InStr(UCase(h.Name), "PDF") > 0 Then
If Dir(h.Address) = "" Then
test = False
MsgBox h.Name & vbCr & _
h.Address & vbCr & _
h.SubAddress & vbCr & _
"in cell " & h.Range.Address & vbCr & _
"link path does not exist"
response = MsgBox("Do you want to continue?", vbYesNo)
If response <> vbYes Then
response = MsgBox("Do you want to goto this cell?", vbYesNo)
If response = vbYes Then
Range(h.Range.Address).Select
End If
Application.StatusBar = False
Exit Sub
End If
End If
End If
count = count + 1
Application.StatusBar = "Links checked: " & count
Next h
If test Then MsgBox count & " Hyperlinks checked ok."
Application.StatusBar = False
Exit Sub

OnError:
MsgBox ("Error with: " & h.Name & vbCr & "Unable to search this path.
Possible invalid characters or invalid syntax.")
response = MsgBox("Do you want to goto this cell?", vbYesNo)
If response = vbYes Then
Range(h.Range.Address).Select
End If
Application.StatusBar = False
End Sub