Test External Hyperlinks

**jeffdino** · 10-26-2020, 02:53 PM

Hello,

I want to test external hyperlinks, that when they are broken color the cell yellow. I have this code but it seem to only work with an internal network

Sub TestHLinkValidity()
Dim rRng As Range
Dim fsoFSO As Object
Dim strFullPath As String

Dim strPath As String
Dim strFName As String
Dim cCell As Range
Dim response
Dim intSlashCount As Integer

Set fsoFSO = CreateObject("Scripting.FileSystemObject")
Set rRng = Selection
For Each cCell In rRng.Cells
If cCell.Hyperlinks.Count > 0 Then
strPath = GetHlinkAddr(cCell)
If fsoFSO.FileExists(strPath) = False Then
cCell.Interior.Color = 65535
End If
End If
Next cCell
End Sub

Function GetHlinkAddr(rngHlinkCell As Range)
GetHlinkAddr = rngHlinkCell.Hyperlinks(1).Address
End Function

Thanks
Jeff

**CK76** · 10-26-2020, 03:30 PM

Assuming external link always contain "http" in hyperlink.address.

May be something like...

Sub Demo()
Dim rng As Range
Dim cel As Range

With Sheets("Sheet1")
    Set rng = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With

For Each cel In rng.Cells
    If cel.Hyperlinks.Count > 0 And InStr(cel.Hyperlinks(1).Address, "http") Then
        With CreateObject("MSXML2.ServerXMLHTTP")
            .Open "GET", cel.Hyperlinks(1).Address, False
            .send
            If .Status = 200 Then
            Else
              cel.Interior.Color = 65535
            End If
        End With
    ElseIf cel.Hyperlinks.Count > 0 Then
        'Do something for local file link.
    End If
Next
End Sub

**jeffdino** · 10-27-2020, 02:28 PM

Thanks. Got a RT ERROR 9
subscript out of range

**CK76** · 10-28-2020, 06:57 AM

At which line of the code?

That usually means some object isn't found. By any chance are you using Mac?

Test External Hyperlinks

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Test External Hyperlinks

Re: Test External Hyperlinks

Re: Test External Hyperlinks

Re: Test External Hyperlinks

Thread Information

Users Browsing this Thread

Similar Threads

Test for invalid Hyperlinks

[SOLVED] Test Hyperlinks to files on Network Drive

Automatically Test Hyperlinks in Excel 2007

Automatically Test Hyperlinks in Excel 2007

maintain hyperlinks with external links

Capturing hyperlinks from external site

How can I test whether or not an external file exists?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions