Excel, REST, and XMLMAPS.Import vs WINHTTP

For some reason the wires never properly crossed that; because you can import XML to Excel directly from a URL, there is no need to use WINHTTP to do simple REST web service queries.

I originally started using the technique after viewing the Amazon Web Services code that uses WinHTTP, however it’s not necessary for only requesting the XML from a web service.

Proof of Concept: Here is a barebones example of using the Yahoo News Search API to get the latest Yahoo news on “Dan Rather” into Excel. This requires a version of Excel that supports XML:

1. Highlight then Copy this URl:

http://api.search.yahoo.com/WebSearchService/V1/webSearch?appid=YahooDemo&query=Dan+Rather&results=10

2. In Excel go to Data->XML->Import

3. In the File Name box use the Paste Shortcut (CTRL-V) to enter the url you just copied and hit enter.

4. Excel will prompt you for the cell to list the Yahoo News Results

So we just managed to use a the Yahoo Rest Webservice to get data into Excel, without WINHTTP. Keep in mind that’s a bare bones example. To expound on it, add a map to the workbook and use XMLMaps.import instead of the WINHTTP examples I’ve been using.

When would I currently use WINHTTP with REST webservices? My best guess so far:

1. When login is required for the service. In which case I need to use SetCredentials

2. When I don’t want to import the XML data to an XML map, rather I just want the raw data. For instance I want to manipulate the data before writing it to a spreadsheet or possibly navigate the XML and pick and choose which data to display based on custom criteria.

Finally, XMLMAPS.Import and WINHTTP both work for consuming REST web services, however the latter requires a few more lines of code. I’ll keep stumbling around with the Web Services untill I learn it (and of course: get it correct). I wonder if anyone else has any REST examples using Excel?