Excel 2003 Programming: A Developer's Notebook

Use the Web Services Toolkit

The Web Services Toolkit provides a way to find and reference web services from Visual Basic. Once you create a reference to a web service, the Toolkit generates classes that give you a familiar interface to the XML expected by the web service. The Toolkit-generated classes also handle responses from the web service, converting those into objects, properties, and methods, rather than raw XML.

Note

Office 2003 doesn’t come with the Web Services Toolkit installed. You need to download and install that tool from Microsoft before proceeding.

Depending on the web service you are using, the Web Services Toolkit may generate many or just a few new classes (Figure 4-9).

Figure 4-9. Office Web Services Toolkit creates proxy classes for the referenced web service

How to do it

In order to use web services from Visual Basic, you must first follow these steps:

Note

The Web Services Toolkit makes using web services easier by generating classes from the web service description. Those classes can then be used with a standard objectoriented approach to create an instance of the web service and invoke the web services properties and methods.

1. Find the Microsoft Office Web Services Toolkit from Microsoft by searching for “Web Services Toolkit” at http:// www.microsoft.com/downloads .

2. Download the Web Services Toolkit installation program (setup.exe).

3. Run the downloaded installation program and follow the steps provided by the Setup Wizard.

4. Start Excel and open the Visual Basic editor.

5. In Visual Basic, select Web References from the Tools menu. Visual Basic displays the Microsoft Office 2003 Web Services Toolkit references dialog (Figure 4-10).

Figure 4-10. Use the Microsoft Office 2003 Web Services Toolkit to create a Web Reference

When you create a Web Reference, the Web Services Toolkit automatically adds references to Microsoft Office SOAP type library and the Microsoft XML library. Then, the toolkit generates proxy classes for the web service.

To see how this works, follow these steps:

1. From the Visual Basic Tools menu, select Web References.

2. Select Web Service URL and type the following line in the text box below that option:

      http://api.google.com/GoogleSearch.wsdl

3. Click Search. The Web Services Toolkit displays the web services available from Google (Figure 4-11).

   

   Figure 4-11. Creating a reference to the Google web service

4. Select the GoogleSearchService and click Add. The Web Service Toolkit adds references to the SOAP and XML libraries and creates proxy classes for each of the services (Figure 4-12).

   

   Figure 4-12. The Web Service Toolkit creates Google web service proxy classes

How it works

Proxy classes are modules of code that stand-in for the code that runs on the server providing the web service. You have to have a local copy of this code so you can compile your application against something. These proxy classes provide the properties and methods you call on the web service—they package those calls, send them, and receive their responses.

The code in these proxy classes is not simple. Fortunately, you don’t have to understand much of it, just create an instance of the main class (identified by the prefix “clsws”) and use its properties and methods. For example, the following code uses the generated classes to search Google for work I’ve done on Excel:

   Dim i As Integer, wsGoogle As New clsws_GoogleSearchService
   Dim wsResult As struct_GoogleSearchResult, wsElement As struct_ResultElement
   Dim devKey As String, searchStr As String
   ' This key is from Google, used to identify developer.
   devKey = "ekN14fFQFHK7lXIW3Znm+VXrXI7Focrl"
   ' Items to search for.
   searchStr = "Jeff Webb Excel"
   ' Call the search web service.
   Set wsResult = wsGoogle.wsm_doGoogleSearch(devKey, _
     searchStr, 0, 10, False, "", False, "", "", "")
   ' For each of the results
   For i = 0 To wsResult.endIndex - 1
       ' Get the individual result.
       Set wsElement = wsResult.resultElements(i)
       ' Display the result.
       Debug.Print wsElement.title, wsElement.URL
   Next

OK, that’s not simple either. Most of the complication here comes from the web service itself. Google requires a license key to use their service, I include my key in the devKey variable. Google allows 1,000 search requests per day for this free license key, so you’ll want to get your own key from Google eventually. But for now, it’s OK to use my key.

Next, the wsm_doGoogleSearch method submits the search to Google. That method takes a lot of arguments and returns a structure, which is defined in another proxy class so you need to use Set to perform assignment. Similarly, you need to use Set to get elements from the result.

What about...

Table 4-1 lists the web service description addresses for the Google and Amazon web services. These are the addresses you enter in the Web Services Toolkits’ Web Service URL field to create a reference to these services.

Table 4-1. Web service description addresses

Web Service	Web Service URL
Amazon	http://soap.amazon.com/schemas3/AmazonWebServices.wsdl
Google	http://api.google.com/GoogleSearch.wsdl