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字符串：此方法查找获取需要的字符串
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· AI技术革命，工作效率10个最佳AI工具