| documents | ELEMENT | stylesheet | Word  | Example | Figure | schemas | elements |
  • Sitemap
  • Contact
  • index
  • index_A
  • index_B
  • index_C
  • index_D
  • index_E
  • index_F
  • index_G
  • index_H
  • index_I
  • index_J
  • index_K
  • index_L
  • index_M
  • index_N
  • index_O
  • index_P
  • index_Q
  • Previous Section Next Section

    9.5 Accessing REST Web Services with VBA

    While the Microsoft Office Web Services Toolkit doesn't provide direct support for REST-based services, REST is simple enough in practice that it doesn't really need a toolkit. All it requires is support for HTTP, which VBA offers through the MSXML2.XMLHTTP object. Using this object, you can create HTTP requests and process the responses. Since a lot of the SOAP web services described previously offer simple HTTP versions, it's easy to create a comparison, so this example will use the GetInfoByZIP service shown earlier. If you visit http://webservicex.net/uszip.asmx?op=GetInfoByZIP, you'll see the test form in Figure 9-15.

    Figure 9-15. Test form that supports the web service

    If you enter "13062" and click the Invoke button, you'll see something like Figure 9-16.

    Figure 9-16. A test invocation of the web service using GET

    What has happened here is that the form sent the zip code information as part of a GET query—note the query string in the address bar—and received an XML document in return. For many web services, there's no need for anything more complicated.

    Integrating this simple version of the web service into Excel is easy. Start by creating a new spreadsheet that looks like Figure 9-17, itself an echo of Figure 9-10.

    Figure 9-17. Spreadsheet base for running the REST web service

    There's no need to use the Microsoft Office Web Services Toolkit for this example; the VBA code for the button in Example 9-4 alone is all you need.

    Example 9-4. EREST-based code for retrieving Zip Code information
    Private Sub ZipCoderREST_Click( )
    Dim zip As String
    Dim query As String
    zip = Range("B1").Text
    'assemble query string
    query = "http://webservicex.net/uszip.asmx/GetInfoByZIP?USZip=" + zip
    'define XML and HTTP components
    Dim zipResult As New MSXML2.DOMDocument
    Dim zipService As New MSXML2.XMLHTTP
    'create HTTP request to query URL - make sure to have 
    'that last "False" there for synchronous operation
    zipService.Open "GET", query, False
    'send HTTP request
    'parse result
    zipResult.LoadXml (zipService.responseText)
    'extract result contents into appropriate cells
    Range("B3").Value = zipResult.selectSingleNode("//CITY").Text
    Range("B4").Value = zipResult.selectSingleNode("//STATE").Text
    Range("B5").Value = zipResult.selectSingleNode("//AREA_CODE").Text
    Range("B6").Value = zipResult.selectSingleNode("//TIME_ZONE").Text
    End Sub

    Instead of calling a generated object, this code constructs an HTTP request. If you enter "13062" into cell B1 and click the Get ZIP Info (REST) button, you'll see the result shown in Figure 9-18.

    Figure 9-18. Result of running the REST version of the Zip Code web service

    The REST HTTP version is both simpler and more portable, and demands less code on the server side as well. Why wouldn't you use REST rather than SOAP throughout your work? If you control both ends of the transaction, this is a very appealing option, as it lets you use whatever web tools you like, not just tools specifically oriented toward SOAP web services. However, there are many services that are available only through SOAP, and a growing number of programmers who know how to work with SOAP. It's best to have both approaches in your toolbox.

      Previous Section Next Section
      documents   ELEMENT   stylesheet   Word    Example   Figure   schemas