Automate Excel

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.

Chris Kunicki posts about a new XML toolkit: Excel 2003 XML Toolkit - Get it and you too can be cool.

I kicked it around for only 10 minutes and I think I’ll be keeping it installed.

Why? It’s handy for even a novice XML’r like me. Immediate likes:

• Toolbar Icon to Toggle the XML source pane.

• View Schema makes it easy to generate a schema with a quick copy and paste

• Toolbar Icons for most of the XML functionality: Refresh selected map is nice, Toggle a cells XML borders is interesting(transparent xml).

• XML Range properties (shown in the picture to this post) is very useful. I’ve coded to grab the Xpath string of a mapped cell multiple times (usually because I couldn’t figure it out on my own), this new functionality let’s me display the Xpath and provides a very hand Copy to Clipboard button.

There’s much more to be discovered with the XML toolkit. If you’re learning Excel XML I’d definitely check it out.

Now I just need to find room for the new toolbar.

I just spent some time digging around the site gummy stuff … about Investing (mostly)

Not “officially” an Excel website, but there are loads of Excel spreadsheet examples with the tutorials. For instance, here’s one of the most useful tutorials on consuming Yahoo Stock Data I’ve seen.

(via the Delicious feed of Nathan Slaughter.)

Debra Dalgleish has an Excel site called Contextures.com, which in my opinion has some of the best Excel tutorials on the web.

If you look at the bottom of her homepage you’ll see she now has an RSS feed!

I’d recommend subscribing to keep up to date on when new tutorials become available.

The blogroll is updated (and getting a bit longer

(via the pdbook)

You should be able to get your hands on a Beta copy of Office 12 this fall… Read about it at News.com or Eweek

I just recieved a resourceful email from Mike Hernendez, the VSTO Community Program Manager. I thought I’d pass along the links section of the email for those interested… For the VSTO Links Click Here

It’s very cool to post about recieving VSTO a few days ago and getting a personal email from a Microsoft employee, with some tips on where to go to get started.

It’s still going to be awhile untill I get a solid chance to explore VSTO, however now we know where to start.

Thanks Mike!!!

My 2005 Visual Studio arrived with 3 CD’s: Team Suite, Team Server, and SQL Server 2005, very nice.

During the FULL install of Team Suite the text said “Installing Visual Studio Tools for Microsoft Office”. So what is it? Basically it’s not a seperate IDE or program, it’s just the option to start a new project in Visual Studio as “New Workbook” and you’re programming in VSTO.

There is a toolbox full of controls to drop onto your workbook and program, like TaskPanes, Progress Bars, Treeviews, and the usual controls.

I’m not sure how to program most of them in DotNet yet, but within 10 minutes I had a TaskPane with a picture in it added to the workbook, awesome. The IDE is for sure a step up from VBA, Screenshot.

Just a guess, one major drawback is going to be deployment, or most workbooks developed with VSTO won’t run “stand-alone” on most machines: after a half hour of researching I couldn’t figure out how to run a workbook outsided of the development environment maybe? or maybe?. I didn’t even attempt to run a VSTO workbook on a machine without VSTO.

I’ve started a new category called VSTO/DotNet. Any tinkering I do I’ll post there.

Thanks MS. And two related links:

Common Tasks in Office Programming

VSTO Blog

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.

Research Buzz posted about a cool tool to mashup the Excel RSS feeds called RSSMix

I took their examples and created a mashed feed here.

Very nice, this could definitely be parsed as another interpretation of Dicks Excel Headlines idea. Just dump in all the Excel feeds and out pops the latest and greatest Excel posts.

And I’m now subscribed

Dick posted his Excel headlines idea over here and it looks like it’s starting to get some legs. Here’s an interpretation here, here, and even a french version over here.

From the pdbook I learned that John Peltier has added an RSS feed to his site and so did Andy Pope Over Here.

I also noticed two French Excel blogs here and here. Blogroll is updated!

Sidenote: It’s a shame there is still a language barrier in 2005 with all the technology out there. I’m sure this wasn’t the intented tagline for the XXL site, via the google translation of the page “Some Tricks to Smell Itself Well with Excel”

Update: Misange stopped by and pointed out the correct translation is “Some Tricks to feel more confortable with Excel“. That’s more like it! Thanks.

I was just browsing the article 15 things you can do with RSS and noticed that WunderGround provides an RSS feed for weather (the option is available after doing a search).

Knowing that RSS is consumable with Excel (XML versions), I whipped up a barebones* example of getting the current and daily forecasted weather into Excel.

You can download the Weather Spreadseet here.

Simply enter a City and State and hit the “Get Weather” button. If the inputs are valid, your spreadsheet will return data looking something like this:

Looks like possible rain for the running of the derby today

*This is a barebones example to demonstrate how to get weather into Excel. I included only an “On Error Resume Next” instead of detailed error handling, and the weather is returned as single string (ideally this string would be parsed).

In case you don’t subscribe to the feed, OfficeZealot just uploaded another podcast:

Show #3: Interview with Joe Andreshak of Office Developer Marketing

“In this show we talk about InfoPath, OneNote, SharePoint, XML and the direction of Office.”