Calling a Web Service from Microsoft Office (Excel)
This article presents a guide to using the "currency conversion" demo web service from within Microsoft Excel.
It talks about the Microsoft SOAP toolkit, and the Microsoft XP Office Web Services toolkit.
It also discusses performance implications both for the web service consumer and the provider.
(see instructions to download the sample spreadsheet).
Introduction - what you need
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
- 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
Now let us begin..
- Open the Microsoft Visual Basic Editor..
Tools->Macro->Visual Basic Editor
- 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).
- 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.
VBA code to call the web service...
Simply copy this code in to your Excel VBA code module.
' 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 the web service
fResult = objSClient.calcExcRate(rsCurrIn, rsCurrOut, vfAmtIn)
Set objSClient = Nothing
ccyConvert = fResult
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..
Performance implications of Web Services
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
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.
Microsoft Office XP Web Services toolkit
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
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.
* For security and performance reasons the demo web service presented on WebContinuum has a built-in 100 call limit.
This means any one user on the server can only call it 100 times before a new connection
must be made.