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

i have tried many json addins excel having no luck parsing json data below. have managed use code below

sub jsondecode()    dim jsondecode variant    jsontext = worksheets("sheet3").range("a1").value     set sc = createobject("scriptcontrol"): sc.language = "jscript"     set jsondecode = sc.eval("(" + jsontext + ")") end sub 

to create staggered object cannot access values in image below. have tried following

msgbox(jsondecode.location.id) msgbox(tostring(jsondecode.location.id)) msgbox(jsondecode(location(id))) 

any appreciated code values marked , b in image below :) forgive me if terminology bit skewif

cheers!!

image of 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 morning. high (70%) chance of rain in late evening. light winds becoming northeasterly 15 20 km/h in late afternoon tending northerly in evening."}]}],"issuedatetime":"2016-09-13 10:41:16","name":"central coast"}}

your approach uses jscript within vba on scriptcontrol object. approach not recommendable since scriptcontrol object 32-bit activex component. not work 64-bit versions of office.

you can make work if accept jscript objects different vba objects. need jscript method jscript objects.

example:

sub jsondecode()   dim jsondecode 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 jscript method jscript objects.

but stated should better methods parsing json vba. there if search.