Office 2003 XML

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 ...