How to Create a Link Checker with Microsoft Excel
If you do any serious link building for search engine optimization you will quickly realize that you need to track the inbound links that you generate. Sometimes link appear and disappear, or turn into no-follow (which Search Engines are not meant to recognize), and so on. You need to keep track, you need to know if your efforts are paying off or going to waste.
There are commercial products that will watch all your links for you, but did you know Excel is perfectly capable of doing this job for you for free?
The Excel Link Checker
If you would like to get our Excel Link Checking spreadsheet, go here and download it. When you run it, make sure you enable macros.
Enter your website URL without the “http://” in cell B2 then enter the URLs of any pages on the web that should be linking to your website in cells A5, A6, and so on.
When you are ready to check the links, hit Ctrl-Shift-B. All the links that are supposed to exist will be checked, and if the link is found it will discover the target page and if it is do-follow!
How it Works
Essentially what the macro does is it retrieves the contents of each of the pages, and scans for links to your website.
Only the first link to your website in each page is considered. If a link appears then its anchor text and target etc are displayed next to the page’s URL. If a back-link is not found, a note to that effect is shown.
The information can be updated only once a day (configurable in the code), so you don’t end up hammering the websites that link to you.
The main macro, CheckBacklinks, simply creates a BacklinkChecker object for each URL that needs updating, and then waits for all objects to finish.
The juicy stuff is all inside the BacklinkChecker class.
Just like with the previous tool, the search result ranking tracker, we use WinHTTP to fetch the contents of pages while masquerading with an Internet Explorer 7 User-Agent string.
Unlike the previous tool, however, the code here uses asynchronous I/O mode. In this mode the VBA code gets an event notification as soon as the page has finished downloading. This allows us to retrieve many pages in parallel, and is a very good thing because we assume that very few URLs in the list will reside on the same physical server.
Private WithEvents http As WinHttp.WinHttpRequest Sub http_OnResponseFinished() ... End Sub
Also unlike the previous tool, the HTML parsing is done using MSHTML library. By utilizing the HTMLDocument object, we get full access to the page’s DOM. Just like had the page been opened in Internet Explorer.
Dim doc As New HTMLDocument doc.body.innerHTML = http.ResponseText For Each link In doc.Links ... Next
With the DOM in place, the code iterates over the links in the document, looking for our precious backlinks.
Summary
If you are doing guest posting, linkbait, reciprocal links, or (shhh!) buying links, then you really need to be checking that all your hard linkbuilding efforts are turning into profitable links. Do you think you could use a tool like this?
About the author
Yoav is the CEO of a company called Cogniview that creates software to convert files from PDF to XLS.
Prior to that, Yoav Ezer was the CEO of Nocturnus, a technology-centered software solution company.
For more Excel tips from Yoav, join him on Facebook or Twitter or leave a comment in the comment section below.
HI
I have been using this link checking tool and it has proved to be very useful, however recently there have been multiple run time errors occuring. Have you updated it anywhere?
THe error that I am now receiving which seems to stop it from working atall is: System Error &H80072EE6 (-2147012890).
Does that mean anything to you?
Your help will be most appreciated.
Many thanks
Magnus
Hey Willem, will the sample excel file also work for mac?