excel 将JSON解析代码转换为数组并获得错误

n7taea2i 于 2023-02-05 发布在其他

关注(0)|答案(1)|浏览(180)

我正在做一个从API中提取数据并通过相关列解析的项目，第一个项目运行得很好，但它的优化速度非常慢。
所以我想把它转换成数组以便快速处理，但是Run-time error 9下标超出了范围
我们将非常感谢您的帮助来解决这个问题。

第一个优化缓慢的代码。

Dim json As Object
Dim timeEntry As Object
Dim ti As Object
Dim lastRow As Long
Dim myValue As String

Set json = JsonConverter.ParseJson(Data)
i = 2
lastRow = Sheet2.Range("A1").End(xlUp).Row

For Each timeEntry In json("timeentries")
    With Sheet2.Cells(i, 1)
        .Value = timeEntry("projectName")
        .Offset(0, 4).Value = timeEntry("taskName")
        .Offset(0, 8).Value = timeEntry("description")
        .Offset(0, 9).Value = timeEntry("clientName")
    End With

    Set ti = timeEntry("timeInterval")
    With Sheet2.Cells(i, 1)
        .Offset(0, 10).Value = ti("start")
        .Offset(0, 6).Value = ti("duration")
    End With
    i = i + 1
Next timeEntry

第二个代码使用数组并获得错误

Dim json As Object
Dim timeEntry As Object
Dim ti As Object
Dim lastRow As Long
Dim myValue As String

Set json = JsonConverter.ParseJson(Data)
i = 2
lastRow = Sheet2.Range("A1").End(xlUp).Row

Dim dataArray() As Variant
ReDim dataArray(1 To lastRow, 1 To 12)

For Each timeEntry In json("timeentries")
    dataArray(i, 1) = timeEntry("projectName")
    dataArray(i, 5) = timeEntry("taskName")
    dataArray(i, 9) = timeEntry("description")
    dataArray(i, 10) = timeEntry("clientName")

    Set ti = timeEntry("timeInterval")
    dataArray(i, 11) = ti("start")
    dataArray(i, 7) = ti("duration")
    i = i + 1
Next timeEntry

Sheet2.Range("A2").Resize(lastRow, 12).Value = dataArray

excel

来源：https://stackoverflow.com/questions/75334752/converted-the-json-parsing-code-to-arrays-and-getting-error

1条答案

按热度按时间

8xiog9wr1#

将数组大小调整为条目数

Sub demo()

    Dim json As Object, t As Object
    Dim data, i As Long, n As Long
    data = "{'timeentries':[" & _
           "{'projectName':'Name1','taskName':'Task1','timeInterval':{'start':'08:00','duration':'123'}}," & _
           "{'projectName':'Name2','taskName':'Task2','timeInterval':{'start':'09:00','duration':'234'}}," & _
           "{'projectName':'Name3','taskName':null,'timeInterval':{'start':'10:00','duration':'345'}}]}"
    
    Set json = JsonConverter.ParseJson(data)
    n = json("timeentries").Count
    If n < 1 Then
        MsgBox "No timeentries in JSON", vbCritical
        Exit Sub
    End If
  
    Dim dataArray() As Variant
    ReDim dataArray(1 To n, 1 To 6)
    
    i = 1
    For Each t In json("timeentries")
        dataArray(i, 1) = t("projectName") '1
        If Not IsNull(t("taskName")) Then
            dataArray(i, 2) = t("taskName") '5
        End If
        dataArray(i, 3) = t("description") '9
        dataArray(i, 4) = t("clientName") '10
        dataArray(i, 5) = t("timeInterval")("start") '11
        dataArray(i, 6) = t("timeInterval")("duration") '77
        i = i + 1
    Next
    
    ' columns
    Dim col: col = Array(1, 5, 9, 10, 11, 7)
    For i = 0 To UBound(col)
        Sheet2.Cells(2, col(i)).Resize(n) = WorksheetFunction.Index(dataArray, 0, i + 1)
    Next

End Sub

赞(0）回复(0）举报 2023-02-05

我来回答

excel 将JSON解析代码转换为数组并获得错误

1条答案

相关问题

热门标签

最新问答