Verifying Hyperlinks to PDF Files on Internal Network

[tripodgod]

Hello!

I have been stuck on this issue for a while now. I have an Excel 2010 based userform which links to over 1000 PDF's stored on an internal network (filtered based on a criteria, which in turn uses the "ThisWorkbook.FollowHyperlink ****.Caption).

The trouble I'm having is verifying all the links work (as they were manually input over many hours).

Through research I managed to find code to check a "HTTP" based hyperlink is valid, can I adapt it to check an internal hyperlink?

Here is the code (credit to the author):

For Each alink In Cells.Hyperlinks
    strURL = alink.Address

    If Left(strURL, 4) <> "[B]http[/B]" Then
        strURL = ThisWorkbook.BuiltinDocumentProperties("Hyperlink Base") & strURL
    End If

    Application.StatusBar = "Testing Link: " & strURL
    Set objhttp = CreateObject("MSXML2.XMLHTTP")
    objhttp.Open "HEAD", strURL, False
    objhttp.Send
    If objhttp.statustext <> "OK" Then
        alink.Parent.Interior.Color = 255
    End If
Next alink
Application.StatusBar = False
On Error GoTo 0
MsgBox ("Checking Complete!" & vbCrLf & vbCrLf & "Cells With Broken or Suspect Links are Highlighted in RED.")

Unfortunately I have little experience in coding, and admittedly have relied a lot on this forum!

Thank you for your help!

 

[Andrew Poulsom]

Can't you just use DIR on each Hyperlink's Address property? It will return "" if the file doesn't exist.

 

[tripodgod]

Can't you just use DIR on each Hyperlink's Address property? It will return "" if the file doesn't exist.

Hi Andrew

Thank you for coming back to me!

Forgive me for being stupid but the links are saved on a worksheet in this format:

\\network\marketing\Jo\PROPERTY DATABASE\a. House Database\Abbey\Documents\2400.pdf

How could I incorporate your suggestion in to the Hyperlink Address property? The link is vlookup'd to a caption on the userform, which in turn is opened on the caption being pressed?

I just need to validate the long list of hyperlinks to check no mistakes have been made, as if so, the userform crashes!

Thank again.

 

[Andrew Poulsom]

If the cell contains a Hyperlink you can use it's Address property like in the code you posted:

    Dim alink As Hyperlink
    Dim StrURL As String
    For Each alink In ActiveSheet.Cells.Hyperlinks
        StrURL = alink.Address
        If Dir(StrURL) = "" Then
            alink.Parent.Interior.Color = 255
        End If
    Next alink

 

[tripodgod]

Hi Andrew

That worked perfectly, thank you so much!

Trying to learn code with no prior experience is a challenge, so thanks again.