So far I can load some of these apps, but cannot get them to
run. I was wondering if anyone has some experience with this that offer some
helpful suggestions for getting these to work in Excel.
Calling a Web
Service
An even more powerful
approach is to create a spreadsheet that calls a dedicated web service.
Essentially, a web service is a miniature program that you can call over the
Web. Calling a web service is like calling a built-in Excel function, in that
you supply some information (as arguments) and retrieve a result. The difference
is that unlike an Excel function, the web service doesn't execute on your
computer. Instead, it runs on a web server somewhere on the
internet.
Clearly, there's more
involved in calling a web service than there is in using a built-in Excel
function. Fortunately, in Excel 2002 and Excel 2003, most of the work is managed
for you behind the scenes. All you need to do is use a specialized web service
add-in and write a small amount of macro code.
The first step is to
download the Office Web Services Toolkit, a free add-in that gives you the
ability to call remote web services from inside an Office document. To download
the toolkit, surf to the Microsoft Download
Center and search for Office Web Services Toolkit. You'll see two different
versions: one that's designed for Office XP and another that works with Office
2003.
http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=Office%20Web%20Services%20Toolkit&DisplayLang=en
Once you install the
toolkit, you're ready to web-enable your worksheets. However, there's still a
gap; namely, you need to find a web service that provides the information you
need. Fortunately, web services are a cross-platform standard, and there are
hundreds of free web services scattered over the Web and many more fee-based
options. Additionally, companies that have in-house development staff often
create their own web services using a tool like Microsoft's .NET platform. In
this article, you'll use a web service from XMethods that provides up-to-date currency
exchanges rates.
In order to use any web
service, you need to know its URL. The URL for the currency exchange service is
http://www.xmethods.net/sd/2001/CurrencyExchangeService.wsdl. You
can try typing this URL directly into your browser, but all you'll see is an
unhelpful XML document that describes, in computerese, how to communicate with
the currency exchange web service. (Thankfully, Excel is able to read this XML
document and configure your spreadsheet accordingly.)
Once you have the URL for
your web service, the next step is to attach it to your workbook by following
these steps:
1.
You need
to perform this task from the Visual Basic editor that's built into Excel, so
start by choosing Tools -> Macro -> Visual Basic Editor from the menu.
2.
In the
Visual Basic editor, choose Tools -> Web Services References, which shows a
dialog box where you can connect to your web service (see Figure 3).
3.
Click on
the Web Service URL option at the bottom of the window, and then type in the URL
for the currency exchange service and click on Search. It's always easiest to
use this technique to go straight to the web service you want. Although the
window also has options for hunting for web services, most web services aren't
registered in public catalogs and so won't show up in a keyword search.
4.
An entry
for the currency exchange service will appear in the tree on the right side of
the dialog box. Click to place a check mark next to it.
5.
Click on
Add to complete the process.
Figure 3.
Once you finish these
steps, Excel generates a slew of extra code for calling the web service. With
the currency exchange service, Excel places the code in a class named, rather
unhelpfully, clsws_CurrencyExchangeServi. (You'll see this class in the
Visual Basic editor tree.) Fortunately, you don't need to understand this code.
However, you should scroll through to find the wsm_getRate() function. This is the important
part; it calls the exchange web service and gets the exchange rate for the
specified countries.
There's still an extra
step you need to take before you can use the web service in your worksheet: you
need to add a custom function that uses the generated web service class. You can
then call this function at any point in a cell formula. Creating a custom
function in Excel is fairly straightforward (and the process is explained in
detail in Excel: The Missing
Manual). The first step is to create a new module, by choosing Insert ->
Module in the Visual Basic Editor window. Now you need to add a function inside
the module that calls the web service. Here's the code you need to
add:
Function GetRate(country1 As String, country2 As String)
Dim WebService As New clsws_CurrencyExchangeServi
GetRate = WebService.wsm_getRate(country1, country2)
End Function
It's worth taking a
line-by-line look at this function:
1.
The
first line defines the function. In this case, the name GetRate() makes sense, but you can use
whatever you want. The next two parameters define two arguments that the person
calling the function needs to supply. In this case, you need two country names.
(The first country name is the currency you're changing to, while the second
country name is the currency you're changing from.) If you're wondering how I
knew to use two parameters, there's no magic involved; this function just has to
match the wsm_getRate() function that Excel generated for
the web service.
2.
The
second line creates the web service object. At this point, all the
initialization you need is performed automatically. Note that the name you use
is clsws_CurrencyExchangeServi, because that's the name of the
file Excel generated when you added the web reference.
3.
The
third line does the hard work of calling the web service (with the supplied
country information) and it then returns the result to whoever called the
GetRate() function.
Using the
GetRate() function is refreshingly easy. To
try it out, type a cell formula like this:
=GetRate("usa","euro")
This returns (at the time
of this writing) an exchange rate of 0.7649 dollars to the euro (see Figure
4).
Figure 4.
To convert your bank
account balance (stored in cell B2) from dollars to euros, you use a formula
like this:
=GetRate("usa","euro") *
B2
For a full list of
acceptable country names, refer to the detailed documentation for the currency
exchange service at XMethods. While you're
at it, you might find some other demonstration services that you'd like to
try.
http://www.xmethods.net/
