After our previous three SEO spreadsheet articles …
- How to Automatically Track Your Google Positions in Microsoft Excel
- A Search Engine Keyword Variation Generator in Excel
- 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:
- Download the Excel file: redirect-checker.xls and open the spreadsheet.
- Enable macros.
- 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 (http:// or https://), then the default http protocol is added.
If (InStr(url, "://") = 0) Then url = "http://" & 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?
Yoav co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to XLS conversion software. For more Excel tips, join him on Facebook or Twitter