Posted by Seamus on Thursday, January 06, 2011.

Using web services from Excel

Of course it’s easy in Google Docs…

Still, I got pretty excited when I saw this for the first time:

screenshot of Google Docs spreadsheet using importXML

In Google Docs, importXML lets you read XML from a web service and then use XPath to select particular elements:

=importXML(A4, "//emission_estimate/emission")

where A4 is the URL http://carbon.brighterplanet.com/flights.xml?origin_airport=MSN&destination_airport=ORD… yielding 327.4 kilos of carbon emissions or so.

What about Excel?

I first looked for an equivalent to XMLHttpRequest for Excel. A helpful StackOverflow post about Msxml2.ServerXMLHTTP and WinHttp.WinHttpRequest.5.1 got me started, but they’re Windows only. You’ll get errors about ActiveX can't create object.

Then, thanks to an excellent tip from Kennedy27, I found QueryTables:

With ActiveSheet.QueryTables.Add(Connection:="URL;http://carbon.brighterplanet.com/flights.txt", Destination:=Range("A2"))
    .PostText = "origin_airport=MSN&destination_airport=ORD"
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .Refresh
End With

I wrote a VBA function called GetEmissionEstimate and voilà!

screenshot of Excel for Mac 2011 using Querytables from VBA

It took me a while to remember that Excel is essentially functional programming, so the key to getting the result to auto-refresh is to make sure the output of your VBA function is entirely dependent on the input:

screenshot showing that arguments to VBA function entirely determine its output

Now if I change the destination airport, the emission estimate will automatically update.

What blog is this?

Safety in Numbers is Brighter Planet's blog about climate science, Ruby, Rails, data, transparency, and, well, us.

Who's behind this?

We're Brighter Planet, the world's leading computational sustainability platform.

Who's blogging here?

  1. Patti Prairie CEO