SEOmoz Mozscape Add-In For Excel

by Jason Green on September 5, 2012 · 36 comments

Powered by Mozscape

With the updates just added to the Mozscape API (previously Linkscape), I decided to update the Excel Macro and turn it into a full-fledged Excel Add-In.

Features:
Select any number of cells containing URL’s.
Right-click and select “Run Mozscape on Selection”
Enter your Access ID and Secret Key (aka Private Key) from the api page.
http://www.seomoz.org/api/keys
A big improvement is that you only need to do this once. :-)
Mozscape API Credentials
If your credentials are accepted, a new sheet named “Mozscape” will be created and all url-metrics will be added for your chosen URL’s.

Warnings:
If you run this twice, or if you already have a sheet named “Mozscape” it will be deleted and replaced. Rename it before running again if you want to keep the data.
The free Mozscape API is limited to 1 request every 10 seconds.
100 URL’s would take about 17 minutes. 1,000 would take 3 hours.

Full details will be posted soon. For now, I’m just looking for a few beta testers. ***Please bear with me as this is very much a draft version. There are many features I intend to add, but since the latest release of the Mozscape API broke our previous macro, I’m releasing this untested in case anyone really needs the data. Your support and patience is very much appreciated.

[Update 9/8/2012 : If you're not seeing any data, it's because of a change to the Mozscape API code. They now require that we explicitly list which url-metrics we want to return by using bit flags. I'm expecting to release a new version today that will fix this as well as a few of the other bugs we've seen. More info here: http://apiwiki.seomoz.org/deprecated#default-metrics ]

[Update 9/10/2012 : We've updated the code to use bit flags, fixing the previous issue. Individual bit flag selection has been added as a todo item, as it's best to only retrieve the minimum amount of information you need.
Automatically trims access id and secret key to remove extra spaces.
Installs prerequisite software, such as .NET 4.0 and VSTO Runtime. ]

*** Download the latest version here. ***
[[ This version has only been verified to work with Excel 2010 on a computer running Windows 7. If you get it to work on any other setup, please let us know. ]]
Mozscape Add-in : Beta 1 (9/4/2012)
MozscapeAdd-in : Beta 2 (9/10/2012)
MozscapeAdd-in : Beta 2.2 (9/10/2012)
MozscapeAdd-in : Beta 2.3 (9/11/2012)
*****************************************

Please post any questions or suggestions in the comments below. To receive a notice of each release, enter your name and email in the box below. Thanks again for your support!

{ 34 comments… read them below or add one }

Aron September 5, 2012 at 8:33 pm

Hi,

It works for me now, there was a space at the end of the secret key that was causing a problem:) I was wondering if there is a way to adjust the timing between calls to be just over 5 seconds. In your other program, I had adjusted the “sleep” command. The time between calls for someone that has an account with seomoz is 5 seconds, instead of 10 seconds.

Thanks

Reply

Jason Green September 5, 2012 at 9:10 pm

Excellent! We had that problem with the extra spaces on the macro version too. Glad you found the problem and thank you so much for sticking with it. I think I’ll add a trim function to those fields to prevent this from happening again.

As for the rate limiting, yes, I’m aware that paid moz members get a faster limit. It’s just a matter of changing the sleep function from 10 down to 5 seconds. I’m working on a way to have this be user-configurable. For anyone paying the $500 for full API access, they can get up to 200 requests per second!

Since you’ve been so helpful, I’ll see if I can publish a new version with a 5-second rate limit just for my VIP beta testers. :-)

Reply

Jeremy September 7, 2012 at 3:36 pm

Hello,

Just gave this a try and it doesn’t pull any data. Installs fine and accepts my credentials.

Thanks for the great tool!

Reply

Jason Green September 7, 2012 at 4:01 pm

Jeremy, Thanks for posting. I’m actually having the same problem as well. I’m checking out the code now and will post an update asap. Please subscribe to the update list, or check back often for the new version.

Reply

Jeremy September 7, 2012 at 10:09 pm

Thanks! Will do.

I’ve been playing around with a couple other tool that use SEOMoz data and they don’t work either so maybe it is something on their end. I know they were down yesterday.

It’s weird though, if I make direct queries through my API I get data back but when trying to use tools designed to pull the data no luck.

Reply

David September 7, 2012 at 6:11 pm

I ran setup, rebooted as needed, launched Excel 2010, but the following message popped up:

The following microsoft office solution cannot be loaded because a compatible version of the .NET Framework is not installed: MozscapeAddin.vsto. For more information see (and there’s a URL to a page on go.microsoft.com)

I can see the .vsto file in the unzipped folder.

Also, I figured I’d run setup again, but the exact same message popped up.

David

Reply

Jason Green September 8, 2012 at 9:23 am

Thanks David. I’ll be republishing this weekend with the VSTO / .NET code built into the setup process. Sorry for the inconvenience, and thanks for helping work out the bugs.

Reply

David September 10, 2012 at 1:03 pm

