Manipulating web content in Excel, or, My Excel can haz ajax?
Yes. Well, kinda. Not really. Er… maybe. Hey look, it works!
There’s not much better than Microsoft Excel for mucking about with big grids of stuff really easily. I’ve often used Excel to “screen scrape” or at least “DOM scrape” web content for various reasons. One of the most common is to get stuff from a web app that doesn’t provide a “download to csv” option or a decent API. Another reason is to grab stuff from an RSS feed to mix in to whatever my spreadsheet is doing, or just to use it as a REST client. So I offer you the following generic solution, I’ll base this one around the RSS example…
Wikipedia, the absolute source of all knowledge and truth, says that for something to be AJAX “the use of JavaScript and XML is not actually required, nor do the requests need to be asynchronous”. Despite this “AJAX” is generally considered to be an initialism and acronym for “Asynchronous JavaScript and XML”. Taking both of these into consideration I must conclude that AJAX means whatever we want it to mean. For me it’s when I’m using an XMLHttpRequest object in one way or another to yank some xml/html/whatever from a web server.
In Excel I’m not using javascript asynchronously but I am using xml and even a XMLHttpRequest object via com interop so for me that qualifies as doing ajax in Excel.
How to do it:
1. Avoid late binding of com objects and help yourself out by adding a reference (code editor: Tools -> References…) from the Excel app to a library that will provide a XMLHttpRequest object. Happily, “Microsoft XML, v6.0” provides such a beastie for us.
2. Write some Ajaxy code to use the object like this:
Private Sub MMD_DoSomeAjaxyStuff() 'declare some useful vars Dim req As XMLHTTP Dim doc As DOMDocument Dim url As String 'Some things to process the XML Dim entries As IXMLDOMNodeList Dim entry As IXMLDOmNode 'The RSS to get, naturally uri = "https://mikemacd.wordpress.com/feed/" 'make a new XMLHttpRequest object Set req = new XMLHTTP 'Load the xml req.Open "GET", uri, , "", "" req,Send While req.ReadyState <> 4 DoEvents 'yeah I know... Wend 'Read the XML Set doc = New DOMDocument doc.loadXML req.responseText 'Do something with the RSS Set entries = doc.getElementsByTagName("item") For i = 0 To entries.Length - 1 'Do something with the RSS like stick it in a cell Set entry = entries.Item(i) Cells(i + 1, 1).Value = entry.childNodes(1).Text Next i End Sub
3. w00t 😀
Really informative article post.Thanks Again. i Like This Article
December 22, 2012 at 3:29 pm