Parsing JSON (US BLS) in VBA from MS Access

Question

Thank you in advance for your assistance.

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

I have the following JSON response (Comes from the BLS website, specifically this link 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":"Preliminary. All indexes are subject to revision four months after original publication."}]},{"year":"2014","period":"M10","periodName":"October","value":"147.4","footnotes":[{"code":"P","text":"Preliminary. All indexes are subject to revision four months after original publication."}]},{"year":"2014","period":"M09","periodName":"September","value":"146.5","footnotes":[{"code":"P","text":"Preliminary. All indexes are subject to revision four months after original publication."}]},{"year":"2014","period":"M08","periodName":"August","value":"156.9","footnotes":[{"code":"P","text":"Preliminary. All indexes are subject to revision four months after original publication."}]},{"year":"2014","period":"M07","periodName":"July","value":"156.4","footnotes":[{}]},{"year":"2014","period":"M06","periodName":"June","value":"179.6","footnotes":[{}]},{"year":"2014","period":"M05","periodName": "May","value":"205.4","footnotes":[{}]},{"year":"2014","period":"M04","periodName":"April","value":"201.6","footnotes":[{}]},{"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":"2013","period":"M09","periodName":"September","value":"197.1","footnotes":[{}]},{"year":"2013","period":"M08","periodName":"August","value":"222.2","footnotes":[{}]},{"year":"2013","period":"M07","periodName":"July","value":"252.9","footnotes":[{}]},{"year":"2013","period":"M06","periodName":"June","value":"259.0","footnotes":[{}]},{"year":"2013","period":"M05","p eriodName":"May","value":"263.7","footnotes":[{}]},{"year":"2013","period":"M04","periodName":"April","value":"249.3","footnotes":[{}]},{"year":"2013","period":"M03","periodName":"March","value":"268.1","footnotes":[{}]},{"year":"2013","period":"M02","periodName":"February","value":"267.1","footnotes":[{}]},{"year":"2013","period":"M01","periodName":"January","value":"279.7","footnotes":[{}]},{"year":"2012","period":"M12","periodName":"December","value":"283.2","footnotes":[{}]},{"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","footnotes":[{}]},{"year":"2012","period":"M07","periodName":"July","value":"275.8","footnotes":[{}]},{"year":"2012","period":"M06","periodName":"June","value":"226.9","footnotes":[{}]},{"year":"2012","perio d":"M05","periodName":"May","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","footnotes":[{}]},{"year":"2012","period":"M01","periodName":"January","value":"243.8","footnotes":[{}]}]}] }}`

I am able to use the parser to return "status", "responseTime" and "message". Anything beyond that (the opening of the second curly bracket) I get nothing.

Below is the code I am 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")

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

For the life of me, I've searched around and have not found any solutions. I've tried this which seemed almost identical, but alas, I've tried to replicate the solution here and it seems to have not worked.

Thank you for you assistance!

score 4 · Accepted Answer · answered Dec 31 '14 at 18:53

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

enter image description here

dee, you are awesome, that works just as I was hoping. And you've saved my brain from melting with resonable and understandable coding. Thank you! — Dm3k1, Dec 31 '14 at 19:59

Parsing JSON (US BLS) in VBA from MS Access

1 Answers1

Linked