Automate Excel

Google Does Spreadsheets

Amit has some Google Spreadsheets commentary.

Create basic spreadsheets from scratch.
You can start from scratch and do all the basics, including changing the number format, sorting by columns, and adding formulas.

Upload your spreadsheet files.
Upload spreadsheets or worksheets from CSV or XLS format - all your formulas and formatting will come across intact.

Familiar desktop feel makes editing a breeze.
Just click the toolbar buttons to bold, underline, change the font, change the cell background color and more…

Awesome! The first remix of one of my spreadsheets: Backlink Checker for Google and Yahoo!

Claus has taken my Google API Keyword Spreadsheet, gave it a facelift, and added Yahoo backlink results side-by-side with the Google results.

For a large screenshot click here, or view the top ten results for the search Automate Excel with their Google and Yahoo backlinks in this screenshot:

The remixed version is the result of the nice folks at ResearchBuzz posting a link which was then picked up by ThreadWatch, who requested to see a Yahoo version also. Cluas then ran with it.

Note 1: A Yahoo developer key is not required. Notice in the large screenshot I entered foobar for the Yahoo API key and got the same results. I believe they are still throttling 5000 queries/day by IP (Thank You Yahoo).

Note 2: The VBA code requires a password, so any curious coders are out of luck, Claus? (You can download the original to see the google query. Not that a vba password has stopped anybody before

Note 3: If any SEO gurus care to do a follow up post on how to use this data in keyword research, that would be great!

Note 4: The spreadsheets I make available for download are free to build upon provided a link and proper credit is given, and they’re not sold. Any questions, simply shoot me an email.

Note 5: Woops, getting carried away with the notes… Nice job Claus.

TrimSpreadsheet is a “lightweight open-source JavaScript spreadsheet engine”.

You can read the details and find the download links here.

NumSum is a new community for creating online spreadsheets, tagging and sharing them.

I created a test spreadsheet you can view here.

It was rather simple to add a link, some calculating cells, and an image.

The interface mimics Excel pretty well. I did get a “There was a global error processing your request.” error every time I saved a spreadsheet, however the spreadsheet was saved anyway (no problem, just a quick scare

NumSum was created by Metaha, who has a blog I just added to my feedreader, they discuss NumSum in this post.

Via LifeHacker. Here’s a screen grab:

Update: This is an outdated post. If you’re looking for an ajax spreadsheet, Amit has a rundown of the new Google Spreadsheet and also lists some of the top ajax spreadsheets around the web.

If you take XML, Javascript, and the XMLHttpRequest object, then mix them up a bit, out comes an AJAX Application. The term AJAX has created quite a bit of buzz lately, I won’t try to explain the details (wrong person to do that ;-), however if you’re interested in reading more check out some of these tutorials and apps.

Now what does that have to do with Excel? Check out this Ajax Spreadsheet. It mimics the basic functionality of local spreadsheet, and even has some calculating fields (ie. if you change one cell, multiple dependent cells values will update).

And while you’re there, make sure to click and drag the column headings, very cool.

Also, it’s released with a GPL license. I’ve hacked together a local working copy for future experimentation.

So you’re building a spreadsheet that requires the user to enter a ZipCode, City, and State… How do you build a smarter spreadsheet that automatically fills in the City and State after the user inputs only the ZipCode?

I’ve been looking at some basic tutorials that web devolopers are using with XMLHttpRequest, like this one, and was inspired to recreate the same functionality of this excellent XMLHttpRequest tutorial in Excel (scroll down the link for a demo).

Success! I’ve put together a quick flash movie to demonstrate keying a zip and automatically returning the city and state in Excel.

And here is the spreadsheet download to kick around (there isn’t that much code).

How it works
1. You need a database that has the zipcode/state/city information in it. Get that here (I went with MYSQL as demonstrated).

2. Return the city and state based on a zip code query from the Database, I used PHP to return text.

3. Back in Excel: When the ZipCode field changes, the Worksheet_Change event fires, sending the request via WinHTTP to the PHP page, which returns the City and State for the Request back to Excel.

4. I use the Split() function to parse the string and write the results to the spreadsheet.

Notes
Why not return XML?
It is only a two word string, and text is easy to deal with when there are only two words. XML may have been snazzier however because I could have just mapped the two result fields to cells, and called a custom Import URL: each time.

Why not just connect to the DB directly?
Yep that’s possible, however I’d prefer to create a “service”, this way any app I make, on any platform, can use it.

Why not just import the Zips to Excel and do a lookup?
Possible, however if multiple workbooks use the table, a change needs to be made to all of them.

The Zips in the example may be outdated.

Only tested in XL2003.