Excel 2007 - talking to a web service

Question

I've been using Excel 2003's 'Web Services Toolkit' for some time to write spreadsheets that connect to external web services via VBA.

(ie you install the Web Services Toolkit which adds a 'Web Service References...' item to the 'Tools' menu. You then set up a reference to an Web Service URL and the Toolkit writes the necessary proxy classes in VBA to do the connecting for you. The web service methods then appear in VBA.)

Excel 2007 doesn't have this 'Web Services Toolkit' - or at least I can't find anything similar. I note that my Excel 2003 sheets work fine in 2007 so the necessary hooks are in there somewhere.

Which brings me to my question - what is the approved way of talking to an external web services in Excel 2007? I can't find anything useful on the web?

Thanks...

SAL

Answer 1

You have several options:

1) Microsoft Office Soap Type Library v3.0 (as mentioned above). Nice and simple, works well in VBA, completely useless because it does not support WS-Security. If you're using SOAP without WS-Security then you're wasting your time.

2) C# lib exposed to Excel using COM Interop. It's relatively simple to expose the assembly for use in VBA - permits the use of WSE 3.0/WCF in VBA. Assembly must have strong naming.

3) Visual Studio templates. These templates integrate C# (or any other CLR language) inside the spreadsheet as an assembly. Best form of integration, allowing complete access to .NET framework from Excel (or Word, PowerPoint, etc.). Templates only available in Visual Studio Team Suite though.

If you do add security (even if only HTTPS), you'll need to ensure that you know you're way around MMC and its Certificate snapin. You'll have to add the necessary CAs to your certificate store to ensure .NET doesn't start getting upset.