Calling a Web Service from Microsoft Office (Excel)

Previous articles demonstrated the sample 'currency conversion' web service in the browser. This was practical, but just to prove we can call web services from anywhere, I provide here an example of it running in Excel. So if you want to get this thing working you will need the following :

• Microsoft Excel running in Windows 98, ME, NT, 2000 or XP
• Microsoft SOAP Toolkit Version 2 or 3. Version 2 is included with Windows XP. So if you don't have XP, or if you want to upgrade, then you should download version 3 from Microsoft... connect to "www.msdn.microsoft.com" and search for "SOAP toolkit"!
• Alternatively if you have Office XP then I recommend you to download the Microsoft XP Office Web Services toolkit (version 2). This includes the SOAP 3, MSXML 4 - in short - the complete works! The link is above, but this time search for "XP Web Services toolkit"

NOTE: Using these tools means you will be able to connect to any web service on the Web, not just the sample web service presented in Web Continuum !

The Microsoft SOAP toolkit is a programmatic API that allows us to call Web Services over HTTP. It deals with all the SOAP messaging details for us, so all we have to worry about is specifying what service/s we want to use and how we want to call it. SOAP 3 implements the latest standards, and deals with the passing of complex data types (beyond the scope of this current article).

So I assume that you have SOAP 2 or SOAP 3 installed on your computer (as directed in the previous section), and that you have Microsoft Excel. So we are going to write ourselves a VBA Excel Macro using the SOAP API to use the currency conversion web service. I stress that using the service inside Excel is just an example.. now that you have the SOAP component, you can use it from what ever environment or language that you like.

If you don't want to setup the spreadsheet yourself then as an alternative download "one I made earlier" from HERE. NOTE. The VBA code is defined as a macro, so make sure Excel's macro security setting is Medium or Low before you attempt the download. Do this in the menu item : Tools->Macro->Security. Also.. This uses SOAP toolkit version 3 so you must change the project references if you have version 2, as in number 2 below.

Now let us begin..

1. Open the Microsoft Visual Basic Editor.. Tools->Macro->Visual Basic Editor
2. We must reference the SOAP toolkit. From the VB Editor, select Tools->References. Scroll down the list of libraries and pick "Microsoft Soap Type Library" (V3 if you have it).
3. Great, we are almost ready to write the code to call our web service. Lets do this from a code module so that it is globally available from all work sheets. Add a code module from menu item : Insert->Module.

Simply copy this code in to your Excel VBA code module.

Option Explicit
' Excel VBA Function wrapper to call currency conversion Web Service on the web!
Public Function ccyConvert(rsCurrIn As String, rsCurrOut As String, ByVal vfAmtIn As Single) As Single
    Dim objSClient As MSSOAPLib30.SoapClient30      ' Remove the 30 if using an earlier version of SOAP
    Dim fResult As Single

    ' Point the SOAP API to the web service that we want to call...
    Set objSClient = New SoapClient30
    Call objSClient.mssoapinit(par_WSDLFile:="http://webcontinuum.net/webservices/ccydemo.wsdl")

    ' Call the web service
    fResult = objSClient.calcExcRate(rsCurrIn, rsCurrOut, vfAmtIn)
    Set objSClient = Nothing

    ccyConvert = fResult
End Function

NOTE: This code is minimal for the sake of clarity.. i.e. No error handling, and no concept of efficiency in the case where multiple calculations are required (see next section).

Now we can use this formulae anywhere we want to in our spreadsheet cells, just like we would use one of Excel's built/in functions. Here is a screen shot..

This Excel example compromises efficiency for the sake of demonstration clarity. In reality we would never want the currency conversion service to be built like this. Imagine if we had 1000's of cells in our spreadsheet all calling this service over the web at the same time. Well it would work, but it would be a hell of a lot slower than doing the calculation locally (orders of magnitude slower). It would be far more sensible in this example if we could simply get the latest exchange rates, and then do the calculation locally using a table of rates. (In fact this then becomes a data extraction problem, rather than one where we need a web service. Tools already exist to import frequently changing indexes into Excel !).

Think again about the poor web service provider. The server might have to handle 1000's of calls to its service for each client. Multiply this by 1000's of possible clients all recalculating simultaneously. The message is that we must think very carefully about how many clients we are expecting, and how intensively they will be using the service. Will the service scale? Might the performance degrade to the extent so as to make the service unusable?

Working in any distributed environment always has its pros and cons. Web services (as with any remote process) are best reserved for tasks that must be done remotely for integration, data, or architectural reasons, and not for trivial and intensive tasks that could be implemented locally.

Often the client might not expect an immediate result from a web service. The service can be run asynchronously of course. In this case a callback function might be invoked or an event fired on the client when the web service results are received. Likewise server architecture could incorporate message queuing to decouple the request interface from the processing. And finally if we include a login to our web service, or if we are charging for its use (per request or via a license), then this naturally limits the potential volume of requests to only those that we plan for.

*NOTE: In case you are thinking of putting 1000's of currency conversion cells in the spreadsheet, please see the note at the bottom of this article.

We can make the task of calling web services from Microsoft Office products even more elegant (compared to the previous example) if we use the Microsoft Office XP web Services toolkit. This allows us to search the www for useful services, and, once we have picked one, it generates the code necessary to connect us to it automatically.

The tool actually generates a VB proxy class (same principle as in .NET), and sets all the method calls and parameters up for us automatically. Thus, we call the web service just like calling a function in our local program... obviously because the proxy class is in our local program; we even get 'Intellisence' to help us fill in the function names and parameters.

This thing is easy to use, but you will need Office XP (2002). If you have downloaded it you will know that Microsoft have included enough information on how to use it, so it is not necessary to repeat this here. Once you have installed it you can use the tool from any Office VBA editor from menu item: Tools->Web Service References.

This article illustrates that within a comparative short space of time we can develop a solution to run the currency conversion web service (or any other service) from within a Microsoft Office product. Using the SOAP toolkit we can extend this functionality to any environment from which we can call a COM component. This underlines the ease from which we can utilise a web service architecture using Microsoft tools.

API's are available for other platforms and applications (IBM Websphere, J2EE etc...) to facilitate Web Service connectivity. Thus much of the work to connect to web services is already done for - we just have to get our hands on the right tools. These tools follow the published standards, allowing us to get on with the business of building our solution.

In this distributed architecture it is important to take into account performance and availability issues. Having said this, in the end we will only be using the Web Services that we really need and can trust. To this end web service standards can offer a powerful and rapid solution.