json - 如何使用 VBA 检索 JSON 响应？

Question

我向网站发出请求并将 JSON 响应粘贴到单个单元格中。

我得到一个对象需要 424 错误。

Sub GetJSON()

Dim hReq As Object
Dim JSON As Dictionary
Dim var As Variant
Dim ws As Worksheet

Set ws = Title

'create our URL string and pass the user entered information to it
Dim strUrl As String
strUrl = Range("M24").Value

Set hReq = CreateObject("MSXML2.XMLHTTP")
With hReq
    .Open "GET", strUrl, False
    .Send
End With

'wrap the response in a JSON root tag "data" to count returned objects
Dim response As String
response = "{""data"":" & hReq.responseText & "}"

Set JSON = JsonConverter.ParseJson(response)

'set array size to accept all returned objects
ReDim var(JSON("data").Count, 1)

Cells(25, 13) = JSON

Erase var
Set var = Nothing
Set hReq = Nothing
Set JSON = Nothing

End Sub

在单元格“M24”中为我提供响应的 URL:

https://earthquake.usgs.gov/ws/designmaps/asce7-10.json?latitude=36.497452&longitude=-86.949479&riskCategory=III&siteClass=C&title=Seismic

Qharr 回复后的代码。即使错误表明它运行成功，我也会收到运行时 0 错误。没有任何内容被复制到我的单元格中。

Public Sub GetInfo()
    Dim URL As String, json As Object
    Dim dict As Object
    URL = "https://earthquake.usgs.gov/ws/designmaps/asce7-10.json?latitude=36.497452&longitude=-86.949479&riskCategory=III&siteClass=C&title=Seismic"
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .Send
        Set json = JsonConverter.ParseJson(.responseText) '<== dictionary
        ThisWorkbook.Worksheets("Title").Cells(1, 1) = .responseText
        Set dict = json("response")("data")
        ws.Cells(13, 27) = "ss: " & dict("ss") & Chr$(10) & "s1: " & dict("s1")
    End With
End Sub

Answer 1

我不清楚你的意思。整个响应可以按如下方式进入单元格。
JSON 是一个对象，因此您需要 Set关键字，但您不能为字典对象设置单元格范围 - 错误的来源。

Option Explicit

Public Sub GetInfo()
    Dim URL As String, json As Object
    URL = "https://earthquake.usgs.gov/ws/designmaps/asce7-10.json?latitude=36.497452&longitude=-86.949479&riskCategory=III&siteClass=C&title=Seismic"
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .send
        Set json = JsonConverter.ParseJson(.responseText) '<== dictionary
         ThisWorkbook.Worksheets("Sheet1").Cells(1, 1) = .responseText
    End With
End Sub

当您使用 parsejson 时，您将转换为需要处理的字典对象。内部嵌套的数据太多，无法将任何可读的内容(如果不超过限制)写入一个单元格。

内词典 data迅速下降到嵌套集合。嵌套集合计数来自

Dim dict As Object
Set dict = json("response")("data")
Debug.Print "nested collection count = " & dict("sdSpectrum").Count + dict("smSpectrum").Count

要仅获取 s1 和 ss 值，请将它们解析出来:

Dim dict As Object
Set dict = json("response")("data")
ws.Cells(1, 2) = "ss: " & dict("ss") & Chr$(10) & "s1: " & dict("s1")