<

Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

Automated Link Checker

Published on
4,737 Points
1,637 Views
1 Endorsement
Last Modified:
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlighted yellow and are converted into a hyperlink.
Automation and link building do not belong in the same sentence, or do they? Some tasks involved in great link building can be tedious. Why not make your life a little easier? Part of resource link building involves reaching out to a lot of sites and offering an asset with a link back to your client. Every once in awhile, a site will put up your link and never tell you. No problem, you can run a backlink check and see any new links your client has obtained. But how do you sort out the links that you obtained through your own efforts?  
Well you would take your list of domain URL’s that you have reached out to and dump them into an Excel file. Then you would download any referring domain backlinks and add these to your list of URL’s that you have reached out to. 
Then type ALT+F8 and hit Enter and your work is done (if you used my code). 
tidg050puCuZGaFvyK-fLY3SS64FaqjXAHB0jV60 
Over the years I have spent a lot of time learning Excel, and how to automate projects. If there is a task that I do regularly, I try and automate what I can. Time is money, efficiency is key. Of course, some things should never be automated. Part of great link building involves building relationships with other sites. You are much more likely to build a relationship as yourself versus an automated robot. 
Use this script to simplify your link checking and save some time. This code will take your list of outreached sites combined with your client backlinks and look to see if you have gained any links through your outreach efforts. This is done by looking for URL’s that are duplicated in your list, suggesting that a site you outreached is showing a backlink to your client as well. 
The code takes your list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlighted yellow and are converted into a hyperlink for ease of use. 
How to get the code into Excel. 
Open up an Excel workbook.
Select the “File” tab. 
QMxNriU2b65svV26LsYqFjFfhc_U6s9uqHVa1IGm 
Select “Options” at the bottom of the File menu. 
pTi6yoUSwrX_i041Fv720ZtPZiogpvNTR-n2GS2h 
The Excel Options window will appear, select “Customize Ribbon”. 
dxXuqb-7pFcI015iIQzYCljBLUb6BFwzU1VaB2Xd 
Check the “Developer” box under Main Tabs. 
ctWEnqYwTmDSZPM5tD80L4SdDbd81-jc_jLq14p9 
The Developer tab will now appear in all of your Excel workbooks. 
JAt23QDTRnno3F0eStubBoP1tZiuqCYDtatV4lW4 
Select the “Developer” tab, then select “Visual Basic” on the very left. Alternatively, you can type ALT+F11. A new window will open up, Visual Basic Editor (VBE). 
gdFgyW-auCGIWdFNRvGGWiLRWjiip6Uiu-xhUQAr 
In the Project Tree, right-click VBAProject (PERSONAL.XLSB). 
mgBKPT1DrpBkzjW3R2_agsxCRWUUuL1F9FaI5_mU 
Select Insert, then Module. 
qjTTUej1ct4pJDEV9yYQmpWN2lB3ZN3AI2focqIM 
Copy the code in this article and paste into the Module window. 
U6lTp5vLk0R2dTfkUG6vXLGkjf1L0L_Gd-kFs3EH 
Click the “Save” icon or type CTRL+S to save the code into your Personal Workbook.
Close out the Visual Basic Editor.
Populate Column “A” with your list of domain URL’s that you have already outreached. Note that these URL’s are domain level. It is important that all URL’s in this list are domain level, so that duplicate values can be found. 
di3ZPjGD_2ijHBA9GJxtQFs56tnwkkE25tUe5K3R 
Run a backlink report on the client in question. Type your client URL in the Site Explorer. 
_cFNRbd2SiSXDZbr8VK1DbmoHM_O__ht07lm20L_ 
Click “Referring domains” in the menu on the left. 
Q-YW2gZPgEIdXd0ySVgyI5v73F_ru3XARS8D92ex 
Click “Export” on the right side. A new window will popup, select the “Microsoft Excel” radio button and click “Start Export”. 
Kiu7vXj6tHobdETbgdb7g9ZM578p0g1GBjiTUJlX 
Open up the workbook you just downloaded. In the “B” column, all Fresh domain level backlinks will be listed. Click the “B2” cell and hold down CTRL+SHIFT+DOWN to select all the URL’s in the “B” column. Then hold down CTRL+C to copy the selected cells. 
9hnCbfjIbwFmMnh1mkJa5UnHSMbuhlMMS2xFTa8H 
Now that you have the backlinks copied, close the workbook. Open up the Excel workbook with your already outreached URL’s. Select the first empty cell in column “A” and paste your backlinks by holding CTRL+V. 
wyXZwbCHBpIOdFm1T1qNhoyMddCr7l3HP_iJng0C 
Hold down ALT+F8 to open up the Macro window. Select PERSONAL.XLSB!HighlightDuplicateLinks and click “Run”. 
VBlZtS6pKLs3w4HI8BfGPv52crwGL31NVli-MeGW 
A new window titled “Look for Duplicates” will open. Click OK. 
JQR3MTMH2RzZ2LZTlmKnCxSzdqzJArACyZPc9wRv 
Once script is completed, (a few seconds depending on the amount of URL’s), click OK. 
oyunSGZa4EMmY3mWu3bJZvWNyHITzc718gpNVa_D 
If you have sites that you outreached that did indeed link back to your client, they will appear at the top of the list in yellow.  
-U2yGpQNSFbBMozRi1vsaHQu10uoB6I9pSf0E6QR 
Your work is done and you can easily see if you have any additional links to report to your client. 
Anytime you have a list of URL’s that you want to check, simply type ALT+F8 and click Run on the selected macro. 
Copy the code below: 
Sub HighlightDuplicateLinks()

