Excel VBA解析API返回JSON字符串并填充表格单元格求助

1. 确认JSON解析依赖是否正确配置 #

VBA无原生JSON解析能力，必须依赖第三方工具，常见两种方案：

• VBA-JSON库：需先导入JsonConverter.bas模块。导入后用JsonConverter.ParseJson()解析字符串。
• Microsoft Script Control：在VBA编辑器的「工具」→「引用」中勾选「Microsoft Script Control 6.0」，通过ScriptControl.Eval()解析JSON。

未配置依赖直接用Dim data As Object会导致解析失败。

2. 校验JSON字符串的有效性 #

将API返回的JSON字符串复制到JSON校验工具中检查：

• 确认无语法错误（如引号不闭合、末尾多余逗号、数组/对象嵌套错误）
• 确认顶层结构是数组（对应你提到的「数百条作业数据的JSON数组」），而非单个对象。

3. 正确解析并遍历JSON数组 #

根据使用的解析工具，调整遍历逻辑：

示例1：使用VBA-JSON #

Sub ParseJsonWithVBAJSON()
    Dim jsonString As String
    jsonString = "你的API返回的JSON数组字符串" ' 替换为实际获取的字符串
    
    Dim jsonData As Collection
    Set jsonData = JsonConverter.ParseJson(jsonString) ' 解析为Collection（对应JSON数组）
    
    Dim rowNum As Integer
    rowNum = 2 ' 从第2行开始填充（第1行是表头）
    
    Dim jobItem As Object
    For Each jobItem In jsonData
        ' 字段名必须与JSON中的键完全匹配（包括大小写、空格）
        Cells(rowNum, 1).Value = jobItem("Job number")
        Cells(rowNum, 2).Value = jobItem("Status")
        Cells(rowNum, 3).Value = jobItem("Actual Hrs")
        Cells(rowNum, 4).Value = jobItem("% Complete")
        rowNum = rowNum + 1
    Next jobItem
End Sub

示例2：使用Microsoft Script Control #

Sub ParseJsonWithScriptControl()
    Dim jsonString As String
    jsonString = "你的API返回的JSON数组字符串"
    
    Dim sc As New ScriptControl
    sc.Language = "JScript"
    
    ' 解析JSON数组，转换为Variant数组
    Dim jsonData As Variant
    jsonData = sc.Eval("(" & jsonString & ")")
    
    Dim rowNum As Integer
    rowNum = 2
    
    Dim i As Integer
    For i = 0 To UBound(jsonData) ' JScript数组是0索引
        Cells(rowNum, 1).Value = jsonData(i)("Job number")
        Cells(rowNum, 2).Value = jsonData(i)("Status")
        Cells(rowNum, 3).Value = jsonData(i)("Actual Hrs")
        Cells(rowNum, 4).Value = jsonData(i)("% Complete")
        rowNum = rowNum + 1
    Next i
End Sub

4. 排查字段匹配与赋值问题 #

• 确认JSON中的键名与表格列名完全一致，包括空格、大小写（如JSON里是jobNumber还是Job number，必须严格对应）。
• 检查单元格赋值逻辑：确保行号rowNum在每次循环后递增，列索引对应正确（第1列对应Cells(rowNum,1)）。
• 若Actual Hrs或% Complete是数值类型，确认JSON返回的是数字而非字符串，避免赋值后显示为文本。

5. 调试技巧 #

• 在解析后添加Debug.Print输出，比如Debug.Print jsonData.Count确认数组长度，或Debug.Print jobItem("Job number")查看单个字段值，判断是解析失败还是赋值逻辑错误。
• 若解析时抛出错误，检查JSON字符串是否包含特殊字符（如换行、转义符），可先清理：jsonString = Replace(jsonString, vbCrLf, "")。

内容的提问来源于stack exchange，提问作者Mathewjl