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 zipService.send      '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.

Office 2003 XML
Office 2003 XML
ISBN: 0596005385
EAN: 2147483647
Year: 2003
Pages: 135

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net