Hey Jason, just downloaded, ran setup, rebooted; setup continued after reboot then I got the exact same error message I reported above (boo!). I’m using Excel 2010 version 14.0.6123.5001 32-bit, if that’s helpful to know.

David

Reply

Jason Green September 10, 2012 at 4:18 pm

Thanks for trying again David. I think I got it now. It had the VSTO runtime, but was missing the Primary Interop Assemblies. That should be the last thing we’re missing. (Unfortunately, this is difficult for me to replicate because all of my PC’s are setup for development.) I really appreciate your feedback and patience while I work out these bugs.

Reply

David September 10, 2012 at 8:13 pm

Hey Jason – I downloaded the 2.2 version, unzipped and ran the setup file and got the same error message, this time right after the setup initialized. Looking forward to 2.3? :)

Jason Green September 10, 2012 at 9:41 pm

David. I was able to replicate the error on a Win 7 pc with Excel 2010. Working on which version of the .NET framework I need it to install. Could be a day of installing/uninstalling ahead of me. I’ll refrain from publishing new versions until I’m sure it will work for you. 2.3 will be the one. :-)

David September 11, 2012 at 11:25 am

Thank you Jason, I’m looking forward to version 2.3 – I forgot to mention I’m running Windows XP, if that’s helpful.

David

Jason Green September 11, 2012 at 5:26 pm

David. Beta 2.3 has arrived!
I updated the type of .NET package it installed and got it to work on the Windows 7 PC. (Installed and verified by my wife.) If you still get the error, it’ll have to be because of XP, but let’s keep those fingers crossed that it just works. :-)

David September 12, 2012 at 1:06 pm

Hey Jason, I downloaded, unzipped, ran setup which ran this time then provided a new error message different than previous (let me know if you want me to email a screenshot) that says

Microsoft Office Customization Installer
Installing Office customization
Name: MozscapeAddin
From: (path on my computer to the .vsto file)
(red circle with X and this message): Customized functionality in this application will not work because the certificate used to sign the deployment manifest for MozscapteAddin or its location is not trusted. Contact your administrator for further assistance.

Note that I’m not on a network or shared environment. Pllleeaaase let this not be an XP thing, I really want to use this plugin (as you can tell! :) )

Thanks for your updates! Hopefully this is an easy fix? Fingers crossed…

David

Reply

Jason Green September 12, 2012 at 3:24 pm

Progress. :-)
Go to your Excel Options under “File”.
Choose “Trust Center”.
Click the “Trust Center Settings” button.
Choose “Add-ins”.
There should be a check box for “Require Application Add-ins to be signed by Trusted Publisher”.
Uncheck that box and click ok, ok.
Close Excel.
Try to install again.

If all goes well, that should be the last error! Fingers crossed again. :-)

Reply

David September 12, 2012 at 6:24 pm

Love progress!

I went there and that box was already unchecked. I tried setup again just because; fail. Tried checking the box; fail – unchecked again; fail again (I did close Excel each time for running setup again).

So then I examined the last part of the error message “or it’s location may not be trusted” so I added the path to my downloaded unizipped beta2-3 file location, and that failed. Then I examined the existing file locations and put the unzipped file into one of those locations (one I found said “addins”) but that also failed.

I’m willing to keep trying! :) Any other suggestions? (please please please!)

Thanks!
David

David September 12, 2012 at 6:40 pm

PS: here’s the error details, hopefully helpful – many fingers crossed! :)

Name: MozscapeAddin
From: file:///C:/DOWNLOAD/MozscapeAdd-in_20120911_Beta2-3/MozscapeAddin.vsto

************** Exception Text **************
System.Security.SecurityException: Customized functionality in this application will not work because the certificate used to sign the deployment manifest for MozscapeAddin or its location is not trusted. Contact your administrator for further assistance.
at Microsoft.VisualStudio.Tools.Applications.Deployment.ClickOnceAddInTrustEvaluator.VerifyTrustPromptKeyInternal(ClickOnceTrustPromptKeyValue promptKeyValue, DeploymentSignatureInformation signatureInformation, String productName)
at Microsoft.VisualStudio.Tools.Applications.Deployment.ClickOnceAddInTrustEvaluator.VerifyTrustUsingPromptKey(Uri manifest, DeploymentSignatureInformation signatureInformation, String productName)
at Microsoft.VisualStudio.Tools.Applications.Deployment.ClickOnceAddInDeploymentManager.VerifySecurity(ActivationContext context, Uri manifest, AddInInstallationStatus installState)
at Microsoft.VisualStudio.Tools.Applications.Deployment.ClickOnceAddInDeploymentManager.InstallAddIn()
The Zone of the assembly that failed was:
MyComputer

Jason Green September 10, 2012 at 10:24 am

Beta 2 has been published.
Utilizes bit flags.
Trims access ID and secret key to remove spaces.
Installs prerequisite software. (.NET 4.0 and VSTO Runtime)

Please download the latest version and let us know how it goes.

Thank you,

Jason Green

Reply

