If You want to check if link is still active, You will just click it and see if site is online. But what should You do if You got 1000 links to check? Or more? In this article I’ll show You how to use WinHttp Request to get your site status.
In the first place I thought that my code should open Internet Explorer or any other Internet browser, then go to the chosen site address and based on content macro will know if it is active, or not. Luckily, few minutes of Google searching later I realized, that there are easier methods of checking website status.
Finally, I’ve chosen WinHttpRequest, because it was giving me all the information I wanted.
Set xmlhttp = CreateObject("WinHttp.WinHttpRequest.5.1")
By information I mean the codes, whole variety of status codes. After some tests with different website links I realized, that I am interested in 2 numbers – 200 and 300.
Why these numbers?
First number – 200 – stands for situation, when your browser displays the file, for instance instruction in .pdf, or when your browser asks to save this file on your computer. Second number – 300 – stands for fully loaded website.
I was not interested in:
– redirection to other location or to homepage,
– error 404 page not found,
– situation when page can’t load at all.
Avoid unexpected issues
First two points are giving status number (301, 302 and 404), but the last one is different history. For this kind of situation I implemented into code:
On Error Resume Next If Err.Number <> 0 Then ... Else ... End If On Error GoTo 0
This part is responsible to keep the code safe and uninterrupted.
Security Certificate warning
This was the second thing, which I struggled with working on this tool. After research I found out, that one magic line can deal with the problem:
xmlhttp.Option(6) = False
Reset WinHttp object
Also a good thing is to reset object to, let’s call it, clean the memory.
Set xmlhttp = Nothing
I did not notice much difference with or without it in this case, but I consider this as a good approach.
Imagine a simple table with 10 rows of links You want to check in column A. If the link is active code will fill cell as green, if not as red.
If getStatus <> "300" And getStatus <> "200" Then .Cells(i, 1).Interior.ColorIndex = 3 Else .Cells(i, 1).Interior.ColorIndex = 4 End If
Option Explicit Sub winHttpRequest() Dim xmlhttp As Object Dim myURL As String Dim getPageText As String Dim getStatus As String Dim lastRow As Long, i As Long Dim arr As Variant With ThisWorkbook.Sheets(1) lastRow = .Cells(Rows.Count, 1).End(xlUp).Row arr = .Range(.Cells(1, 1), .Cells(lastRow, 1)) For i = 1 To UBound(arr) myURL = arr(i, 1) On Error Resume Next Set xmlhttp = CreateObject("WinHttp.WinHttpRequest.5.1") xmlhttp.Option(6) = False xmlhttp.Open "GET", myURL, False xmlhttp.send If Err.Number <> 0 Then 'sometimes there is no "asnwer" from server Else getStatus = xmlhttp.Status If getStatus <> "300" And getStatus <> "200" Then .Cells(i, 1).Interior.ColorIndex = 3 Else .Cells(i, 1).Interior.ColorIndex = 4 End If End If On Error GoTo 0 Set xmlhttp = Nothing Next i End With End Sub
WinHttp Request is a really good option to get your site status. There are other options, but in case of delivered information I would recommend that method. By using WinHttp You can achieve also other goals, but about that maybe in other article.