Importing JSON Data into Excel

The below sample code is something I wrote for someone on another forum, the problem is that the Excel Web Data import doesn't work in dynamically built tables - built with JavaScript rather than being in the source of the document (as far as I can tell anyway).

The OP was trying to capture the leader board info from the PGA Tour website http://www.pga.com/openchampionship/scoring/leaderboard and the data import he had previously used had stopped working as the website design had changed. The leader board is created in the browser from javascript which periodically updates, the actual source of the data for the leaderboard is here:

http://data.pga.com/jsonp/event/open...aderboard.json

As you can see the data is in the form of a JSON object in a Javascript call back wrapper - JSON is extremely widely used on the web and allows storing complex data structures in a string, a single row of the above with some formatting applied, looks like this: http://pastebin.com/raw.php?i=khp1Ag3k

To extract this data and build a table that can be used in Excel, the JSON needs to be parsed - there is no built in library for reading JSON in Excel. The most simple way I have ever found of doing this is here: http://stackoverflow.com/questions/6...n-in-excel-vba

The above uses the Microsoft Script Control which can run JScript and return the values/objects of Jscript functions to VBA.

The below is the code that builds a table from the PGA JSON object and puts it into a sheet in Excel. The code is based on the code on SO, but adapted to work for this scenario. For the below to run you need to add a reference to Microsoft Script Control 1.0

Please Login or Register  to view this content.

I'm pretty sure that there must be other useful applications of this library - but I can't think of any off the top of my head

Hope this helps someone

Cheers

Kyle

After much toing an froing between snb and I, I am including snb's take on this problem to show primarily that the JSON format is not some sort of strange dark art, but actually just a string, specifically:

Originally Posted by snb

a json file isn't some sort of secret code but a flat AscII file with a predermined prescripted structure

The following, whilst elegant may at first (predictably) seem entirely incomprehensible given it's contributor

It does however serve to demonstrate that since JSON is just a string, standard(ish) string manipulation techniques can extract the data:
Way1:

Originally Posted by snb

Please Login or Register  to view this content.

Way2:

Originally Posted by snb

Please Login or Register  to view this content.

After much discussing of the matter as to ways of extracting json into a useful format, it comes down to personal preference and what you're comfortable with. In terms of speed, mine is faster but that's a trade off as I am using JavaScript functions that are substantially different from VBA, whereas snb uses built in functions.

The more interesting point from all this for me is that it appears that the WinHttpRequest is substantially slower than the XMLHTTP object, so that it appears that the additional functionality comes at a cost.

Anyways I'm off to play with YQL