How to get a value from nested / staggered Excel object - data from json

Question

I have tried many JSON addins for Excel but I am having no luck parsing the JSON data below. finally I have managed to use the code below

Sub jsonDecode()
   Dim jsonDecode As Variant
   jsonText = Worksheets("Sheet3").Range("A1").Value
    Set sc = CreateObject("ScriptControl"): sc.Language = "JScript"
    Set jsonDecode = sc.Eval("(" + jsonText + ")")
End Sub

to create a staggered object but cannot access the values in the image below. I have tried the following

msgbox(jsonDecode.location.id)
msgbox(tostring(jsonDecode.location.id))
msgbox(jsonDecode(location(id)))

Any help would be really appreciated for the code to get the values marked A and B in the image below :) Forgive me if my terminology is a bit skewif

Cheers!!

Image of the array tree in Excel locals window

JSON text is

{"location":{"id":2456,"name":"Tuggerah","region":"Central Coast","state":"NSW","postcode":"2259","timeZone":"Australia/Sydney","lat":-33.30701,"lng":151.4159,"typeId":1},"forecasts":{"weather":{"days":[{"dateTime":"2016-09-13 00:00:00","entries":[{"dateTime":"2016-09-13 00:00:00","precisCode":"showers-rain","precis":"Late rain","precisOverlayCode":"","night":false,"min":10,"max":22}]}],"units":{"temperature":"c"},"issueDateTime":"2016-09-13 11:35:20"},"wind":{"days":[{"dateTime":"2016-09-13 00:00:00","entries":[{"dateTime":"2016-09-13 00:00:00","speed":9.1,"direction":287,"directionText":"WNW"},{"dateTime":"2016-09-13 01:00:00","speed":9.3,"direction":258,"directionText":"WSW"},{"dateTime":"2016-09-13 02:00:00","speed":9.3,"direction":256,"directionText":"WSW"},{"dateTime":"2016-09-13 03:00:00","speed":9.1,"direction":254,"directionText":"WSW"},{"dateTime":"2016-09-13 04:00:00","speed":6.9,"direction":260,"directionText":"W"},{"dateTime":"2016-09-13 05:00:00","speed":5.7,"direction":256,"directionText":"WSW"},{"dateTime":"2016-09-13 06:00:00","speed":5.7,"direction":249,"directionText":"WSW"},{"dateTime":"2016-09-13 07:00:00","speed":5.9,"direction":245,"directionText":"WSW"},{"dateTime":"2016-09-13 08:00:00","speed":5.2,"direction":254,"directionText":"WSW"},{"dateTime":"2016-09-13 09:00:00","speed":4.6,"direction":272,"directionText":"W"},{"dateTime":"2016-09-13 10:00:00","speed":4.6,"direction":281,"directionText":"W"},{"dateTime":"2016-09-13 11:00:00","speed":6.1,"direction":312,"directionText":"NW"},{"dateTime":"2016-09-13 12:00:00","speed":8,"direction":14,"directionText":"NNE"},{"dateTime":"2016-09-13 13:00:00","speed":9.6,"direction":45,"directionText":"NE"},{"dateTime":"2016-09-13 14:00:00","speed":9.8,"direction":56,"directionText":"NE"},{"dateTime":"2016-09-13 15:00:00","speed":9.6,"direction":77,"directionText":"ENE"},{"dateTime":"2016-09-13 16:00:00","speed":9.4,"direction":88,"directionText":"E"},{"dateTime":"2016-09-13 17:00:00","speed":10.7,"direction":73,"directionText":"ENE"},{"dateTime":"2016-09-13 18:00:00","speed":11.9,"direction":43,"directionText":"NE"},{"dateTime":"2016-09-13 19:00:00","speed":12.6,"direction":28,"directionText":"NNE"},{"dateTime":"2016-09-13 20:00:00","speed":11.7,"direction":11,"directionText":"N"},{"dateTime":"2016-09-13 21:00:00","speed":9.8,"direction":336,"directionText":"NNW"},{"dateTime":"2016-09-13 22:00:00","speed":7.8,"direction":318,"directionText":"NW"},{"dateTime":"2016-09-13 23:00:00","speed":4.6,"direction":304,"directionText":"NW"}]}],"units":{"speed":"km/h"},"issueDateTime":"2016-09-13 12:11:55"}},"forecastGraphs":{"temperature":{"dataConfig":{"series":{"config":{"id":"temperature","color":"#003355","lineWidth":2,"lineFill":false,"lineRenderer":"StraightLineRenderer","showPoints":false,"pointFormatter":"TemperaturePointFormatter"},"yAxisDataMin":10.7,"yAxisDataMax":22.2,"yAxisMin":0,"yAxisMax":32,"groups":[{"dateTime":1473724800,"points":[{"x":1473724800,"y":12.4},{"x":1473728400,"y":11.7},{"x":1473732000,"y":11.3},{"x":1473735600,"y":11},{"x":1473739200,"y":10.9},{"x":1473742800,"y":10.7},{"x":1473746400,"y":11},{"x":1473750000,"y":12.2},{"x":1473753600,"y":14.2},{"x":1473757200,"y":16.8},{"x":1473760800,"y":19.3},{"x":1473764400,"y":21},{"x":1473768000,"y":21.9},{"x":1473771600,"y":22.2},{"x":1473775200,"y":22.2},{"x":1473778800,"y":21.7},{"x":1473782400,"y":20.7},{"x":1473786000,"y":19.1},{"x":1473789600,"y":17.6},{"x":1473793200,"y":16.1},{"x":1473796800,"y":15.3},{"x":1473800400,"y":14.9},{"x":1473804000,"y":14.6},{"x":1473807600,"y":14.1}]}],"controlPoints":{"pre":{"x":1473721200,"y":10.2},"post":{"x":1473811200,"y":13.7}}},"xAxisMin":1473724800,"xAxisMax":1473811199},"units":{"temperature":"c"},"issueDateTime":"2016-09-13 07:21:53","nextIssueDateTime":"2016-09-13 08:21:53"},"precis":{"dataConfig":{"series":{"config":{"id":"precis","lineFill":false,"showPoints":true,"pointRenderer":"PrecisSummaryPointRenderer","pointFormatter":"PrecisSummaryPointFormatter"},"groups":[{"dateTime":1473724800,"points":[{"x":1473728400,"precisCode":"partly-cloudy","night":true},{"x":1473739200,"precisCode":"fog","night":true},{"x":1473750000,"precisCode":"mostly-cloudy","night":false},{"x":1473760800,"precisCode":"mostly-cloudy","night":false},{"x":1473771600,"precisCode":"mostly-cloudy","night":false},{"x":1473782400,"precisCode":"mostly-cloudy","night":false},{"x":1473793200,"precisCode":"chance-shower-cloud","night":true},{"x":1473804000,"precisCode":"showers-rain","night":true}]}],"controlPoints":[]},"xAxisMin":1473724800,"xAxisMax":1473811199}}},"observational":{"observations":{"temperature":{"temperature":18.5,"apparentTemperature":17.4,"trend":-1},"humidity":{"percentage":87},"dewPoint":{"temperature":16.3,"trend":1},"pressure":{"pressure":1019.3,"trend":null},"wind":{"speed":16.7,"gustSpeed":20.4,"trend":0,"direction":202.5,"directionText":"SSW"},"rainfall":{"lastHourAmount":0,"todayAmount":0,"since9AMAmount":0}},"stations":{"temperature":{"name":"Norah Head AWS","distance":15.5},"pressure":{"name":"Norah Head AWS","distance":15.5},"wind":{"name":"Norah Head AWS","distance":15.5},"rainfall":{"name":"Norah Head AWS","distance":15.5}},"issueDateTime":"2016-09-13 12:20:00","units":{"temperature":"c","amount":"mm","speed":"km/h","distance":"km","pressure":"hPa"}},"observationalGraphs":{"pressure":{"dataConfig":{"series":{"config":{"id":"pressure","color":"#003355","lineWidth":2,"lineFill":false,"lineRenderer":"StraightLineRenderer","showPoints":false,"pointFormatter":"PressurePointFormatter"},"yAxisDataMin":1018.2,"yAxisDataMax":1020.9,"yAxisMin":850,"yAxisMax":1100,"groups":[{"dateTime":1473724800,"points":[{"x":1473724800,"y":1019.4},{"x":1473728400,"y":1019.9},{"x":1473730200,"y":1019.4},{"x":1473732000,"y":1019.1},{"x":1473733800,"y":1018.7},{"x":1473735600,"y":1018.2},{"x":1473737400,"y":1018.5},{"x":1473739200,"y":1018.8},{"x":1473741000,"y":1019.1},{"x":1473742800,"y":1019.1},{"x":1473744600,"y":1019.3},{"x":1473746400,"y":1019.7},{"x":1473748200,"y":1020},{"x":1473750000,"y":1020.1},{"x":1473751800,"y":1020.5},{"x":1473753600,"y":1020.9},{"x":1473755400,"y":1020.9},{"x":1473757200,"y":1020.9},{"x":1473759000,"y":1020.4},{"x":1473760800,"y":1020.4},{"x":1473762600,"y":1020.5},{"x":1473764400,"y":1020.5},{"x":1473766200,"y":1019.8},{"x":1473768000,"y":1019.3}]}],"controlPoints":[]},"xAxisMin":1473724800,"xAxisMax":1473897599},"units":{"pressure":"hpa"},"provider":{"id":329,"name":"Norah Head AWS","lat":-33.28,"lng":151.58,"distance":15.5,"units":{"distance":"km"}}},"temperature":{"dataConfig":{"series":{"config":{"id":"temperature","color":"#003355","lineWidth":2,"lineFill":false,"lineRenderer":"StraightLineRenderer","showPoints":false,"pointFormatter":"TemperaturePointFormatter"},"yAxisDataMin":15.1,"yAxisDataMax":20.1,"yAxisMin":0,"yAxisMax":32,"groups":[{"dateTime":1473724800,"points":[{"x":1473724800,"y":15.6},{"x":1473725400,"y":16.1},{"x":1473726000,"y":16.1},{"x":1473726600,"y":16.1},{"x":1473727200,"y":15.8},{"x":1473727800,"y":15.9},{"x":1473728400,"y":16},{"x":1473729000,"y":15.9},{"x":1473729600,"y":15.9},{"x":1473730200,"y":15.8},{"x":1473730800,"y":15.6},{"x":1473731400,"y":15.4},{"x":1473732000,"y":15.4},{"x":1473732600,"y":15.4},{"x":1473733200,"y":15.5},{"x":1473733800,"y":15.3},{"x":1473734400,"y":15.3},{"x":1473735000,"y":15.1},{"x":1473735600,"y":15.3},{"x":1473736200,"y":15.3},{"x":1473736800,"y":15.5},{"x":1473737400,"y":15.5},{"x":1473738000,"y":15.5},{"x":1473738600,"y":15.4},{"x":1473739200,"y":15.5},{"x":1473739800,"y":15.6},{"x":1473740400,"y":15.7},{"x":1473741000,"y":15.8},{"x":1473741600,"y":15.9},{"x":1473742200,"y":16.1},{"x":1473742800,"y":16.2},{"x":1473743400,"y":16.4},{"x":1473744000,"y":16.4},{"x":1473744600,"y":16.4},{"x":1473745200,"y":16.4},{"x":1473745800,"y":16.3},{"x":1473746400,"y":16.3},{"x":1473747000,"y":16.4},{"x":1473747600,"y":16.4},{"x":1473748200,"y":16.5},{"x":1473748800,"y":16.6},{"x":1473749400,"y":16.8},{"x":1473750000,"y":16.8},{"x":1473750600,"y":16.9},{"x":1473751200,"y":17},{"x":1473751800,"y":17.1},{"x":1473752400,"y":17.4},{"x":1473753000,"y":17.4},{"x":1473753600,"y":17.6},{"x":1473754200,"y":17.9},{"x":1473754800,"y":17.9},{"x":1473755400,"y":17.9},{"x":1473756000,"y":17.9},{"x":1473756600,"y":18.2},{"x":1473757200,"y":18.2},{"x":1473757800,"y":18.3},{"x":1473758400,"y":18.2},{"x":1473759000,"y":18.3},{"x":1473759600,"y":18.4},{"x":1473760200,"y":18.6},{"x":1473760800,"y":18.8},{"x":1473761400,"y":18.7},{"x":1473762000,"y":18.6},{"x":1473762600,"y":18.4},{"x":1473763200,"y":18.6},{"x":1473763800,"y":19.2},{"x":1473764400,"y":20.1},{"x":1473765000,"y":19.6},{"x":1473765600,"y":20.1},{"x":1473766200,"y":20},{"x":1473766800,"y":20.1},{"x":1473767400,"y":19.2},{"x":1473768000,"y":18.8},{"x":1473768600,"y":18.6},{"x":1473769200,"y":18.5}]}],"controlPoints":[]},"xAxisMin":1473724800,"xAxisMax":1473897599},"units":{"temperature":"c"},"provider":{"id":329,"name":"Norah Head AWS","lat":-33.28,"lng":151.58,"distance":15.5,"units":{"distance":"km"}}}},"regionPrecis":{"days":[{"dateTime":"2016-09-13 00:00:00","entries":[{"dateTime":"2016-09-13 00:00:00","precis":"Cloudy. Patchy fog early this morning. High (70%) chance of rain in the late evening. Light winds becoming northeasterly 15 to 20 km/h in the late afternoon then tending northerly in the evening."}]}],"issueDateTime":"2016-09-13 10:41:16","name":"Central Coast"}}

