Brighter Planet's blog
Of course it’s easy in Google Docs…
Still, I got pretty excited when I saw this for the first time:
In Google Docs,
importXML lets you read XML from a web service and then use XPath to select particular elements:
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
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
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à!
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:
Now if I change the destination airport, the emission estimate will automatically update.