How to Check HTTP Redirects with Microsoft Excel

After our previous three SEO spreadsheet articles ...

1. How to Automatically Track Your Google Positions in Microsoft Excel
2. A Search Engine Keyword Variation Generator in Excel
3. An automatic back-link checker tool in Excel

... we thought we would show you another nice little Excel SEO trick to add to your toolbox. This is a tiny utility that will tell you if a link is working and what type of redirect a URL is using. OK, not mega useful, but it does demonstrate some nice Excel techniques and gives you an idea of what kind of thing Excel is capable of in your SEO work.

How to use it

The spreadsheet is quite simple, all the magic happens behind the scenes:

1. Download the Excel file: redirect-checker.xls and open the spreadsheet.
2. Enable macros.
3. Enter a URL in cell C2

The script will go away and request the URL and look at the response. Whatever response code is returned will be displayed along with the status text.

How it works

Our "trick" here is to retrieve the URL via HTTP GET, and check the returned status code (such as 404 not found or 301 permanent redirect).

This script is automatic, so it hooks onto the Worksheet_Change event. If the changed target range is named "URL", then processing begins.

As with the previous tools, we use the WinHTTP library, but in this case we must set a special option to prevent WinHTTP from using redirects, otherwise we end up looking at the redirect target URL, instead of the redirect response itself.

http.Option(WinHttpRequestOption_EnableRedirects) = False

Also, you may note that just like the previous tools, this tool also uses the Internet Explorer 7 User-Agent string.

If the typed URL is missing the protocol (https:// or https://), then the default http protocol is added.

If (InStr(url, "://") = 0) Then
    url = "https://" & url
End If

Then, any error that may be thrown by WinHTTP is caught, handled gracefully, and displayed.

On Error Resume Next
...
http.Open "GET", url
If Err.Number <> 0 Then
    sh.Cells(3, 3).Formula = Trim(Err.Description)
    Exit Sub
End If
http.Send
If Err.Number <> 0 Then
    sh.Cells(3, 3).Formula = Trim(Err.Description)
    Exit Sub
End If

And finally if everything worked so far, the result is displayed

sh.Cells(3, 3).Formula = http.Status & " " & http.StatusText
sh.Cells(4, 3).Formula = http.GetResponseHeader("Location")

What Could You Use this for?

As mentioned above, the obvious purpose of this is to check a link is working and what response is returned. Imagine extending this macro to go through a list of important links (landing pages, sales letters, and so on).

Another option is to check a shortened URL to see where it goes, perhaps to avoid Trojans and other malware, or to see if someone is cloaking affiliate or other links.

Have you got any ideas for how this might be useful?