Excel 2003 Programming: A Developer's Notebook

Use Web Services Through XML

Web services from different companies define their interfaces differently. For example, the Google web service provides methods that take simple string arguments, whereas the Amazon web service provides methods that take complex XMLNodeList arguments.

It’s difficult to construct and debug XMLNodeList arguments for the Amazon web service. It’s much easier to invoke this web service directly through its URL and receive the XML response directly.

Note

You don’t have to use the Web Services Toolkit to use web services. In some cases, it’s actually easier to call a web service directly without using the generated proxy classes.

How to do it

The following code performs a keyword search for books about wombats on Amazon:

   Dim SearchUrl As String
   ' Create a new DOMDocument and set its options
   Dim xdoc As New DOMDocument
   xdoc.async = True
   xdoc.preserveWhiteSpace = True
   xdoc.validateOnParse = True
   xdoc.resolveExternals = False

   ' Create the search request
   SearchUrl = "http://xml.amazon.com/onca/xml2" & _
            "?t=" & "webservices-20" & _
            "&dev-t=" & "D1UCR04XBIF4A6" & _
            "&page=1" & _
            "&f=xml" & _
            "&mode=books" & _
            "&type=lite" & _
            "&KeywordSearch=wombat"

   ' Issue the request and wait for it to be honored
   Loaded = xdoc.Load(SearchUrl)
   ' Display the results
   Debug.Print xdoc.XML

Because the results are returned as XML, you can create XML map from the result and import the results into a list created from that XML map as shown here:

   Set wb = ThisWorkbook
   wb.XmlImportXml xdoc.XML, wb.XmlMaps("ProductInfo_Map"), True

Figure 4-13 displays the result of importing an Amazon search for wombats into a list on a worksheet.

Figure 4-13. Displaying XML results from a web service through an XML map and list

How it works

The documentation for the Amazon web service is structured to show you how to call its methods using its URL rather than using proxy classes and SOAP. This means that you don’t have to use the Web Services Toolkit to create proxies for the Amazon web service, just add a reference to the Microsoft XML type library.

This method of accessing a web service is sometimes called Representational State Transfer (REST). That acronym is useful as a search term when looking for this type of interface for a given web service. For instance, type “REST Google API” in a Google search to see an active debate on the relative features of REST and SOAP.

The Google web service doesn’t support direct access through its URL, but you can avoid the proxies and call it directly through SOAP. For example, the following code performs a search for wombats and imports the result through an XML map directly into a list:

   Dim soap As New SoapClient30, xn As IXMLDOMNodeList, strXML As String
   soap.MSSoapInit "http://api.google.com/GoogleSearch.wsdl"
   Set xn = soap.doGoogleSearch("ekN14fFQFHK7lXIW3Znm+VXrXI7Focrl", _
     "wombats", 0, 10, False, "", False, "", "", "")
   ' Build a string containing the results from the search in XML.
   strXML = "<GoogleSearchResults>"
   For i = 1 To xn.Length - 1
       strXML = strXML & xn(i).XML
   Next
   strXML = strXML & "</GoogleSearchResults>"
   ' Import the results through an XML Map into a list.
   Set wb = ThisWorkbook
   wb.XmlImportXml strXML, wb.XmlMaps("GoogleSearchResults_Map"), True

What about...

To learn about	Look here
Representational State Transfer (REST)	http:// internet.conveyor.com/RESTwiki/moin.cgi/FrontPage
MSXML Documentation	http://msdn.microsoft.com/library/en-us/xmlsdk/htm/sdk_intro_6g53.asp
DOMDocument	http://msdn.microsoft.com/library/en-us/xmlsdk/htm/xml_obj_overview_20ab.asp
IXMLDOMNodeList	http://msdn.microsoft.com/library/en-us/xmlsdk30/htm/xmobjxmldomnodelist.asp