从MS Access在VBA中解析JSON(US BLS) [英] Parsing JSON (US BLS) in VBA from MS Access

问题描述

在此先感谢您的帮助.

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

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

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

I have the following JSON response (Comes from the BLS website, specifically this link Here:

{状态":"REQUEST_SUCCEEDED"，响应时间":71，消息":[]，结果":{ 系列": [{"seriesID":"WPS012"，"data":[{"year":"2014"，"period":"M11"，"periodName":"11月"，"value":"153.6"，脚注:[{" code:" P，" text:"初步.所有索引均应在原始发布后的四个月内进行修订.}]}，{" year:" 2014，" period:" M10，" periodName:"十月，" value:" 147.4，"脚注:[{" code:" P，" text:"初步.所有索引均应在原始版本四个月后进行修订Publication.}]}，{" year:" 2014，" period:" M09，" periodName:" September，" value:" 146.5，" footnotes:[{" code: "P"，"text":初步.所有索引都会在原始发布后的四个月内进行修订."}]}，{"year":"2014"，"period":"M08"，"periodName":"August，" value:" 156.9，" footnotes:[{" code:" P，" text:"初稿.所有索引均应在原始出版后的四个月内进行修订.}]}，{ "year":"2014"，"period":"M07"，"periodName":"July"，"value":"156.4"，脚注":[{}]}，{"year":"2014" ，"period":"M06"，"periodName":"June"，"value":"179.6"，"footnotes":[{}]}，{"year":"2014"，"peri od:" M05，" periodName: "May"，"value":"205.4"，"footnotes":[{}]}，{"year":"2014"，"period":"M04"，"periodName":"April"，"value" :"201.6"，脚注":[{}]}，{"year":"2014"，句号":"M03"，"periodName":三月"，值":"188.1"，脚注:[{}]}，{" year:" 2014，" period:" M02，" periodName:" February，" value:" 180.2，" footnotes:[{}]} ，{"year":"2014"，"period":"M01"，"periodName":一月"，"value":"177.8"，脚注":[{}]}，{"year":"2013"​​，句号":"M12"，"periodName":"December"，"value":"183.2"，"footnotes":[{}]}，{"year":"2013"​​，"period": "M11"，"periodName":十一月"，值":"180.4"，脚注":[{}]}，{"year":"2013"​​，"period":"M10"，"periodName" :十月"，值":"186.4"，脚注":[{}]}，{年":"2013"​​，句号":"M09"，"periodName":九月"，值:" 197.1，"脚注:[{}]}，{" 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，"句号:" M05，" p eriodName:"五月，" value:" 263.7，"脚注:[{}]}，{" year:" 2013，" period:" M04，" periodName:" April， "value":"249.3"，脚注":[{}]}，{"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，"句点:" M12，" periodName:"十二月，"值:" 283.2，"脚注:[{}]}，{" year:" 2012，" period:" M11，" periodName:" 11月，" value:" 280.8，"脚注:[{}]}，{" year:" 2012，" period:" M10， "periodName":十月"，"value":"286.7"，脚注":[{}]}，{"year":"2012"，"period":"M09"，"periodName":"September" ，"value":"285.2"，脚注":[{}]}，{"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，"脚注:[{}]}，{" year:" 2012，" period:" M04， "periodName":"April"，"value":"239.9"，脚注":[{}]}，{"year":"2012"，"period":"M03"，"periodName":"March" ，值":"243.6"，脚注":[{}]}，{年":"2012"，句号":"M02"，句号名称":二月"，值":"239.9" ，" footnotes:[{}]}，{" year:" 2012，" period:" M01，" periodName:" January，" value:" 243.8，" footnotes:[ {}]}]}] }}`

{"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":[{}]}]}] }}`

我能够使用解析器返回状态"，"responseTime"和消息".除此之外(第二个花括号的开头)，我什么也没得到.

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

p.Item("responseTime")的打印工作并返回"71"，但是在第二次尝试打印时出现无效的调用过程或参数"错误.

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.

感谢您的协助！

推荐答案

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

这篇关于从MS Access在VBA中解析JSON(US BLS)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！