JSON parsing (US BLS) in VBA from MS Access

Question

Thank you in advance for your help.

I am using JSON VB6 Parser, which can be found at: VB JSON Parser

I have the following JSON answer (comes from a BLS site, specifically this link is here :

{"status": "REQUEST_SUCCEEDED", "ResponseTime": 71, "message": [], "Results": {"series": [{"SeriesID": "WPS012", "data": [{"" year " : "2014", "period": "M11", "periodName": "November", "value": "153.6", "footnotes": [{"code": "P", "text": " All indexes are subject to revision four months after the initial publication of ".}}}, {" Year ":" 2014 "," period ": M10", "periodName": "October", "value": "147.4", "footnotes": [{"code": "P", "text": "Preliminary. All indices are subject to revision four months after the original edition of "}]}, {" year. ":" 2014 "," period ":" M09 "," periodName ":" September "," value ":" 146.5 "," footnotes ": [{" code ":" P "," text ": "Preliminarily. All indices are subject to revision four months after the initial publication ".}}}, {" Year ":" 2014 "," period ":" M08 "," periodName ": August", "value": "156.9", footnotes: [{"code": "P", "text": "preliminary. All indices are subject to revision four months after the initial publication".}}}, {"year": "2014", "period": "M07", "periodName": "July", "value": "156.4", "footnote": [{}]}, {"year": "2014" "period": "M06", "periodName ":" June "," value ":" 179.6 "," footnote ": [{}]}, {" year ":" 2014 "," period ":" M05 "," periodName ":" May "," value ":" 205.4 "," footnotes ": [{}]}, {" year ":" 2014 "," period ":" M04 "," periodName ":" April "," value ":" 201.6 "," footnote ": [{}]}, {" year ":" 2014 "," period ":" M03 "," periodName ":" March "," value ":" 188.1 " , "footnotes": [{}]}, {"year": "2014", "period": "M02", "periodName": "February", "value": "180.2", "footnotes": [{}]}, {"year": "2014", "period": "M01", "periodName": "January", "value": "177.8", "footnotes": [{}]} , {"year": "2013", "period": "M12", "periodName": "December", "value": "183.2", "footnotes": [{}]}, {"year" : "2013", "period": "M11", "periodName": "November", "value": "180.4", "footnotes": [{}]}, {"year": "2013", "period": "M10", "periodName": "October", "value": "186.4", "footnotes": [{}]}, {"year": the value is "September", ":" 2013 ", "period": "M09", "periodName" ":" 197.1 "," footnotes ": [{}]}, {" year ":" 2013 "," period ":" M08 "," periodName ":" August "," value ":" 222.2 "," footnotes ": [{}]}, {" year ":" 2013 "," period ":" M07 " , "periodName": "July", "value": "252.9", "footnote": [{}]}, {"year": "2013", "period": "M06", "periodName": "June", "value": "259.0", "footnotes": [{}}}, {"year": "2013", "period": "M05", "eriodName": "May", "value": "263.7", "footnotes": [{}]}, {"year": "2013", "period": "M04", "periodName": "April", "value": " 249.3 "," footnote ": [{}]}, {" year ":" 2013 "," period ":" M03 "," periodName ":" March "," value ":" 268.1 ", "footnote": [{}]}, {"year": "2013", "period": "M02", "periodName": "February", "value": "267.1", "footnote": [ {}]}, {"year": "2013", "period": "M01", "periodName": "January", "value": "279.7", "demolition and ": [{}]}, {" year ":" 2012 "," period ":" M12 "," periodName ":" December "," value ":" 283.2 "," footnote ": [{ }]}, {"year": "2012", "period": "M11", "periodName": "November", "value": "280.8", "footnotes": [{}]}, { "year": "2012", "period": "M10", "periodName": "October", "value": "286.7", "footnotes": [{}]}, {"year": " 2012 "," period ":" M09 "," periodName ":" September "," value ":" 285.2 "," footnotes ": [{}]}, {" year ":" 2012 "," period ":" M08 "," periodName ":" August "," value ":" 298.9 "," footnote ": [{}]}, {" year ":" 2012 "," period ":" M07 " , "periodName": "July", "value": "275.8", "footnote": [{}]}, {"year": "2012", "period": "M06", "periodName": “June”, “Value”: “226.9”, “Footnotes”: [{}]}, {“Year”: “2012”, “Period d”: “M05”, “PeriodName”: “Ma th "," value ":" 233.7 "," footnotes ": [{}]}, {" year ":" 2012 "," period ":" M04 "," periodName ":" April "," value ":" 239.9 "," footnotes ": [{}]}, {" year ":" 2012 "," period ":" M03 "," periodName ":" March "," value ":" 243, 6 "," footnotes ": [{}}}, {" year ":" 2012 "," period ":" M02 "," periodName ":" February "," value ":" 239.9 "," footnote ": [{}]}, {"year": "2012", "period": "M01", "periodName": "January", "value": "243.8", "footnote": [{}]} ]}]}} ``

I can use the parser to return "status", "responseTime" and "message". Nothing but this (opening the second curly brace) I get nothing.

Below is the code I'm trying to use:

Dim p As Object Set p = JSON.parse(gbl_response) 'Print the text of a nested property ' Debug.Print p.Item("responseTime") 'Print the text of a property within an array ' Debug.Print p.Item("Results").Item("series").Item("seriesID") 

Fingerprint p.Item ("responseTime") works and returns "71", however I get the error "invalid call procedure or argument" in the second print attempt.

In life, I was looking for me and did not find solutions. I tried this one that seemed almost identical, but alas, I tried to reproduce the solution here and didn't seem to work.

Thank you for your help!

Answer 1

 Public Const jsonSource As String = "{" & _ """status"": ""REQUEST_SUCCEEDED"", " & _ """responseTime"": 71, " & _ """message"": [ " & _ "], " & _ """Results"": { " & _ """series"": [ " & _ "{ " & _ """seriesID"": ""WPS012"", " & _ """data"": [ " & _ "{ " & _ """year"": ""2014"", " & _ """period"": ""M11"", " & _ """periodName"": ""November"", " & _ """value"": ""153.6"", " & _ """footnotes"": [ " & _ "{ " & _ """code"": ""P"", " & _ """text"": ""Preliminary. All indexes are subject to revision four months after original publication."" " & _ "} " & _ "] " & _ "} " & _ "] " & _ "}]}}" Sub JsonTest() Dim jsonData As Scripting.Dictionary Set jsonData = JSON.parse(jsonSource) Dim responseTime As String responseTime = jsonData("responseTime") Dim results As Scripting.Dictionary Set results = jsonData("Results") Dim series As Collection Set series = results("series") Dim seriesItem As Scripting.Dictionary For Each seriesItem In series Dim seriesId As String seriesId = seriesItem("seriesID") Debug.Print seriesId Dim data As Collection Set data = seriesItem("data") Dim dataItem As Scripting.Dictionary For Each dataItem In data Dim year As String year = dataItem("year") Dim period As String period = dataItem("period") Dim periodName As String periodName = dataItem("periodName") Dim value As String value = dataItem("value") Dim footnotes As Collection Set footnotes = dataItem("footnotes") Dim footnotesItem As Scripting.Dictionary For Each footnotesItem In footnotes Dim code As String code = footnotesItem("code") Dim text As String text = footnotesItem("text") Next footnotesItem Next dataItem Next seriesItem End Sub