从 MS Access 解析 VBA 中的 JSON (US BLS)

预先感谢您的帮助。

我正在使用 JSON VB6 解析器，可以在以下位置找到它：VB JSON 解析器

我有以下 JSON 响应（来自 BLS 网站，特别是此链接Here:

{“状态”：“REQUEST_SUCCEEDED”，“响应时间”：71，“消息”：[]，“结果”：{ “系列”： [{"seriesID":"WPS012","data":[{"year":"2014","period":"M11","periodName":"11月","value":"153.6","脚注":[{"code":"P","text":"初步。所有索引均会在原始发布后四个月内进行修订。"}]},{"year":"2014","period":" M10","periodName":"10 月","value":"147.4","footnotes":[{"code":"P","text":"初步。所有索引均会在原始索引后四个月内进行修订"}]},{"year":"2014","period":"M09","periodName":"9 月","value":"146.5","footnotes":[{"code": "P","text":"初步。所有索引均会在原始发布后四个月内进行修订。"}]},{"year":"2014","period":"M08","periodName":"八月","value":"156.9","footnotes":[{"code":"P","text":"初步。所有索引均会在原始发布后四个月内进行修订。"}]},{ "year":"2014","period":"M07","periodName":"7 月","value":"156.4","footnotes":[{}]},{"year":"2014" ,"period":"M06","periodName":"6 月","value":"179.6","footnotes":[{}]},{"year":"2014","period":"M05 ","期间名称": "5 月","value":"205.4","footnotes":[{}]},{"year":"2014","period":"M04","periodName":"4 月","value" :"201.6","脚注":[{}]},{"year":"2014","period":"M03","periodName":"3 月","value":"188.1","脚注":[{}]},{"year":"2014","period":"M02","periodName":"二月","value":"180.2","footnotes":[{}]} ,{"year":"2014","period":"M01","periodName":"一月","value":"177.8","footnotes":[{}]},{"year":" 2013","period":"M12","periodName":"12 月","value":"183.2","footnotes":[{}]},{"year":"2013","period": "M11","periodName":"11 月","value":"180.4","footnotes":[{}]},{"year":"2013","period":"M10","periodName" :"10 月","value":"186.4","footnotes":[{}]},{"year":"2013","period":"M09","periodName":"9 月","value ":"197.1","footnotes":[{}]},{"year":"2013","period":"M08","periodName":"8 月","value":"222.2","脚注":[{}]},{"year":"2013","period":"M07","periodName":"7 月","value":"252.9","footnotes":[{}] },{"year":"2013","period":"M06","periodName":"6 月","value":"259.0","footnotes":[{}]},{"year": "2013","期间":"M05","p eriodName":"5 月","value":"263.7","footnotes":[{}]},{"year":"2013","period":"M04","periodName":"4 月", "value":"249.3","footnotes":[{}]},{"year":"2013","period":"M03","periodName":"3 月","value":"268.1" ,"footnotes":[{}]},{"year":"2013","period":"M02","periodName":"二月","value":"267.1","footnotes":[{ }]},{"year":"2013","period":"M01","periodName":"一月","value":"279.7","footnotes":[{}]},{"year ":"2012","period":"M12","periodName":"12 月","value":"283.2","footnotes":[{}]},{"year":"2012"," period":"M11","periodName":"11 月","value":"280.8","footnotes":[{}]},{"year":"2012","period":"M10", "periodName":"10 月","value":"286.7","footnotes":[{}]},{"year":"2012","period":"M09","periodName":"9 月" ,"value":"285.2","footnotes":[{}]},{"year":"2012","period":"M08","periodName":"8 月","value":"298.9 ","脚注":[{}]},{"year":"2012","period":"M07","periodName":"7月","value":"275.8","footnotes":[ {}]},{"year":"2012","period":"M06","periodName":"6 月","value":"226.9","footnotes":[{}]},{"年份":"2012","期间 d":"M05","periodName":"5 月","value":"233.7","footnotes":[{}]},{"year":"2012","period":"M04", "periodName":"4 月","value":"239.9","footnotes":[{}]},{"year":"2012","period":"M03","periodName":"3 月" ,"value":"243.6","footnotes":[{}]},{"year":"2012","period":"M02","periodName":"二月","value":"239.9 ","脚注":[{}]},{"year":"2012","period":"M01","periodName":"1月","value":"243.8","footnotes":[ {}]}]}] }}`

我能够使用解析器返回“status”、“responseTime”和“message”。除此之外（第二个花括号的开头）我什么也得不到。

下面是我尝试使用的代码：

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")

p.Item("responseTime") 的打印有效并返回“71”，但是我在第二次打印尝试时收到“无效的调用过程或参数”错误。

在我的一生中，我四处寻找，但没有找到任何解决方案。我试过了this这看起来几乎相同，但可惜的是，我尝试在这里复制该解决方案，但它似乎不起作用。

谢谢您的帮助！

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