Answer 1

You may use the free Microsoft Excel add-In Power Query* (from Excel 2010) to browse and extract data from your JSON file.

On superuser you have an example.

*Power Query is known as Get & Transform in Excel 2016 and is full part of the software

Answer 2

The excel-requests Addin might be of help (disclaimer: I'm the author of this open source project).

You can find docs, installer script etc here: http://excel-requests.readthedocs.io/en/latest/.

Let me know if you need help.

Answer 3

Your approach uses JScript within VBA over ScriptControl object. This approach is not recommendable since ScriptControl object is only a 32-bit ActiveX component. It will not work with 64-bit versions of Office.

You can make it work if you accept that JScript objects are different from VBA objects. So you need a JScript method to get the JScript objects.

Example:

Sub jsonDecode()

 Dim jsonDecode As Variant
 jsonText = Worksheets("Sheet3").Range("A1").Value
 Set sc = CreateObject("ScriptControl"): sc.Language = "JScript"

 sc.AddCode "function getProperty(jsonObj, propertyName) { return jsonObj[propertyName]; } "

 Set jsonDecode = sc.Eval("(" + jsonText + ")")

 Set oLocation = sc.Run("getProperty", jsonDecode, "location")
 MsgBox sc.Run("getProperty", oLocation, "id")

 Set oForecasts = sc.Run("getProperty", jsonDecode, "forecasts")
 Set oWeather = sc.Run("getProperty", oForecasts, "weather")
 Set oDays = sc.Run("getProperty", oWeather, "days")
 Set oDay0 = sc.Run("getProperty", oDays, "0")
 MsgBox sc.Run("getProperty", oDay0, "dateTime")

End Sub

Here function getProperty is the JScript method to get the JScript objects.

But as stated already you should look for better methods parsing JSON with VBA. There are some if you search.