用vba将json文件提取到excel表格中

Option Explicit

Sub ExtractJSONToExcel()
    Dim filePath As String
    Dim fileContent As String
    Dim jsonObject As Object
    Dim dataArr As Variant
    Dim rowIndex As Long
    Dim colIndex As Long
    
    ' 选择JSON文件路径
    filePath = Application.GetOpenFilename("JSON Files (*.json), *.json")
    
    ' 判断是否选择了文件
    If filePath <> "False" Then
        ' 读取文件内容
        Open filePath For Input As #1
        fileContent = Input$(LOF(1), 1)
        Close #1
        
        ' 解析JSON
        Set jsonObject = JsonConverter.ParseJson(fileContent)
        
        ' 定义表格起始行号和列号
        rowIndex = 1
        colIndex = 1
        
        ' 遍历JSON数据
        For Each dataArr In jsonObject
            ' 将数据写入Excel表格
            Cells(rowIndex, colIndex).Value = dataArr("key1")
            Cells(rowIndex, colIndex + 1).Value = dataArr("key2")
            ' 继续添加其他需要提取的数据字段
            
            ' 更新行号
            rowIndex = rowIndex + 1
        Next dataArr
        
        MsgBox "JSON数据提取完成！"
    Else
        MsgBox "未选择文件！"
    End If
End Sub