vba解析JSON字符串

vba解析JSON字符串

vba解析JSON大概有4种方法

1、htmlfile对象解析json（支持32位和64位系统）

思路：创建htmlfile对象，使用write方法写入浏览器版本，创建parentwindow对象，在使用execscript对象执行js代码，使用eval对象根据属性名获取属性值

'封装提取JSON的函数，可复制函数到vba中，直接调用函数名
Function getJsonAttribute(jsonstr, expre As String) 'expre填写规则：【jstr.属性名...】，jstr是根对象,可以不需要填写，直接【属性...】
    Dim oHtml As Object
    '定义HtmlDocument对象
    Set oHtml = CreateObject("htmlfile")
    '设置IE浏览器版本
    oHtml.write "<meta http-equiv='X-UA-Compatible'content='IE=8'\>"
    Dim oWindow, s01, s02, s03 As Object
    Set oWindow = oHtml.parentWindow
    With oWindow
        .execScript "var jstr=" & jsonstr'将json字符串加入js代码中，声明变量jstr
    End With
    'Set s01 = oWindow.eval("JSON.stringify(jstr,null,2)")
    'Dim s04 As String
    's04 = oWindow.eval("JSON.stringify(jstr,null,2)")
    'Debug.Print s04
    'Set s03 = oWindow.arr03
    getJsonAttribute = oWindow.eval("jstr." & expre)'属性值表达式，获取值赋给函数返回
    Set oHtml = Nothing
    Set oWindow = Nothing
End Function

2、ScriptControl对象解析json

这里代码引用网友的帖子

