Macro

Update: Version 2.0 of the Excel Interface to the seoMoz API

by Jason Green on September 22, 2011 · 6 comments

I’m very happy to announce an updated version of our Excel Interface to the seoMoz LinkScape API. Your suggestions have been heeded and the new version is a great improvement over the first.

What’s new?

  • Run On-Demand: No more auto-updating. Now, just press Alt+F8 to run when YOU want.
  • Rate Limiter: Helps to keep us out of the seoMoz doghouse. Allows 2 URLs to be processed per second.
  • Cleaner Spreadsheet: Removed the “raw” seoMoz response and handles all parsing within the macro.
  • Conditional Formatting: Automatically compares scores against all others on the sheet. Highlighting opportunities FTW!
  • Stand-Alone Macro: Save the macro to your personal workbook and you can run it against any list of URLs. No need to use *this* spreadsheet every time. (minor tweaks required)

BusinessHut seoMoz LinkScape API Excel Spreadsheet

– Download Version 2.0 of the Macro-Enabled Excel Workbook –
– Download the Text-Only Version of the Macro –

Prior supporting information is available here: Excel Interface to the seoMoz API

— Make it more awesome with this post by John Doherty —
— And for even more info, check out John’s post on SEOmoz —

********* UPDATE 12/13/2011 **********
Rolling out December, 16th, 2011, the SEOmoz Free API will be severely limited and becomes almost unusable for this macro.
The new rate limit for the Free API is 1 request every 10 seconds.
(Compared to 2 or 3 per second that we’ve been running.)
And batch requests for the URL Metrics Call has been limited to 10 URLs for every POST request.
You can read the official SEOmoz API update here.
I’m currently looking into alternatives, and am entertaining the idea of building my own API.
Suggestions and support are welcome. Thank you to everyone who has given feedback thus far.
**************************************

Using the SEOmoz Free API with Excel

by Jason Green on April 19, 2011 · 59 comments

Powered by Linkscape


At one time or another, we’ve all come across a “juicy” list of directories, blogs, or other websites that we might be able to use for some link building. I recently came across such a list and was quickly annoyed at how low the quality of some of the sites was. I wanted a quick and dirty way to rate all of the sites so I could start my linkbuilding process with the most promising websites. That’s when I found the SEOmoz Free API. With my mad Excel skills and lots of help from Leith Ross with some Excel code, I was able to create what I think is the fastest and easiest implementation of the SEOmoz API out there. It’s also easy to customize if you want to hack it into something that suits your needs.

This is a self-service spreadsheet, so you’ll need to have your own API credentials. (Get credentials here) If there’s enough demand, I’d be happy to publish a more polished version for mass consumption. (leave a comment)

I wrote a custom function in Excel that queries the SEOmoz API URL with the various parameters pulled from the spreadsheet. You just need to download the spreadsheet and insert your credentials, and the function should start pulling data. (Press Ctrl+Alt+F9 to refresh formulas.)Press Alt+F8 and run the GetURLMetrics macro to begin processing. Be patient with large lists of URL’s. Currently, it takes about a half-second per URL to process.

To follow along, download the latest Excel macro-enabled spreadsheet below: (Enable macros after opening.)
Excel Spreadsheet (Beta1) 4/19/2011 (Original Version)
Excel Spreadsheet (Beta 2) 9/22/2011


View the Updates in Version 2

The function, named GetURLMetrics, takes a specified URL and whether or not to follow redirects as parameters. [eg. =GetURLmetrics("google.com",TRUE) ]

It returns the entire response of the Linkscape url-metrics API call, and the cells to the right parse that into usable fields. If you’re looking for a way to assess many websites quickly, I think this will work for most applications.

This is very much a beta version and I appreciate anyone giving it a try. It worked fine for me, but others have not had the same success. Your questions and suggestions are very much appreciated.

*** Update: If you receive an “invalid signature” error, please refresh the API page on SEOmoz to get new credentials. Make sure to re-copy all of the information needed, and that you don’t accidentally copy line breaks or other hidden characters. It takes a few tries for some, but usually works.

