How to Check HTTP Redirects with Microsoft Excel

by Yoav Ezer July 6th, 2010 

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 (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")

Redirect Checker Spreadsheet

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 Ezer

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

Codswallop blog

You May Also Like

15 Responses to “How to Check HTTP Redirects with Microsoft Excel”

  1. adamSEO says:

    Download link isn't working for me
    .-= adamSEO recently posted: Magento URL Suffix- How to Add a Trailing Slash =-.

  2. [...] How to Check HTTP Redirects with Microsoft Excel Written by Yoav Ezer [...]

  3. Jb Diaz says:

    Wow!, this helped me a lot. Thank you so much!. Is it also possible to retrieve specific meta tags using Excel?

    • Yoav Ezer says:

      Jb Diaz,
      I'm happy to know this tool helped you.
      I suppose you are talking about the meta-description tag. Is there any other meta tag you would want to retrieve?

  4. Jb Diaz says:

    Yes, other than the description, I would also like to retrieve the title and keyword tags for a certain page. Thanks a lot you are awesome!.

  5. Derrick says:

    is there anyway to modify this spreadsheet to check mulitple entries in column C and then subsequently return the redirected URLs in column D?

  6. Vincent says:

    Thanks for sharing this Yoav and already we are finding it very useful. We just redesigned (two week ago) and changed our entire format to 'pretty urls' the entire site is redirected and is a bit of a challenge to monitor the 301's. This sheet is helpful.

    Nice post and thanks

  7. Vikas says:

    Hi,

    The Excel tool to check redirects is really cool. Will it be possible for you to update the sheet that allows one to check hundreds of URls, for example (e.g. redirect URLs in one coloumn and other in second column).

    I would appreciate it if you could please do that.

    Thanks,
    Vikas

  8. This is fantastic – could this be modified to pick up additional redirects (redirect chains)?

    Thanks!
    Dave

  9. Wilson Moss says:

    @Vikas – I think what you're looking for is here.

    http://www.tips-for-excel.com/tools/url-checking-tool/

    @Yoav – nice post, this saved me heaps of time

  10. Michael says:

    Hi,

    I wondered is there's way that we can change and amend this spreadsheet to check mulitple entries in column C and then return the redirected URLs in column D?

  11. Vikas says:

    Hi Wilson,

    Thanks for the link. I downloaded the excel and the macro was working fine…well, until now. Now it says – this version is old and download a new version from tips-for-excel.com. I tried downloading new excel file and the problem is still the same – so it does not work anymore. But, thanks for your help and it seemed really useful.

    Cheers,
    Vikas

  12. J Mann says:

    Hey Yoav,
    Very cool, this redirect-checker. I'm wondering, and I'll bet a smrt guy like you can easily make it work for A SERIES of URL's?
    So, instead of just entering a url in cell C2, maybe one could enter a series of url's in a column, (Column C) for example, and the redirect type and new locations show up in different columns, (columns D and E), with each column D and E result in the row specific for the checked url?
    I could really, really use something like that and I don't know how to write scripts.