hyperlink function verification

[Mike Dickey]

I have a spreadsheet that allows me to filter down to
specific groups of items in an inventory system. I used
the HYPERLINK function to create links to PDF drawings
(approx. 900) to confirm part selection. Creating
hyperlinks for each drawing would be much too time
consuming. The drawings are updated or changed once in a
while. Over time I have encountered broken links in the
spreadsheet. I need help verifying the existence of the
links. Does anyone know of a function, marco, or VBA code
that will verify the existence of the target file for each
hyperlink?

 

[Tom Ogilvy]

Assuming the Address of the hyperlink contains the fully qualified path to
the file then

for each lnk in Activesheet.hyperlinks
sAddr = lnk.Address
if dir(sAddr) = "" then
' not found, do what
else
' verified, do what
end if
Next

 

[Mike Dickey]

Thanks Tom,

I made a few changes to massage this into a function. It
does what I need.

Public Function CheckHyperlink(sAddr As String) As Boolean
If Dir(sAddr) = "" Then
CheckHyperlink = False
Else
CheckHyperlink = True
End If
End Function

I had to copy the link_location into another cell because
it was reading the entire cell contents as a string. Is
there some way to get just the link_location argument from
the HYPERLINK function without parsing in this case?