自动链接检查器

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. 

好吧，您将获取您已访问的域URL列表，并将其转储到Excel文件中。 然后，您将下载所有引荐域反向链接，并将其添加到您已联系到的URL列表中。

Then type ALT+F8 and hit Enter and your work is done (if you used my code). 

然后键入AL T + F8并按Enter键，您的工作就完成了（如果使用了我的代码）。

 

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. 

多年来，我花了大量时间学习Excel以及如何自动化项目。 如果我有定期执行的任务，则尝试自动执行。 时间就是金钱， 效率是关键 。 当然，某些事情绝不应该自动化。 良好链接构建的一部分涉及与其他站点的关系建立。 与自动化机器人相比，您更有可能建立自己的关系。

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. 

使用此脚本可以简化链接检查并节省一些时间。 该代码将结合您的外联网站列表和客户反向链接，并查看您是否通过外联工作获得了任何链接。 这是通过查找列表中重复的URL来完成的，这表明您延伸到的站点也显示了到客户端的反向链接。

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. 

该代码获取您的URL列表，并添加标题为“ URL List”的标题。 然后，它搜索“ A”列中的所有URL，以查找重复项。 如果找到重复项，则将其移到列表顶部。 然后将重复的URL突出显示为黄色，并转换为超链接以方便使用。

How to get the code into Excel. 

如何将代码导入Excel。

Open up an Excel workbook.

打开一个Excel工作簿。

​Select the “File” tab. 

选择“文件”选项卡。

 

Select “Options” at the bottom of the File menu. 

选择“文件”菜单底部的“选项”。

 

The Excel Options window will appear, select “Customize Ribbon”. 

将出现“ Excel选项”窗口，选择“自定义功能区”。

 

Check the “Developer” box under Main Tabs. 

选中“主要”标签下的“开发人员”框。

 

The Developer tab will now appear in all of your Excel workbooks. 

现在，“开发人员”选项卡将出现在所有Excel工作簿中。

 

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

选择“开发人员”选项卡，然后在最左侧选择“ Visual Basic”。 或者，您可以键入ALT + F11。 Visual Basic编辑器（VBE）将打开一个新窗口。

 

In the Project Tree, right-click VBAProject (PERSONAL.XLSB). 

在项目树中，右键单击VBAProject（PERSONAL.XLSB）。

 

Select Insert, then Module. 

选择插入，然后选择模块。

 

Copy the code in this article and paste into the Module window. 

复制本文中的代码，然后粘贴到“模块”窗口中。

 

Click the “Save” icon or type CTRL+S to save the code into your Personal Workbook.

单击“保存”图标或键入CTRL + S将代码保存到您的个人工作簿中。

Close out the Visual Basic Editor.

关闭Visual Basic编辑器。

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. 

在列“ A”中填充您已扩展的域URL列表。 请注意，这些URL是域级别的。 重要的是，此列表中的所有URL都是域级别的，以便可以找到重复的值。

 

Run a backlink report on the client in question. Type your client URL in the Site Explorer. 

在有问题的客户端上运行反向链接报告。 在站点资源管理器中键入您的客户端URL。

 

Click “Referring domains” in the menu on the left. 

点击左侧菜单中的“引用域”。

 

Click “Export” on the right side. A new window will popup, select the “Microsoft Excel” radio button and click “Start Export”. 

点击右侧的“导出”。 将弹出一个新窗口，选择“ Microsoft Excel”单选按钮，然后单击“开始导出”。

 

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. 

打开您刚刚下载的工作簿。 在“ B”列中，将列出所有“新鲜”域级别的反向链接。 单击“ B2”单元格并按住CTRL + SHIFT + DOWN来选择“ B”列中的所有URL。 然后按住Ctrl + C复制所选的单元格。

 

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. 

现在您已经复制了反向链接，请关闭工作簿。 使用您已经扩展的URL打开Excel工作簿。 选择“ A”列中的第一个空单元格，然后按住CTRL + V粘贴反向链接。

 

Hold down ALT+F8 to open up the Macro window. Select PERSONAL.XLSB!HighlightDuplicateLinks and click “Run”. 

按住ALT + F8可以打开“宏”窗口。 选择PERSONAL.XLSB！HighlightDup licateLink s，然后单击“运行”。

 

A new window titled “Look for Duplicates” will open. Click OK. 

将打开一个名为“查找重复项”的新窗口。 单击确定。

 

Once script is completed, (a few seconds depending on the amount of URL’s), click OK. 

脚本完成后（几秒钟，具体取决于URL的数量），请单击“确定”。

 

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.  

如果您拥有的外展网站确实链接回了您的客户 ，则它们会以黄色显示在列表的顶部。

 

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. 

只要您有要检查的URL列表，只需键入ALT + F8，然后在选定的宏上单击“运行”。

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

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.

虽然对于大多数链接构建而言，不建议使用自动化，但是某些任务可以自动化，并且每分钟加起来。 集成客户数据 ，潜在客户站点和外发电子邮件是某种自动化的理想选择。 甚至一点点自动化都可以为您节省大量时间，并增加业务利润 。 例如，假设您能够在大约2分钟内无需使用此代码的情况下找到重复的站点，并且您每月有十个客户端检查未报告的链接。 使用此代码将每月或每年4个小时为您节省大约20分钟的工作时间。 现在，我们假设雇主平均用于建立链接的费用为每小时15美元。 使用此代码，您每年可以为雇主节省60美元。 放宽思路，想象一下，同一雇主每月有50个链接构建器全部执行相同的任务。 现在，此代码将为您的雇主每年节省3,000美元。 即使在高质量的有机链接构建世界中，总会有一点自动化的余地以节省时间和金钱。

翻译自: https://www.experts-exchange.com/articles/28850/Automated-Link-Checker.html