Once you've installed the toolkit, you can start connecting your spreadsheet to web services. To get to the Web Service References Tool (its name inside of all of the applications), you'll need to go to Tools Macro Visual Basic Editor. On the Tools menu of the Visual Basic Editor, you'll find Web Services References. Selecting that will bring up the dialog box shown in Figure 9-1.
Figure 9-1. The Microsoft Office Web Services Toolkit in action
You can use the search features in the top left of this dialog to find services through Microsoft's UDDI service, or you can choose instead to enter a URL for the WSDL file at the lower left. The toolkit defaults to UDDI, and UDDI hosted by Microsoft at that, as you'll see if you click the More button. If you'd like to try looking for a service through UDDI, enter a keyword or business name in the appropriate location, and then click the Search button at the bottom. If you enter "currency" under keyword, click the More button, and click Search, you'll have a brief wait while the toolkit queries Microsoft and then you'll see something like Figure 9-2.
Figure 9-2. Searching for services using the UDDI support of the Microsoft Office Web Services Toolkit
The Currencyws service offers two methods: GetLicRate and GetRate. (As the documentation for them is identical, it's difficult to say what the difference is.) Clicking the Test button will let you visit a page where you can test the services, if the provider of the service offers testing. If you were to check the box next to "Currencyws" and click Add, the toolkit would generate code to let you access the service.
Instead, because UDDI hasn't really taken off, and most consumers of web services are using their own or other people's private services, we'll experiment with the other option, the Web Service URL. This lets you work with any service whose providers offer a WSDL file describing it, whether or not it's been registered with UDDI.
You can find a listing of public services at http://xmethods.net/, though you should definitely test to make sure that the services still work before you integrate them with your documents. Many services also require license keys and sometimes license payments, but for this example we'll use one that is available for free. It returns the IP address for a given domain name. We'll start by telling Excel which service we'd like to use, in this case, http://www.cosme.nu/services/dns.php?wsdl. Enter that value in the URL: box at the bottom left and click Search. A search result for the DNS service will appear in the top right, as shown in Figure 9-3. Check the box to its left.
Figure 9-3. Telling the Web Services Toolkit to generate code for a specific web service
Clicking the Add button will make Excel generate VBA code for invoking the service, as shown in Figure 9-4.
Figure 9-4. VBA code for accessing the DNS service generated by the Web Services Toolkit
Next, close the Visual Basic Editor and set up a very simple spreadsheet like the one shown in Figure 9-5.
Figure 9-5. A spreadsheet for adding web services
To demonstrate how to call a service, add a button for calling the service. Display the Control Toolbar by right-clicking on a toolbar and choosing Control Toolbox from the pop-up menu. Click the button icon, and then click on the spreadsheet wherever you'd like the button to go. Right-click the button, and choose Properties from the pop-up menu. Under Name, enter GetData; under Caption, enter Get IP Address. (These names can be anything you like.) Close the Properties dialog box, and your spreadsheet should look something like Figure 9-6.
Figure 9-6. Spreadsheet with button for calling web services
Example 9-1. Code for calling simple web service
Private Sub GetData_Click( ) Dim info As New clsws_dns Dim name As String Dim IP As String name = Range("B2").Text IP = info.wsm_dns(name) Set IPRange = Range("B3") IPRange.Value = IP End Sub
This code is pretty simple. It references the object the toolkit created for the web service, clsws_dns, and creates variables for the name and IP address. It collects the name from cell B2, calls the web service at wsm_dns with the name as an argument, and then puts the value returned into cell B3. The method name, wsm_dns, is set by the Web Services Toolkit and appears in the comments at the top of the generated code, as you can see if you look back to Figure 9-4.
Once you've entered this code and closed the Visual Basic Editor, you can then leave design mode by making sure the triangle and ruler icon at the left of the Control Toolbar isn't highlighted. The spreadsheet will now let you enter a domain name in cell B2. Clicking on the "Get IP Address" button will invoke the web service, using the generated wsm_dns method, and put the IP address corresponding to that domain name in cell B3. Figures 9-7 and 9-8 show this spreadsheet in action with different domain names.
Figure 9-7. A retrieved IP address for oreilly.com
Figure 9-8. A retrieved IP address for simonstl.com
IP address resolution is one of the simpler services out there, but there are many cases where services this simple can be very useful in a spreadsheet, including currency convertors, price retrieval, postal code processing, and much more.