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

http://www.excelguru.ca/blog/category/the-ribbon/  

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

Web Service Toolkit – http://www.microsoft.com/downloads/info.aspx?na=22&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3dfa36018a-e1cf-48a3-9b35-169d819ecf18%26DisplayLang%3den

Advertisements