Now I’m not much of a VBA person, in fact I hate doing, I avoid it like the plague.
Recently I was asked to knock something up that showed a report of our open calls.
No problem I though just connect to the existing Web Service I will just knock up a simple asp.net 2 page with a grid and away I go. This was easy enough until I wanted it deployed to IIS, and not having played with Studio 2005 that much I just gave up and thought I’ll do it in 2003. Well long story short my machine really could do with a rebuild.
Damn, I’ll just do it in Office 2007 Excel, That’s all XML based now isn’t it !
HA!
For all MS’s talk of web services and connectivity excel can’t natively consume a web service. If I’m wrong show me, please, but I could not find add web reference in the VBA or create a web service data connection.
Well the XP/2003 Office Toolkit came in handy it allows you to connect to a web service, tick the box and it generates a load of VBA code in a handy Class. It doesn’t matter that it’s for 2003 it works in 2007 (did for me).
This worked great, once I figured out how to actually reference a cell in VBA, I had it ripping through a node list and putting the data into cells (its not a big report if it was then this is quite slow).
But there is just something really nasty about having Buttons “IN” the spreadsheet, its shoddy.
So I wondered how I could create a custom menu with buttons on it.
Well in 2007 with the Ribbon bar it could not be simpler.
Take a look at this series of posts by Ken Puls
This allowed me to quickly figure out how to create my own custom interface to execute the evil VBA macros making calls to web services.
Handy Tools :
CustomUIEditor – http://openxmldeveloper.org/archive/2006/05/26/CustomUIeditor.aspx
