WinHttp Request – get your site status

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.

Example sheet

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

Code

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

Summary

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.