Toll Free: 1-877-695-7388

GTA: (647) 699-2838

Search Engine People
  • SEO
  • SEM
  • CRO
  • Display
  • Blog
  • Why Us
  • Contact
  • Join Our Team
  • Get A Quote

Toll Free: 1-877-695-7388

GTA: (647) 699-2838

How to Check HTTP Redirects with Microsoft Excel

Yoav Ezer | July 6th, 2010
Tweet12
Share4
Share
Pin
16 Shares

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?

Tweet12
Share4
Share
Pin
16 Shares
Posted in Web DesignTagged excel, http, redirect, spreadsheet

About the Author: 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

13 thoughts on “How to Check HTTP Redirects with Microsoft Excel”

  1. adamSEO says:
    July 9, 2010 at 1:44 am

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

    1. Yoav Ezer says:
      July 10, 2010 at 10:35 am

      Adam, I’ve fixed the link. Please let me know if it works for you now.

  2. Jb Diaz says:
    August 28, 2010 at 1:06 am

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

    1. Yoav Ezer says:
      August 29, 2010 at 1:21 am

      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?

  3. Jb Diaz says:
    August 31, 2010 at 11:10 pm

    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!.

  4. Derrick says:
    November 13, 2010 at 11:28 pm

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

  5. Vincent says:
    December 2, 2010 at 3:38 am

    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

  6. Vikas says:
    February 14, 2011 at 10:01 am

    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

  7. David Reynolds says:
    March 10, 2011 at 4:11 am

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

    Thanks!
    Dave

  8. Wilson Moss says:
    May 19, 2011 at 9:14 am

    @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

  9. Michael says:
    June 9, 2011 at 11:19 am

    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?

  10. Vikas says:
    June 10, 2011 at 1:24 am

    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

  11. J Mann says:
    May 18, 2012 at 7:40 pm

    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.

Comments are closed.

Recent Posts

  • Maximizing Your E-Commerce Sales:
    A CRO Audit Guide
  • Movin’ On Up! Why Migrating to Google Analytics 4 (GA4) Should be a Priority
  • A Year in Review: The Digital Marketing Trends That Defined 2021
  • The Basics of Video Marketing
  • Just How Much Do Google Reviews Impact Your SEO Ranking?

Categories

  • Analytics & ROI Analysis
  • Company News
  • Content
  • Conversion Optimization
  • CRO
  • Display Advertising/RTB
  • Email Marketing
  • En Español
  • En Français
  • Inbound Marketing
  • Lead Nurture & Marketing Automation
  • Local Search
  • Marketing
  • Mobile
  • Partnership Marketing
  • PPC
  • PR
  • SEO
  • Social Media Marketing
  • Web Design

Additional Posts

5 Reasons Your Company May Never Adopt Twitter

July 5th, 2010 | by Dean Guadagni

Ruud Questions: Meg Geddes aka Netmeg

July 2nd, 2010 | by Ruud Hein

Google Stop The Rot Please

June 29th, 2010 | by Barry Welford

LET'S TALK

Need more information or want to get in touch?

Get in touch!
  • SEO
  • SEM
  • Display
  • Blog
  • Why Us
  • Join Our Team
  • Contact Us
  • Local SEO
  • Small Business SEO
  • Enterprise SEO
  • International SEO

LOCATION

1305 Pickering Parkway,
5th Floor Pickering, L1V 3P2

PHONE

Toll Free: 1-877-695-7388
Greater Toronto Area: (647) 699-2838

Social

© Search Engine People Inc. 2023 – Canada’s Top Digital Agency
© SEP 2023 – A Search Engine People Company | Privacy Policy

Search Engine People