How to Check HTTP Redirects with Microsoft Excel
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
Leave a Reply
Get the SEP Newsletter
Download link isn't working for me
.-= adamSEO recently posted: Magento URL Suffix- How to Add a Trailing Slash =-.
Adam, I've fixed the link. Please let me know if it works for you now.
[...] How to Check HTTP Redirects with Microsoft Excel Written by Yoav Ezer [...]
Wow!, this helped me a lot. Thank you so much!. Is it also possible to retrieve specific meta tags using Excel?
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?
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!.
is there anyway to modify this spreadsheet to check mulitple entries in column C and then subsequently return the redirected URLs in column D?
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
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
This is fantastic – could this be modified to pick up additional redirects (redirect chains)?
Thanks!
Dave
[...] 2. Check Your Redirects [...]
@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
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?
Michael recently posted: Benefits of Digital Signage and Digital Displays Solution
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