*** Update: Mac users: If anyone was able to get this working on their Mac, or with OpenOffice on any platform, we’d love to hear from you. Several users have tried, but weren’t able to get it working. If you figured it out, please let us know.

Download the spreadsheet here.
(right-click and Save Target As)
The macro/function is saved within and can be viewed by pressing Alt+F11 once in Excel.
Press Alt_F8 to Run.

You can download the text of the macro below. You’ll need all of the included functions for everything to work properly. To run as a stand alone macro, just insert your credentials directly into the code and uncomment the input boxes. (There are notes included in the macro.)

– Download Version 2.0 of the Macro-Enabled Excel Workbook –
– Download the Text-Only Version of the Macro –



********* UPDATE 12/13/2011 **********
Rolling out December, 16th, 2011, the SEOmoz Free API will be severely limited and becomes almost unusable for this macro.
The new rate limit for the Free API is 1 request every 10 seconds.
(Compared to 2 or 3 per second that we’ve been running.)
And batch requests for the URL Metrics Call has been limited to 10 URLs for every POST request.
You can read the official SEOmoz API update here.
I’m currently looking into alternatives, and am entertaining the idea of building my own API.
Suggestions and support are welcome. Thank you to everyone who has given feedback thus far.
**************************************

Excel Macro to Generate SQL Insert

by Jason Green on August 28, 2010 · 6 comments

I am in the process of creating a database of County information, and I needed to insert several thousand records. I was able to get the data into Excel, but now I needed to write an SQL Insert statement to get the records into my database. SQL wizards, I’m sure you know a more efficient way to write this INSERT. Actually, what is generated is a series of INSERT statements. If you’d like to share your knowledge, please leave a comment, and I’d gladly update the macro for your improved format.

How it works:

Let’s start by looking at my spreadsheet:

  • I’m using Row 1 as my header.
  • The values are: state, county, statefips, countyfips, and fipsclass
  • Beginning on row 2 I list that information.
  • Columns A, B and E are text.
  • Columns C and D are Integers. ***Note this in the macro when modifying.***


Upon running the macro, it will ask for the start row. Enter the number for the row that begins your data.

It then loops through columns A through E and puts those values into variables.

Then, it writes the insert statement, with our variables included, to the file specified by the “MyFile” variable. Currently, it will be written to the active/default Excel directory.

The macro will then loop down all of the rows until it finds a blank.

This macro is fairly short, so I’m copying it below.
I just realized that there are several things I could change to optimize this code and make it more useful. I’ll try to get an update out soon. Until then, what would YOU change in this code?

Sub generateInsert()

Dim state As String
Dim county As String
Dim statefips As Integer
Dim countyfips As Integer
Dim fipsclass As String
Dim myRow As Integer
Dim myCol As Integer

myRow = InputBox(“What row to START on?”)
myCol = 1
MyFile = “insertStmt.txt”

‘set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum

Do Until ActiveSheet.Cells(myRow, myCol) = “” ‘Loop until you find a blank.

state = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
county = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
statefips = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
countyfips = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
fipsclass = ActiveSheet.Cells(myRow, myCol)

myCol = 1 ‘ Return to column A
myRow = myRow + 1 ‘ Move down 1 row

Print #fnum, “insert into countyTable (state, county, statefips, countyfips, fipsclass)”
Print #fnum, “values (‘” & state & “‘, ‘” & county & “‘,” & statefips & “, ” & countyfips & “, ‘” & fipsclass & “‘);”

Loop

‘ Close the file
Close #fnum

End Sub


I’m looking forward to your comments.

- Jason

Outlook Attachment Reminder Macro

by Jason Green on August 27, 2010 · 9 comments

How many times have you written an email about “the attached file” only to forget to attach the file before hitting send? If you follow the instructions in this post, that’ll never happen again!

We’re going to add a VBA macro to Microsoft Outlook that searches your email for the text “attach” when you click “send”. If it finds that string of text (ie. attached, attachment…) it will check to see if you actually attached a file. If there’s no file attached, you’ll be prompted with a message box. Then, you can either continue without attaching anything, or you can stop the email from sending so you can include your file. Yes, it’s that simple.