'Takes URL's in Column A, sorts and highlights duplicate values.

Dim dialogBox
Dim finishBox

   dialogBox = MsgBox("Look for Duplicate Values in Column A?" & _
   vbNewLine & vbNewLine & "Written by Joshua Snow" & _
   vbNewLine & "jsnow.pageonepower@gmail.com", vbOKCancel, "Look for Duplicates")
   
   If dialogBox = vbCancel Then
   Exit Sub
   End If

Dim lastRow As Long
Dim duplicateFound As Long
Dim i As Long

Dim myWB As Workbook
Set myWB = ActiveWorkbook

Dim ws As Worksheet
Set ws = myWB.ActiveSheet

Dim cell As Range

ws.Range("A1").EntireRow.Insert
ws.Range("A1").Value = "URL List"

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To lastRow

If Cells(i, 1) <> "" Then
   duplicateFound = WorksheetFunction.Match(Cells(i, 1), Range _
       ("A1:A" & lastRow), 0)

If i <> duplicateFound Then
   Cells(i, 1).Interior.Color = rgbYellow

End If
End If

Next

   AutoFilterMode = False
   Range("A1").AutoFilter
   
With ws
   
   .AutoFilter.Sort.SortFields.Add(Range _
   ("A:A"), xlSortOnCellColor, xlAscending, xlSortNormal) _
       .SortOnValue.Color = rgbYellow
   .AutoFilter.Sort.Header = xlYes
   .AutoFilter.Sort.MatchCase = False
   .AutoFilter.Sort.Orientation = xlTopToBottom
   .AutoFilter.Sort.SortMethod = xlPinYin
   .AutoFilter.Sort.Apply

   .Columns("A:A").ColumnWidth = 50 'for aesthetics
   .Range("A1").Select
   
End With

   For Each cell In Range("A:A")
       If cell.Interior.Color = Excel.XlRgbColor.rgbYellow Then
           cell.Value = "http://www." & cell.Value
           ws.Hyperlinks.Add Anchor:=cell, Address:=cell.Formula
       End If
   Next cell

finishBox = MsgBox("Finished!" & vbNewLine & vbNewLine _
   & "Duplicate URL's are at the top of the list.", _
   vbOKOnly, "Finished")

   If finishBox = vbokay Then
   Exit Sub
   End If

End Sub

Open in new window

 

While automation is not advised for the majority of link building, certain tasks can be automated and every minute adds up. Integrating customer data, prospecting sites and outreach emails are great candidates for some sort of automation. Even a little automation can save you a ton of time and increase your business' profits. For example, let’s say that you are able to find duplicate sites without using this code in about 2 minutes and that you have ten clients that you check for unreported links every month. Using this code will save you about 20 minutes of work every month or 4 hours a year. Now let’s assume that an average employer cost for link building is $15 an hour. Using this code you could save your employer $60 a year. Think big and imagine that same employer has 50 link builders all performing the same task each month. This code will now save your employer $3,000 a year. There is always room for a little automation to save time and money, even in the world of high quality organic link building.
1
Comment
Author:Josh Snow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month