Roel B. September 13, 2012 at 9:04 am

The newest add-in also seems to work on Excel 2007, but only for a small selection. When I select more than 10 URL’s, the add-in returns a prompt stating that a 503 server error response occurred.

Do you think that this is due to the version of Excel?

Anyways, I think you’re doing a great job and I think the tool kicks some serious butt.

Best,

Roel

Reply

Jason Green September 13, 2012 at 9:38 am

Roel, That’s good to hear, and thanks for the encouragement! :-)

I intend to publish a fully working solution for Excel 2007, but wanted to work out the major issues in one system first. We should be there pretty soon.

Reply

David Portney January 18, 2013 at 7:21 pm

Hey Roel,

That worked! It installed just fine.

And even better, IT’S WORKING!

Thanks for your help, and big thanks to Jason for making this plugin!

David

Reply

Roel B. September 17, 2012 at 5:33 am

For me, the add-in works better when running Excel 2007 rather than Excel 2010.

When I try to install it on a machine running Excel 2010, I run into the same error messages as David.

In Excel 2007, the add-in is stable when checking no more than 20 URL’s. I can only run the process once, when I want to check another batch of domains, I have to exit Excel (end the entire process).

Reply

Roel B. September 24, 2012 at 3:38 am

For me, the newest add-in works perfectly with a Windows7/Excel2007 combination. The only thing is that I can run it only once per process, so I have to restart EXCEL.EXE when I want to run another selection.

Thanks again for an amazing tool!

Reply

Charlotte November 13, 2012 at 7:14 am

Hi Jason,

Thanks for the incredibly useful tool! I just have one suggestion – at the moment if the code can’t find one of the sites in a list it halts the whole thing with an error message. This makes it difficult to run long lists of sites, as I then have to reset and start again.

If the code could mark the site as an error and then carry on down the list, that’d save tons of time!

Many thanks,
Charlotte.

Reply

Aron November 16, 2012 at 10:25 pm

Hi Jason,

Any word on the ability to change the sleep function to 5 seconds for your testers?:)

Reply

Paul December 22, 2012 at 5:37 pm

Wondering if anyone else has had this issue. Windows 7 with Excel 2010 and everything installs good, but when I right click there isn’t anything there to click on. Just the standard options appear. Any ideas?

Thanks!

Reply

Stephen January 5, 2013 at 12:32 am

I am trying to run the Add-In:

Install was fine but when I right click and run “MozScape on Selection” nothing happens.

Help is much appreciated. I have my Access ID and secret key ready for next step but there is no option for it.

Reply

Roel January 7, 2013 at 12:17 pm

Hi David,

Don’t know if you’re still looking for a solution, but I managed to install the plugin correctly. I experienced the same problems you describe, but I solved it by ‘unblocking’ the files.

When you download the addin, unzip all files into a new directory. Then, one by one, rightclick on the files and go to ‘Properties’ and click the ‘Unblock’ button (see this image: http://hypftier.de/dump/properties_unblock.png).

Do so for all the files, also in the Application Files directory. Then run the Setup file. Hope it works for you too!

Reply

Olli January 18, 2013 at 10:33 am

Hi there. Thanks so much for this. Only thing that was unclear is, that Excel loads the plugin from the unzipped folder every time – even though I ran setup. Maybe you can mention this in the readme.txt or a FAQ, since I would have put the folder initially into another location prior to installing it ;) Otherwise: It won’t request my API-and secret-stuff, sadly. Until I figured out why this is, I can’t give it a try. Greets, Olli

Reply

Joe August 8, 2013 at 6:12 pm

Is this working for 2013?

Reply

Jason Green September 13, 2012 at 9:37 am

David, I found a possible solution, but for now, we’ll refer to it as a hack. :-)
http://msdn.microsoft.com/en-us/library/bb772070.aspx

If you’re comfortable editing registry values, you can follow the instructions int the post above. Otherwise, I’ll build a small console application that will update the values programmatically. This would still be considered a hack until I can figure out how to implement this into the installer and/or buy a digital certificate.

***I leave for vacation tomorrow, so let’s hope this works!***

Reply

David September 13, 2012 at 6:42 pm

Hey Jason – I’ll review info at that link and see if it’s over my head or not – I’m going on vacation this Saturday to the following week (back in office on 24th) so if you’re out a week too, that’s a good match, eh? – have a fun vacation!

David

Reply

David September 13, 2012 at 7:43 pm

Me again. :) Tried the hack, pretty sure I did it correctly, rebooted, ran setup, same error message.

I’ve come too far to give up! :) – I’m on the email update list and would love to install so please feel free to email me and/or I’ll keep watch on this page, eh?

Thanks for all your time (and hand-holding) looking forward to using this obviously-great tool!

David

Reply

David October 23, 2012 at 7:18 pm

Hey Jason, just realized I forgot to check back here after my vacation a couple weeks ago – is there an updated version I should be trying?

Thanks!
David

Reply

Leave a Comment

{ 2 trackbacks }

Previous post:

Next post: