9.5 Accessing REST Web Services with VBA. 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 , you'll see the test form in Figure 9-15 . Figure 9-15. If you enter "13062" and click the Invoke button, you'll see something like Figure 9-16 .
Figure 9-16. 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. Integrating this simple version of the web service into Excel is easy. Figure 9-17. Different Ways of Using Web Queries in Microsoft Office Excel 2003. Frank Rice Microsoft Corporation February 2004 Applies to: Microsoft® Office Excel 2003 Summary: Learn various ways to use Web queries to retrieve data from a Web page.
This includes using static and dynamic queries as well as the Add and Find methods. Experimenting with these methods allows you to use the data on Web pages in your own applications. (9 printed pages) Contents Introduction Web pages often contain information that is ideal for analysis in Microsoft® Office Excel. VBA Excel 2007: Programmer sous ... URL Encoding. RFC 1738: Uniform Resource Locators (URL) specification The specification for URLs (RFC 1738, Dec. '94) poses a problem, in that it limits the use of allowed characters in URLs to only a limited subset of the US-ASCII character set: "...Only alphanumerics [0-9a-zA-Z], the special characters "$-_.+!
*'()," [not including the quotes - ed], and reserved characters used for their reserved purposes may be used unencoded within a URL. " HTML, on the other hand, allows the entire range of the ISO-8859-1 (ISO-Latin) character set to be used in documents - and HTML4 expands the allowable range to include all of the Unicode character set as well. In the case of non-ISO-8859-1 characters (characters above FF hex/255 decimal in the Unicode set), they just can not be used in URLs, because there is no safe way to specify character set information in the URL content yet [RFC2396.]
What characters need to be encoded and why? How are characters URL encoded? Example URL encoding converter Browser Peculiarities.