*Note: This will not work in Outlook Express as it doesn’t support macros.


How to set it up:

1. Open Outlook.
2. Press Alt+F11 : This will open the Visual Basic editor.
3. Expand the project until you find “ThisOutlookSession” and select it.
4. Copy the code below into the Visual Basic code window.
5. Save.

Now to test:
6. Close & Reopen Outlook for good measure.
7. Write an email containing the word attach.
8. Click send. *This is when you should get the pop-up.

*Note: If you have an image in your email signature, that would count as an attachment. Just change “intStandardAttachCount” from 0 to 1.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
‘ Pops up a reminder if the word “attach” is found but there is no attachment on your email.
Dim m As Variant
Dim strBody As String
Dim intIn As Long
Dim intAttachCount As Integer, intStandardAttachCount As Integer

On Error GoTo handleError

‘Edit the following line if you have a signature on your email that includes images or other files. Make intStandardAttachCount equal the number of files in your signature.
intStandardAttachCount = 0

strBody = LCase(Item.Body)

intIn = InStr(1, strBody, “original message”)

If intIn = 0 Then intIn = Len(strBody)

intIn = InStr(1, Left(strBody, intIn), “attach”)

intAttachCount = Item.Attachments.Count

If intIn > 0 And intAttachCount <= intStandardAttachCount Then

m = MsgBox(“It appears that you mean to send an attachment,” & vbCrLf & “but there is no attachment to this message.” & vbCrLf & vbCrLf & “Do you still want to send?”, vbQuestion + vbYesNo + vbMsgBoxSetForeground)

If m = vbNo Then Cancel = True

End If

handleError:

If Err.Number <> 0 Then
MsgBox “Outlook Attachment Reminder Error: ” & Err.Description, vbExclamation, “Outlook Attachment Reminder Error”
End If

End Sub

Also, LifeHacker has a script for GreaseMonkey that does the same thing for GMail.

Credit for this Outlook macro goes to Mark Bird. I’ve seen this macro a few times on the internets, but I believe he’s the original creator. You may also find some answers to any questions on his site. Thanks Mark!


There you go! Now anytime you mention an attachment in your email, you’ll be sure to have it attached.

Excel Macro Spreadsheet to HTML

by Jason Green on August 27, 2010 · 0 comments

So I have a customer that needed a registration page for their E-University. This consisted of about 500 courses in various categories.

I got the ~great idea to implement an AJAX Accordion to contain the categories. Within each accordion pane, I needed separate tables to house the various sub-categories. So how do I get a list of courses, with course codes and hyperlinks into a properly formatted accordion control?

I thought about doing this manually (for about 1 second), then I decided to try writing a macro. I don’t have a ton of macro or VB experience, but this was the only thing I could think of.

Enter the reason for this post…

I searched all over the Internet for an easy way to export Excel to HTML. There were a few examples, but I found them really hard to follow, and most were too involved for my needs. (I thought)
Note: The material presented here was learned through trial and error. I am not a macro, VB, or VBA expert. I know there is a better way to go about this. Unfortunately, I couldn’t easily find it online, so I hacked this together. If you are an expert, I would love for you to comment so we can have an optimized version of the code we should be using. In the end, this code worked great for me. You might not have the same results, so use at your own risk.


Please download the code for the macro here: http://www.businesshut.com/Excel_To_HTML_Macros.txt
Download the sample spreadsheet here: http://www.businesshut.com/E-University_Macros.xls
The output text file will be similar to this: http://www.businesshut.com/ExcelToHTML.txt
The resulting .aspx file will be made available soon.
I recommend following along with the example files.

Now, for a tour of the sub-routines…

First, you’ll see “getHyperlinks”

In column C, the Course#’s are all hyperlinks. However, I needed the hyperlink separated from the Course# in my html.

getHyperlinks starts in cell C4. (you’ll change this per your situation.)
We have a “Do Until” loop that will iterate for the number of rows you enter here. If you enter more rows than you need, the loop will simply write blank values to the cells in column E.
If a hyperlink is NOT found in the cell, we use a blank string.
If a hyperlink IS found, we use Selection.Hyperlinks(1).Address to pull out the URL.