Sub TestJson()
 Dim jsstr As String
 'vb搞个字符串要加n个引号，真是烦人
 jsstr = "{""系别"":""历史系"",""班级"":""一班""," & _
          """学员"":[{""姓名"":""张三"",""年龄"":25,""性别"":""男""}," & _
                     "{""姓名"":""李四"",""年龄"":20,""性别"":""男""}," & _
                     "{""姓名"":""小明"",""年龄"":20,""性别"":""女""}" & _
                    "]" & _
          "}"      
 Dim age As Integer
 Set scobj = CreateObject("ScriptControl")        
'调用MSScriptControl.ScriptControl对象将提取的变量文本运算形成对象集合    
'ScriptControl使用的脚本语言。除了js，也支持Vbscript
scobj.Language = "JavaScript"
'往脚本里添加代码，参数是字符串
scobj.AddCode ("var query = " & jsstr)
'JSON对象获取属性的表示方法：对象.属性
'属性的值如果是个包含多个对象的数组，可以使用索引表示取得对象：对象.属性[0]
'Eval是表达式求值
age = scobj.Eval("query.学员[2].年龄")
Debug.Print "历史系一班的第3个学员的年龄是" & age & "岁"      
End Sub

3、使用split函数解析json字符串：此方法是分割字符串
JSON测试文件:有3个，可以任选一个测试
One

    {
      "id": 1,
      "name": "John",
      "age": 30,
      "department": "Sales",
      "products": 
    {
      "id": 2,
      "name": "Mouse",
      "price": 12.99
    }
    },
    {
      "id": 2,
      "name": "Alice",
      "age": 35,
      "department": "Marketing",
      "products": 
    {
      "id": 2,
      "name": "Mouse",
      "price": 12.99
    }
    }
  

Two

    {
      "name": "John Doe",
      "math": 90,
      "science": 85,
      "english": 92
    },
    {
      "name": "Jane Smith",
      "math": 95,
      "science": 88,
      "english": 91
    },
    {
      "name": "Mike Johnson",
      "math": 85,
      "science": 92,
      "english": 89
    },
    {
      "name": "Emily Brown",
      "math": 88,
      "science": 90,
      "english": 93
    },
    {
      "name": "David Wilson",
      "math": 82,
      "science": 87,
      "english": 90
    },
    {
      "name": "Sarah Davis",
      "math": 91,
      "science": 89,
      "english": 94
    },
    {
      "name": "Daniel Martinez",
      "math": 94,
      "science": 92,
      "english": 86
    },
    {
      "name": "Olivia Anderson",
      "math": 87,
      "science": 91,
      "english": 88
    },
    {
      "name": "Michael Thomas",
      "math": 90,
      "science": 93,
      "english": 85
    },
    {
      "name": "Sophia Taylor",
      "math": 95,
      "science": 85,
      "english": 92
    },
    {
      "name": "Christopher Clark",
      "math": 89,
      "science": 88,
      "english": 91
    },
    {
      "name": "Ava Rodriguez",
      "math": 93,
      "science": 92,
      "english": 89
    },
    {
      "name": "Matthew Turner",
      "math": 86,
      "science": 90,
      "english": 93
    },
    {
      "name": "Isabella Walker",
      "math": 91,
      "science": 87,
      "english": 90
    },
    {
      "name": "James Hill",
      "math": 89,
      "science": 91,
      "english": 94
    },
    {
      "name": "Grace Lopez",
      "math": 90,
      "science": 94,
      "english": 92
    },
    {
      "name": "Benjamin Green",
      "math": 92,
      "science": 86,
      "english": 89
    },
    {
      "name": "Chloe Hall",
      "math": 87,
      "science": 90,
      "english": 88
    },
    {
      "name": "Logan Young",
      "math": 88,
      "science": 92,
      "english": 85
    },
    {
      "name": "Emma King",
      "math": 94,
      "science": 89,
      "english": 93
    },
    {
      "name": "Jacob Baker",
      "math": 85,
      "science": 91,
      "english": 90
    }

Three

    {
      "id": 1,
      "name": "John",
      "age": 25,
      "gender": "Male",
      "email": "john@example.com",
      "phone": "123-456-7890",
      "address": "123 Main Street",
      "city": "New York",
      "state": "NY",
      "country": "USA",
      "occupation": "Engineer",
      "salary": 50000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Reading", "Traveling", "Playing Guitar"],
      "active": true
    },
    {
      "id": 2,
      "name": "Jane",
      "age": 30,
      "gender": "Female",
      "email": "jane@example.com",
      "phone": "987-654-3210",
      "address": "456 Oak Street",
      "city": "Los Angeles",
      "state": "CA",
      "country": "USA",
      "occupation": "Teacher",
      "salary": 40000,
      "education": "Master's Degree",
      "marital_status": "Married",
      "hobbies": ["Cooking", "Hiking", "Photography"],
      "active": true
    },
    {
      "id": 3,
      "name": "Michael",
      "age": 28,
      "gender": "Male",
      "email": "michael@example.com",
      "phone": "555-123-4567",
      "address": "789 Elm Street",
      "city": "Chicago",
      "state": "IL",
      "country": "USA",
      "occupation": "Accountant",
      "salary": 60000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Sports", "Movies", "Painting"],
      "active": false
    },
    {
      "id": 4,
      "name": "Emily",
      "age": 32,
      "gender": "Female",
      "email": "emily@example.com",
      "phone": "999-888-7777",
      "address": "567 Maple Avenue",
      "city": "San Francisco",
      "state": "CA",
      "country": "USA",
      "occupation": "Doctor",
      "salary": 90000,
      "education": "Doctorate Degree",
      "marital_status": "Married",
      "hobbies": ["Yoga", "Gardening", "Volunteering"],
      "active": true
    },
    {
      "id": 5,
      "name": "David",
      "age": 27,
      "gender": "Male",
      "email": "david@example.com",
      "phone": "777-555-4444",
      "address": "321 Pine Street",
      "city": "Seattle",
      "state": "WA",
      "country": "USA",
      "occupation": "Software Developer",
      "salary": 70000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Music", "Photography", "Cooking"],
      "active": true
    },
    {
      "id": 6,
      "name": "Sarah",
      "age": 29,
      "gender": "Female",
      "email": "sarah@example.com",
      "phone": "222-333-4444",
      "address": "987 Cedar Avenue",
      "city": "Boston",
      "state": "MA",
      "country": "USA",
      "occupation": "Marketing Manager",
      "salary": 80000,
      "education": "Master's Degree",
      "marital_status": "Single",
      "hobbies": ["Reading", "Traveling", "Running"],
      "active": true
    },
    {
      "id": 7,
      "name": "Daniel",
      "age": 31,
      "gender": "Male",
      "email": "daniel@example.com",
      "phone": "888-999-1111",
      "address": "555 Walnut Street",
      "city": "Houston",
      "state": "TX",
      "country": "USA",
      "occupation": "Sales Manager",
      "salary": 75000,
      "education": "Bachelor's Degree",
      "marital_status": "Married",
      "hobbies": ["Sports", "Movies", "Golf"],
      "active": true
    },
    {
      "id": 8,
      "name": "Olivia",
      "age": 26,
      "gender": "Female",
      "email": "olivia@example.com",
      "phone": "666-777-8888",
      "address": "234 Willow Street",
      "city": "Miami",
      "state": "FL",
      "country": "USA",
      "occupation": "Graphic Designer",
      "salary": 55000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Drawing", "Photography", "Yoga"],
      "active": true
    },
    {
      "id": 9,
      "name": "William",
      "age": 33,
      "gender": "Male",
      "email": "william@example.com",
      "phone": "444-555-6666",
      "address": "876 Oak Street",
      "city": "Dallas",
      "state": "TX",
      "country": "USA",
      "occupation": "Engineer",
      "salary": 65000,
      "education": "Master's Degree",
      "marital_status": "Married",
      "hobbies": ["Music", "Cooking", "Hiking"],
      "active": false
    },
    {
      "id": 10,
      "name": "Sophia",
      "age": 24,
      "gender": "Female",
      "email": "sophia@example.com",
      "phone": "111-222-3333",
      "address": "345 Oak Avenue",
      "city": "Denver",
      "state": "CO",
      "country": "USA",
      "occupation": "Data Analyst",
      "salary": 60000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Reading", "Traveling", "Painting"],
      "active": true
    },
    {
      "id": 11,
      "name": "Matthew",
      "age": 27,
      "gender": "Male",
      "email": "matthew@example.com",
      "phone": "333-444-5555",
      "address": "876 Maple Street",
      "city": "Phoenix",
      "state": "AZ",
      "country": "USA",
      "occupation": "Financial Analyst",
      "salary": 65000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Sports", "Movies", "Photography"],
      "active": true
    },
    {
      "id": 12,
      "name": "Emma",
      "age": 29,
      "gender": "Female",
      "email": "emma@example.com",
      "phone": "222-333-4444",
      "address": "123 Elm Avenue",
      "city": "Atlanta",
      "state": "GA",
      "country": "USA",
      "occupation": "HR Manager",
      "salary": 70000,
      "education": "Master's Degree",
      "marital_status": "Single",
      "hobbies": ["Cooking", "Reading", "Yoga"],
      "active": true
    },
    {
      "id": 13,
      "name": "James",
      "age": 31,
      "gender": "Male",
      "email": "james@example.com",
      "phone": "999-888-7777",
      "address": "456 Oak Avenue",
      "city": "Las Vegas",
      "state": "NV",
      "country": "USA",
      "occupation": "Business Analyst",
      "salary": 75000,
      "education": "Bachelor's Degree",
      "marital_status": "Married",
      "hobbies": ["Golf", "Traveling", "Cooking"],
      "active": true
    },
    {
      "id": 14,
      "name": "Grace",
      "age": 25,
      "gender": "Female",
      "email": "grace@example.com",
      "phone": "777-555-4444",
      "address": "789 Pine Street",
      "city": "Orlando",
      "state": "FL",
      "country": "USA",
      "occupation": "Software Engineer",
      "salary": 80000,
      "education": "Master's Degree",
      "marital_status": "Single",
      "hobbies": ["Music", "Photography", "Hiking"],
      "active": true
    },
    {
      "id": 15,
      "name": "Alexander",
      "age": 28,
      "gender": "Male",
      "email": "alexander@example.com",
      "phone": "555-123-4567",
      "address": "234 Walnut Avenue",
      "city": "Austin",
      "state": "TX",
      "country": "USA",
      "occupation": "Project Manager",
      "salary": 90000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Sports", "Movies", "Traveling"],
      "active": false
    },
    {
      "id": 16,
      "name": "Ava",
      "age": 30,
      "gender": "Female",
      "email": "ava@example.com",
      "phone": "987-654-3210",
      "address": "321 Cedar Street",
      "city": "San Diego",
      "state": "CA",
      "country": "USA",
      "occupation": "Marketing Coordinator",
      "salary": 55000,
      "education": "Bachelor's Degree",
      "marital_status": "Married",
      "hobbies": ["Reading", "Hiking", "Photography"],
      "active": true
    },
    {
      "id": 17,
      "name": "Ryan",
      "age": 26,
      "gender": "Male",
      "email": "ryan@example.com",
      "phone": "666-777-8888",
      "address": "567 Pine Avenue",
      "city": "Portland",
      "state": "OR",
      "country": "USA",
      "occupation": "Graphic Designer",
      "salary": 60000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Drawing", "Music", "Cooking"],
      "active": true
    },
    {
      "id": 18,
      "name": "Lily",
      "age": 32,
      "gender": "Female",
      "email": "lily@example.com",
      "phone": "444-555-6666",
      "address": "876 Oak Avenue",
      "city": "Charlotte",
      "state": "NC",
      "country": "USA",
      "occupation": "Accountant",
      "salary": 70000,
      "education": "Master's Degree",
      "marital_status": "Married",
      "hobbies": ["Yoga", "Reading", "Traveling"],
      "active": true
    },
    {
      "id": 19,
      "name": "Benjamin",
      "age": 27,
      "gender": "Male",
      "email": "benjamin@example.com",
      "phone": "111-222-3333",
      "address": "345 Elm Street",
      "city": "Raleigh",
      "state": "NC",
      "country": "USA",
      "occupation": "Software Developer",
      "salary": 80000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Music", "Photography", "Cooking"],
      "active": false
    },
    {
      "id": 20,
      "name": "Chloe",
      "age": 29,
      "gender": "Female",
      "email": "chloe@example.com",
      "phone": "333-444-5555",
      "address": "987 Maple Avenue",
      "city": "Nashville",
      "state": "TN",
      "country": "USA",
      "occupation": "Teacher",
      "salary": 55000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Reading", "Traveling", "Painting"],
      "active": true
    },
    {
      "id": 21,
      "name": "Henry",
      "age": 31,
      "gender": "Male",
      "email": "henry@example.com",
      "phone": "999-888-7777",
      "address": "456 Oak Street",
      "city": "Minneapolis",
      "state": "MN",
      "country": "USA",
      "occupation": "Doctor",
      "salary": 90000,
      "education": "Doctorate Degree",
      "marital_status": "Married",
      "hobbies": ["Sports", "Hiking", "Gardening"],
      "active": true
    },
    {
      "id": 22,
      "name": "Mia",
      "age": 25,
      "gender": "Female",
      "email": "mia@example.com",
      "phone": "777-555-4444",
      "address": "789 Pine Avenue",
      "city": "Detroit",
      "state": "MI",
      "country": "USA",
      "occupation": "Engineer",
      "salary": 65000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Music", "Cooking", "Photography"],
      "active": true
    },
    {
      "id": 23,
      "name": "Ethan",
      "age": 28,
      "gender": "Male",
      "email": "ethan@example.com",
      "phone": "555-123-4567",
      "address": "234 Walnut Street",
      "city": "Philadelphia",
      "state": "PA",
      "country": "USA",
      "occupation": "Marketing Manager",
      "salary": 75000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Sports", "Movies", "Traveling"],
      "active": true
    },
    {
      "id": 24,
      "name": "Avery",
      "age": 30,
      "gender": "Female",
      "email": "avery@example.com",
      "phone": "987-654-3210",
      "address": "321 Cedar Avenue",
      "city": "Columbus",
      "state": "OH",
      "country": "USA",
      "occupation": "Sales Manager",
      "salary": 70000,
      "education": "Bachelor's Degree",
      "marital_status": "Married",
      "hobbies": ["Reading", "Cooking", "Golf"],
      "active": true
    },
    {
      "id": 25,
      "name": "James",
      "age": 26,
      "gender": "Male",
      "email": "james@example.com",
      "phone": "666-777-8888",
      "address": "567 Pine Street",
      "city": "Indianapolis",
      "state": "IN",
      "country": "USA",
      "occupation": "Graphic Designer",
      "salary": 60000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Drawing", "Music", "Reading"],
      "active": false
    },
    {
      "id": 26,
      "name": "Ella",
      "age": 32,
      "gender": "Female",
      "email": "ella@example.com",
      "phone": "444-555-6666",
      "address": "876 Oak Avenue",
      "city": "Kansas City",
      "state": "MO",
      "country": "USA",
      "occupation": "Accountant",
      "salary": 65000,
      "education": "Master's Degree",
      "marital_status": "Married",
      "hobbies": ["Yoga", "Cooking", "Traveling"],
      "active": true
    },
    {
      "id": 27,
      "name": "Daniel",
      "age": 27,
      "gender": "Male",
      "email": "daniel@example.com",
      "phone": "111-222-3333",
      "address": "345 Elm Street",
      "city": "Salt Lake City",
      "state": "UT",
      "country": "USA",
      "occupation": "Software Developer",
      "salary": 80000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Music", "Photography", "Hiking"],
      "active": true
    },
    {
      "id": 28,
      "name": "Victoria",
      "age": 29,
      "gender": "Female",
      "email": "victoria@example.com",
      "phone": "333-444-5555",
      "address": "987 Maple Avenue",
      "city": "Seattle",
      "state": "WA",
      "country": "USA",
      "occupation": "Teacher",
      "salary": 55000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Reading", "Traveling", "Painting"],
      "active": true
    },
    {
      "id": 29,
      "name": "Michael",
      "age": 31,
      "gender": "Male",
      "email": "michael@example.com",
      "phone": "999-888-7777",
      "address": "456 Oak Street",
      "city": "Chicago",
      "state": "IL",
      "country": "USA",
      "occupation": "Doctor",
      "salary": 90000,
      "education": "Doctorate Degree",
      "marital_status": "Married",
      "hobbies": ["Sports", "Cooking", "Gardening"],
      "active": true
    },
    {
      "id": 30,
      "name": "Sofia",
      "age": 25,
      "gender": "Female",
      "email": "sofia@example.com",
      "phone": "777-555-4444",
      "address": "789 Pine Avenue",
      "city": "San Francisco",
      "state": "CA",
      "country": "USA",
      "occupation": "Engineer",
      "salary": 65000,
      "education": "Bachelor's Degree",
      "marital_status": "Single",
      "hobbies": ["Music", "Photography", "Hiking"],
      "active": true
    }

Sub Json_Arr_To_Excel()
    '-------------------------------
    'author：caidongji
    'date：2023/06/23
    '-------------------------------
    Dim Json_Arr() As String
    Dim saveFolderPath As String
    Dim textString As String
    Dim targetWorkbook As Workbook
    Dim targetWorksheet As Worksheet
    Dim SaveFileName As String
      
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "选择文件"
        .Filters.Clear
        .Filters.Add "所有文件", "*.*"
        If .Show = -1 Then ' 用户点击了“打开”按钮
            Dim FilePath As String
            FilePath = .SelectedItems(1)
        Else
            MsgBox "未选择文件。操作已取消。", vbExclamation
            Exit Sub
        End If
    End With
    '---------------------------------------------------------分割线------------------------------------------------------
     ' 选择保存文件夹路径
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "选择保存文件夹"
        .Show
        If .SelectedItems.count = 0 Then
            MsgBox "未选择保存文件夹。操作已取消。", vbExclamation
            Exit Sub
        End If
        saveFolderPath = .SelectedItems(1)
    End With
    '---------------------------------------------------------分割线------------------------------------------------------
    textString = ReadFile(FilePath, "UTF-8")
    '处理空格、换行符等
    textString = Replace(textString, " ", "")
    textString = Replace(textString, vbCr, "")
    textString = Replace(textString, vbLf, "")
    
    '将嵌套的对象处理分割符号，避免影响分割
    textString = ReplaceSymbolInRange_Grade(textString, ":{", "}", ",", ";")
    textString = ReplaceSymbolInRange_Grade(textString, ":{", "}", ":", "/")

    '---------------------------------------------------------分割线------------------------------------------------------
    Json_Arr = ParseJSON(textString, "},")
    '---------------------------------------------------------分割线------------------------------------------------------
    SaveFileName = "数据转Excel"
    ' 创建新的工作簿并复制数据
    Set targetWorkbook = Workbooks.Add()
    
    targetWorkbook.Worksheets("sheet1").Range(Cells(1, 1), Cells(UBound(Json_Arr, 1) + 1, UBound(Json_Arr, 2) + 1)) = Json_Arr
    
    ' 命名\保存文件
    targetWorkbook.SaveAs saveFolderPath & "\" & SaveFileName & Format(Now, "YYYY.M.D-h.m") & ".xlsx"
    targetWorkbook.Close SaveChanges:=False
    
    '清空变量
    Set targetWorkbook = Nothing
    
    End_Tag
    
End Sub

Function ParseJSON(json_str As String, delimiter As String)
    Dim arr_tem() As String
    Dim arr_tem02() As String
    Dim arr_result() As String
    Dim i As Integer
    Dim j As Integer
    Dim result As String
    '拆分对象
    arr_tem = Split(json_str, delimiter)
    'Debug.Assert False
    i = LBound(arr_tem)
    '申明数组大小
    ReDim arr_result(0 To UBound(arr_tem) + 1, 0 To 1)
    '将对象数据合并
    Do While i <= UBound(arr_tem)
        result = ReplaceSymbolInRange_Grade(arr_tem(i), "[", "]", ",", ";")
        'Debug.Assert False
        '调用底层对象函数拆分属性
        arr_tem02 = json_base(result, ",")
        'Debug.Assert False
        '扩大数组
        ReDim Preserve arr_result(0 To UBound(arr_tem) + 1, 0 To UBound(arr_tem02))
        
        '填入字段：key
        j = LBound(arr_tem02)
        Do While j <= UBound(arr_tem02) And i = 0
            arr_result(i, j) = arr_tem02(j, 0)
            j = j + 1
        Loop
        
        '填入值：value
        j = LBound(arr_tem02)
        Do While j <= UBound(arr_tem02)
            arr_result(i + 1, j) = arr_tem02(j, 1)
            j = j + 1 '循环进行处理
        Loop
        
        ' 访问 arr_tem(i) 进行处理
        i = i + 1
    Loop
    ParseJSON = arr_result
End Function

Function json_base(json_str As String, delimiter As String)
    '解析最小对象，并将对象的字段与属性值放到数组中并返回
    Dim num_attr As Integer
    Dim arr_tem() As String
    Dim arr_result() As String
    Dim arr() As String
    Dim i As Integer
    Dim tem_str01 As String
    'Debug.Assert False
    '获取对象的属性个数
    num_attr = CountSubstringOccurrences(json_str, ":")
    '申明数组大小
    ReDim arr_result(0 To num_attr - 1, 0 To 1)

    '替换双引号"，{，换行符。等字符
    tem_str01 = Replace(Replace(Replace(Replace(json_str, "{", ""), Chr(10), ""), Chr(34), ""), "}", "")
    'Debug.Assert False
    '拆分属性
    arr_tem = Split(tem_str01, delimiter)
    'Debug.Assert False
    '获取数组下标
    i = LBound(arr_tem)
    '拆分：key与value，并写入数组
    Do While i <= UBound(arr_tem)
        arr = Split(arr_tem(i), ":")
        arr_result(i, 0) = Trim(arr(0))
        arr_result(i, 1) = Trim(arr(1))
        i = i + 1
    Loop
    '获取返回值
    json_base = arr_result
End Function

Function CountSubstringOccurrences(ByVal mainString As String, ByVal subString As String) As Integer
    '计算对象的属性个数
    Dim count As Integer
    count = 0 '初始值为0
    
    Dim startPos As Integer
    startPos = 1 '设置查询字符的起初位置
    
    Dim foundPos As Integer
    foundPos = InStr(startPos, mainString, subString)
    
    While foundPos <> 0
        count = count + 1
        startPos = foundPos + 1 '刷新查询位置
        foundPos = InStr(startPos, mainString, subString)
    Wend
    
    CountSubstringOccurrences = count
End Function

Function ReadFile(FilePath As String, encoding As String) As String
    Dim stream As Object
    Dim fileContent As String
    
    Set stream = CreateObject("ADODB.Stream")
    
    ' 设置流的类型和编码
    stream.Type = 2 ' 文本类型
    stream.Charset = encoding
    
    ' 打开文件并读取内容
    stream.Open
    stream.LoadFromFile FilePath
    fileContent = stream.ReadText
    
    ' 关闭流
    stream.Close
    Set stream = Nothing
    
    ReadFile = fileContent
End Function

Function ReplaceSymbolInRange(ByVal str As String, ByVal startChar As String, ByVal endChar As String, ByVal symbol As String, ByVal replacementChar As String) As String
    '只替换查找到第一个符号的区间的内容
    Dim startIndex As Long
    Dim endIndex As Long
    
    startIndex = InStr(1, str, startChar) + Len(startChar)
    endIndex = InStr(startIndex, str, endChar)
    
    If startIndex > 0 And endIndex > 0 Then
        Dim rangeString As String
        rangeString = Mid(str, startIndex, endIndex - startIndex)
        rangeString = Replace(rangeString, symbol, replacementChar)
        
        ReplaceSymbolInRange = Left(str, startIndex - 1) & rangeString & Mid(str, endIndex)
    Else
        ReplaceSymbolInRange = str
    End If
End Function

Function ReplaceSymbolInRange_Grade(ByVal str As String, ByVal startChar As String, ByVal endChar As String, ByVal symbol As String, ByVal replacementChar As String) As String
    '递归替换函数
    Dim startIndex As Long
    Dim endIndex As Long
    Dim rightString As String
    Dim number As Integer
    number = InStr(1, str, startChar)
    startIndex = number + Len(startChar)
    endIndex = InStr(startIndex, str, endChar)
    'Debug.Assert False
    If number > 0 And endIndex > 0 Then
        Dim rangeString As String
        'Debug.Assert False
        rightString = Mid(str, endIndex)
        If Len(Mid(str, endIndex)) > 2 Then
            'Debug.Assert False
            rightString = ReplaceSymbolInRange_Grade(Mid(str, endIndex), startChar, endChar, symbol, replacementChar)
        End If
        'Debug.Assert False
        rangeString = Mid(str, startIndex, endIndex - startIndex)
        
        rangeString = Replace(rangeString, symbol, replacementChar)
        
        ReplaceSymbolInRange_Grade = Left(str, startIndex - 1) & rangeString & rightString 'Mid(str, endIndex)
    Else
        ReplaceSymbolInRange_Grade = str
    End If
    'Debug.Assert False
End Function

Sub End_Tag()
    MsgBox "数据格式转化成功，如果你尊重作者劳动成果，请联系cai，并请他喝水，谢谢！"
End Sub

4、使用instr函数解析json字符串：此方法查找获取需要的字符串