json - 将 JSON 数据解析为 Excel 工作表

Question

我正在尝试使用以下代码将 JSON 数据作为表格提取到 Excel 工作表中。

Sub test()
    Dim httpObject As Object
    Set httpObject = CreateObject("MSXML2.XMLHTTP")

    sURL = "https://www.nseindia.com/live_market/dynaContent/live_watch/stock_watch/foSecStockWatch.json"

    sRequest = sURL
    httpObject.Open "GET", sRequest, False
    httpObject.send
    sGetResult = httpObject.responseText

    Dim oJSON As Object
    Set oJSON = JsonConverter.ParseJson(sGetResult)

    i = 2

    For Each sItem In oJSON
        dItemString = oJSON(sItem)("symbol")
        sItemValue = oJSON(sItem)("open")
        vItemValue = oJSON(sItem)("high")
        xItemValue = oJSON(sItem)("low")
        Cells(i, 1) = dItemString
        Cells(i, 2) = sItemValue
        Cells(i, 3) = vItemValue
        Cells(i, 4) = xItemValue
        i = i + 1
    Next
End Sub

但是，我收到以下错误!

为什么我会收到此错误？请指教

Answer 1

首先，您需要使用任何在线 JSON 查看器(例如 http://jsonviewer.stack.hu/ )检查 JSON 响应的结构，您可以在其中看到 JSON 对象包含 data 数组，以及几个具有标量值的属性:

更进一步，data 数组中有一些对象，每个对象都包含一些可以填充到工作表上的行中的属性:

以下 VBA 示例显示了如何检索该值。 导入JSON.bas模块到 VBA 项目中进行 JSON 处理。

Option Explicit

Sub Test()

    Dim sJSONString As String
    Dim vJSON
    Dim sState As String
    Dim aData()
    Dim aHeader()
    Dim vResult

    ' Retrieve JSON content
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.nseindia.com/live_market/dynaContent/live_watch/stock_watch/foSecStockWatch.json", True
        .send
        Do Until .readyState = 4: DoEvents: Loop
        sJSONString = .responseText
    End With
    ' Parse JSON sample
    JSON.Parse sJSONString, vJSON, sState
    If sState = "Error" Then MsgBox "Invalid JSON": End
    ' Convert raw JSON to 2d array and output to worksheet #1
    JSON.ToArray vJSON("data"), aData, aHeader
    With ThisWorkbook.Sheets(1)
        .Cells.Delete
        .Cells.WrapText = False
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With
    MsgBox "Completed"

End Sub

Sub OutputArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

我的data数组的输出如下:

顺便说一句，应用了类似的方法 in other answers .