Programming Excel with VBA and .NET

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. For example, 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 an 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 24-12 displays the result of importing an Amazon search for wombats into a list on a worksheet.

The documentation for the Amazon Web Service is structured to show you ...