Ajayxlnc
Firstly, Welcome to the Chandoo.org Forums
A small point, your list is a list of URL's, not a list of Hyperlinks
I'm not an expert in web access and so will provide some code and let you play with it.
Please read instructions below the code
- - - - - -
[pre]
Code:
Declare PtrSafe Function InternetGetConnectedState Lib "wininet" (ByRef dwFlags As Long, ByVal dwReserved As Long) As Long
'Delete the PtrSafe word above if not on a 64 bit machine
Sub Clear_URLs()
Dim oLink As Hyperlink
Dim sFile As Boolean
Dim ws As Worksheet
Dim rCell As Range
For Each ws In ActiveWorkbook.Worksheets
For Each rCell In ActiveSheet.UsedRange.Cells
sFile = GetHTML("Http://" + rCell.Text)
If sFile Then
rCell.Interior.Color = 65535 'Highlight Good Links Yellow
Else
rCell.Interior.Color = 255 'Highlight Bad Links Red
'rCell.ClearContents
End If
Next
Next
End Sub
Function GetHTML(URL As String) As Boolean
Dim oHTTP As MSXML2.XMLHTTP
GetHTML = False 'Set default
If Len(URL) Then
Set oHTTP = New MSXML2.XMLHTTP
oHTTP.Open "GET", URL, False
On Error Resume Next
oHTTP.send
If Len(oHTTP.responseText) Then GetHTML = True 'This does the deed
Set oHTTP = Nothing
End If
End Function
[/pre]
- - - - - -
In addition to this you need to go into VBA, Tools, References
Scroll down to the Microsoft XML, v6.0
or Tick the highest number you have if you don't have 6.0
Also note the comment
'Delete the PtrSafe word above if not on a 64 bit machine
Run the subroutine
Clear_URLs
The problem with HP URL's is that although the URL maynot exist HP redirects you somewhere else.
So this code works well for sites that don't do that
But doesn't work well for sites that do, like yours.
Hopefully somebody who understands what Microsoft XML or MSXML2.XMLHTTP objects can chip in here and assist?
Please let us know how you go?
