SEO Tools is a fantastic add on for Excel. It makes everything simpler from running header checks, to checking title tags, to scraping entire websites and directories.
One downfall of SEO Tools is the program it is built into, Excel. When running thousands of formulas, especially something that needs to hit a server, Excel really struggles and often times out and becomes unresponsive. This makes it difficult when working with SEO Tools to header check thousands of pages. Below, I will go through the VBA code that I use to get around this!
What is VBA code?
VBA code allows you to write code into Excel and build Macros. The benefit is that you can handle huge amounts of data and script to solve mundane tasks.
The VBA Loop
Most developers when learning a new scripting language would learn the loop. Basically, the loop is a set of instructions that are continually completed as long as certain conditions are met. In our case, we are repeating the instructions whilst there is still rows of cells available.
The Code
Sub headercheck()
Dim row As Integer
Dim col As Integer
row = 2
col = 2
While Sheets("Errors").Cells(row, 1) <> ""
Sheets("Errors").Cells(row, col) = "=httpstatus(A" & row & ")"
Sheets("Errors").Cells(row, col).Copy
Sheets("Errors").Cells(row, col).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
row = row + 1
Wend
End Sub
Above is the simplest form of VBA code that I use. It’s not perfect, and I’m sure a VB developer would look at it and cry, but it works!
Rather than going through each line and saying what each does, I’ll just do the overview.
The script works on a sheet named “Errors” (if you don’t have a sheet named this, it will break). It starts with the cell in the 1st Column (A) and Row (2) – it checks to see if this cell is empty, and if not, it inserts the httpstatus formula into 2nd Column (B) and Row (2). Next, it copies the result and pastes it as text, therefore removing the formula from the cell. It continues to do the above for each row, until it reaches an empty cell in Column (A).
Where does the code go?
To insert the code, you need to open Visual Basic. This can be done in Excel by going to the Developer Tab, then Visual Basic. From there, use the menu to insert a new module. Paste the above and you’re ready to go!
The Usage
The usage of the above is to loop and check the header of thousands of pages, without excel breaking and timing out. Things I have used it for include:
- Downloading errors from WMT, running a header check to make sure they are actual 404s. Creating redirect file from broken URLs
- Running each URL as listed in a sitemap
- Running URLs that received traffic in Google Analytics from >1-2yrs previous, making sure these are 200 OKs.