We then use Selection.Offset(0,2).Select to move 0 cells down and 2 cells right, and we write the URL there.

Add 1 to i and repeat until we get to 400.

Great, now I have all of the URLs as text in their own column.

On to exporting this information as HTML…

Unfortunately, much of this code was pulled from random sources on the web, so I can’t even explain how every line works. However, between this post, and the inline comments, you should be able to figure out how to modify it for your own uses.

Line 36: The “MyFile” variable dictates where the resulting text file will be saved.

We create “fnum” as a FreeFile(), and we’ll use
Print #fnum, “string text”
to write each line to the file specified in the MyFile variable

Line 60: myRow was hard coded to 2 because that’s where my data began. You may need to change this.

Now we start creating the accordion panes.
***Note: This macro only creates the accordion PANES. Create an accordion first, (another topic), and just use the code generated by the macro as your accordion panes. ***

Line 63: We get the “courseGroup” by pulling the value from the row specified by “myRow” and column 1 of the current sheet.
*One last time I’ll mention that when I specify cells, that you may need to change them per the format of your spreadsheet.

Line 66: Now we start writing to the file.
This section creates the header information for the AccordionPane.
Everything in quotes is added literally, and the variables are inserted where necessary.

Line 74: Add 1 to myRow to move down to the Course Category and pull that value into our code.
This section labels this part within the AccordionPane as a new category and starts the table that will contain the course information.

Line 91: This is the main loop. (1000 is an arbitrary number. However, it is referenced later in the loop.)
Now that we have the AccordionPane and the category information, we’ll start looping through the courses.
Back up on line 48, we set cl = 1
This was done to help me group various controls and reference them easily. I was having trouble using “FindControl” dynamically with a MasterPage and an AJAX Accordion control, so this was my solution. I might have been able to use a Checkbox Array, but I couldn’t get the formatting I wanted. Each group of controls is simply named with the same number on the end. So if someone clicks “chk1″, which is the checkbox to select the course, I know that “lblchk1″ will contain the corresponding information for that course. Yes, crude but effective.

Line 125: (By “blank row” I mean that column B is blank.)
If we have 1 blank row, it indicates a new category.
If we have 2 in a row, it indicates a new Accordion Pane.
And if we have 3 blank rows, we’re all done.

:: 1 blank row::
Line 129 skips down another row and checks to see if there’s a second blank row, but we’ll assume it’s not.
Jump to the Else on line 182.
We step back a row to get the information that we skipped while checking the next row.
Line 184 ends the table for the previous category.
Line 187 gets the new category info.
Lines 188-199 set up the title and table for the new category.

Line 207 : Loop again from Line 91 until we hit 1000 iterations.

::If we find a second blank row in the IF statement on line 129::
Add another row to myRow and check to see if we have 3 blank rows in a row.
*We’re assuming we only have 2.
Jump to the Else on line 144.
Now we go back 2 rows to get the heading information that we skipped.
Then we end the table for the previous section, and end the Content section of the AccordionPane. We also end the AccordionPane and start the header information of our new one.

Line 157 begins the new AccordionPane, and Line 167 begins the heading and table information for the first category within the new AccordionPane.

EndIf sends us to Line 207 : Loop again from Line 91 until we hit 1000 iterations.

:: 3 blank* rows:: (*Column B is blank.)
Line 135 : End the table for the current category.
Line 138 : End the Content section of the Accordion Pane.
Line 140 : End the Accordion Pane.
Line 143 : Set i to 1000 so we don’t loop again.

Line 213 : Close the file for writing.
End Sub

~~~

Remember when I said I thought the other examples were “too involved” for my needs? I was wrong.
I realize this example is a bit involved, and it’s very specific to my project. If you want to see a simplified example, just send me a comment, and I’d be happy to set one up.

I look forward to everyone’s thoughts on how I went about this solution, and I’m really interested to see how you would have done it.

- Jason Green