VBA 解析下列字符串 {"data":{"rows":[{"_id":"6854bd993061d70c6c87aa4d","68400066ea04d23da81f1953":"","rowid":"737888fe-28d5-4652-8eab-d993f22e08e7","ctime":"2025-06-20 09:47:05","caid":{"accountId":"00b093d0-a7bb-4b31-af89-eaaaf45defed","fullname":"肖飞龙","avatar":"https://quickapp.wuxiapptec.com/file/mdpic/UserAvatar/default12.png?watermark/2/text/6aOe6b6Z/font/5oCd5rqQ6buR5L2T/fontsize/700/fill/d2hpdGU=/dissolve/100/gravity/Center/dx/0/dy/0/fontstyle/Ym9sZA==%7CimageView2/1/w/48/h/48/q/90","isPortal":false,"status":1},"uaid":{"accountId":"user-workflow","fullname":"工作流","avatar":"https://quickapp.wuxiapptec.com/file/mdpic/UserAvatar/workflow.png?imageView2/1/w/48/h/48/q/90","isPortal":false,"status":1},"ownerid":{"accountId":"00b093d0-a7bb-4b31-af89-eaaaf45defed","fullname":"肖飞龙","avatar":"https://quickapp.wuxiapptec.com/file/mdpic/UserAvatar/default12.png?watermark/2/text/6aOe6b6Z/font/5oCd5rqQ6buR5L2T/fontsize/700/fill/d2hpdGU=/dissolve/100/gravity/Center/dx/0/dy/0/fontstyle/Ym9sZA==%7CimageView2/1/w/48/h/48/q/90","i sPortal":false,"status":1},"utime":"2025-06-20 10:32:24","683fff9fea04d23da81f1948":"","6840021fea04d23da81f19f3":"正常","684bb415ea04d23da823b097":"Non-ISO 9001","6840021fea04d23da81f19f4":["Agilent"],"683ff8bf5c36bb7f2c7478a7":"测试","6840021fea04d23da81f19f5":"[{\"accountId\":\"00b093d0-a7bb-4b31-af89-eaaaf45defed\",\"fullname\":\"肖飞龙\",\"avatar\":\"https://quickapp.wuxiapptec.com/file/mdpic/UserAvatar/default12.png?watermark/2/text/6aOe6b6Z/font/5oCd5rqQ6buR5L2T/fontsize/700/fill/d2hpdGU=/dissolve/100/gravity/Center/dx/0/dy/0/fontstyle/Ym9sZA==%7CimageView2/1/w/48/h/48/q/90\",\"status\":1}]","684003a1ea04d23da81f1a06":null,"684b81a1ea04d23da823a915":"[]","684b8370ea04d23da823a937":"[]","684f99d55c44ea7978e3e365":"[]","683ffc8fea04d23da81f1911":"[{\"type\":0,\"sid\":\"ffcb2f30-9aff-4d0b-914f-9c1202cf128e\",\"sidext\":\"\",\"accountId\":\"\",\"fullname\":\"\",\"avatar\":\"\",\"name\":\"液相色谱质联用仪\",\"ext1\":\"\",\"ext2\":\"\",\"link\":\"/worksheet/684be40b921b18b25df12b70/row/ffcb2f30-9aff-4d0b-91 4f-9c1202cf128e\",\"projectId\":\"\",\"sourcevalue\":\"{\\\"_id\\\":\\\"684be51ade06b48f5a0421ff\\\",\\\"wsid\\\":\\\"684be40b921b18b25df12b70\\\",\\\"rowid\\\":\\\"ffcb2f30-9aff-4d0b-914f-9c1202cf128e\\\",\\\"status\\\":1,\\\"683febc45c36bb7f2c7478a0\\\":\\\"液相色谱质联用仪\\\",\\\"unreads\\\":false,\\\"autoid\\\":19,\\\"discussunreads\\\":false,\\\"allowedit\\\":false,\\\"allowdelete\\\":false,\\\"controlpermissions\\\":\\\"\\\"}\"}]","683ffd3cea04d23da81f192b":"液相色谱质联用仪","683ffd3cea04d23da81f192c":"LCMS","683ffc8fea04d23da81f1913":"C","684f69245c44ea7978e3df4a":["记录","维护","维修","校验"],"68400066ea04d23da81f1952":"","autoid":0,"685138403061d70c6c8781f5":"https://quickapp.wuxiapptec.com/app/f934aaa4-2f56-41c8-9bd2-ca728bb1086d/684be40b921b18b25df12b6f/684be40b921b18b25df12b73/row/737888fe-28d5-4652-8eab-d993f22e08e7","683ff8bf5c36bb7f2c7478a5":"CAS-CD-LCMS-AZ","683fff3eea04d23da81f193c":"","683ffc8fea04d23da81f1914":"","683ff8bf5c36bb7f2c7478aa":"","6853d5645c44ea7978e479fa":"","6853d47da716e0 89c0026387":"","68520f2f5c44ea7978e46803":"","6855075c5c44ea7978e48224":"[]","6853d5645c44ea7978e479fb":"","6853d5645c44ea7978e479f9":"","6852197b3061d70c6c87886a":"","684fb9d43061d70c6c856a66":"[]","6840021fea04d23da81f19f6":""},{"_id":"684bea4c6b544ef97b534f82","rowid":"e46f1378-b08f-4236-82f6-dc8d908d9565","ctime":"2025-06-13 17:07:24","caid":{"accountId":"00b093d0-a7bb-4b31-af89-eaaaf45defed","fullname":"肖飞龙","avatar":"https://quickapp.wuxiapptec.com/file/mdpic/UserAvatar/default12.png?watermark/2/text/6aOe6b6Z/font/5oCd5rqQ6buR5L2T/fontsize/700/fill/d2hpdGU=/dissolve/100/gravity/Center/dx/0/dy/0/fontstyle/Ym9sZA==%7CimageView2/1/w/48/h/48/q/90","isPortal":false,"status":1},"uaid":{"accountId":"user-workflow","fullname":"工作流","avatar":"https://quickapp.wuxiapptec.com/file/mdpic/UserAvatar/workflow.png?imageView2/1/w/48/h/48/q/90","isPortal":false,"status":1},"ownerid":{"accountId":"00b093d0-a7bb-4b31-af89-eaaaf45defed","fullname":"肖飞龙","avatar":"https://quickapp.wuxiapptec.com/file/mdpic/UserAva tar/default12.png?watermark/2/text/6aOe6b6Z/font/5oCd5rqQ6buR5L2T/fontsize/700/fill/d2hpdGU=/dissolve/100/gravity/Center/dx/0/dy/0/fontstyle/Ym9sZA==%7CimageView2/1/w/48/h/48/q/90","isPortal":false,"status":1},"utime":"2025-06-21 14:42:45","683ff8bf5c36bb7f2c7478a5":"CAS-CD-WEI-I","6840021fea04d23da81f19f3":"正常","684bb415ea04d23da823b097":"ISO 9001","683ff8bf5c36bb7f2c7478a7":"2mg (E2)","683fff3eea04d23da81f193c":"2025-04-27","683fff9fea04d23da81f1948":"2026-04-26","6840021fea04d23da81f19f6":"[]","684003a1ea04d23da81f1a06":"ADQC","6840021fea04d23da81f19f4":["其他:水玲砝码"],"684b8370ea04d23da823a937":"[]","683ffc8fea04d23da81f1911":"[{\"type\":0,\"sid\":\"174d5754-5277-49b2-8bb7-2dc225f8730d\",\"sidext\":\"\",\"accountId\":\"\",\"fullname\":\"\",\"avatar\":\"\",\"name\":\"砝码\",\"ext1\":\"\",\"ext2\":\"\",\"link\":\"/worksheet/684be40b921b18b25df12b70/row/174d5754-5277-49b2-8bb7-2dc225f8730d\",\"projectId\":\"\",\"sourcevalue\":\"{\\\"_id\\\":\\\"684be961a716e089c0017ffe\\\",\\\"wsid\\\":\\\"684be40b921b1 8b25df12b70\\\",\\\"rowid\\\":\\\"174d5754-5277-49b2-8bb7-2dc225f8730d\\\",\\\"status\\\":1,\\\"683febc45c36bb7f2c7478a0\\\":\\\"砝码\\\",\\\"unreads\\\":false,\\\"autoid\\\":0,\\\"discussunreads\\\":false,\\\"allowedit\\\":false,\\\"allowdelete\\\":false,\\\"controlpermissions\\\":\\\"\\\"}\"}]","683ffd3cea04d23da81f192b":"砝码","683ffd3cea04d23da81f192c":"WEI","683ffc8fea04d23da81f1913":"AE","684b81a1ea04d23da823a915":"[]","68400066ea04d23da81f1953":"","autoid":203,"684f69245c44ea7978e3df4a":["校验"],"685138403061d70c6c8781f5":"https://quickapp.wuxiapptec.com/app/f934aaa4-2f56-41c8-9bd2-ca728bb1086d/684be40b921b18b25df12b6f/684be40b921b18b25df12b73/row/e46f1378-b08f-4236-82f6-dc8d908d9565","6855075c5c44ea7978e48224":"[\"9219053d-1d51-40cd-b664-dea125ed89a5\"]","6840021fea04d23da81f19f5":"","68400066ea04d23da81f1952":"","683ffc8fea04d23da81f1914":"","683ff8bf5c36bb7f2c7478aa":"","6853d5645c44ea7978e479fa":"","6853d47da716e089c0026387":"","684f99d55c44ea7978e3e365":"[]","68520f2f5c44ea7978e46803":"","6853d56 45c44ea7978e479fb":"","6853d5645c44ea7978e479f9":"","6852197b3061d70c6c87886a":"","684fb9d43061d70c6c856a66":"[]"}],"total":204},"success":true,"error_code":1}
时间: 2025-06-22 08:32:27 浏览: 2
<think>我们面对的是一个复杂JSON解析问题,用户要求将包含嵌套对象和数组的JSON字符串解析为VBA数组。根据引用[1]和引用[3],复杂JSON通常包含多层嵌套结构,例如引用[1]中提到的和风天气JSON数据(heweather6键的值是一个数组,数组内只有1个json对象,这个对象里又嵌套了几个json对象)。在VBA中处理这种复杂结构,使用第三方库VBA-JSON是最佳选择。解决方案步骤:1.导入VBA-JSON解析器(JsonConverter.bas)并添加MicrosoftScriptingRuntime引用。2.使用JsonConverter.ParseJson方法将JSON字符串解析为VBA中的字典(Dictionary)和集合(Collection)对象。3.递归遍历解析后的对象,将嵌套结构转换为多维数组或数组的数组。注意:由于JSON的嵌套结构,我们可能需要设计递归函数来处理,但考虑到最终目标是转换为数组,我们需要明确数组的结构。通常,复杂JSON转换为数组时,我们可能需要扁平化处理或者使用多维数组来表示层次关系。以下是一个处理包含嵌套对象和数组的JSON的示例代码:假设我们有如下复杂JSON字符串(来自引用[1]的简化版):{"heweather6":[{"basic":{"city":"北京"},"update":{"loc":"2020-04-0112:30"},"status":"ok","now":{"cloud":"10","cond_code":"104"}}]}我们的目标是将这个JSON解析并转换为VBA数组。由于最外层是一个对象,其中"heweather6"是一个数组,数组中的元素是一个对象,该对象又包含多个嵌套对象,因此我们需要逐层解析。步骤:1.解析整个JSON字符串,得到一个字典对象(最外层)。2.从字典中取出键"heweather6"对应的值(这是一个集合,因为JSON中它是一个数组)。3.遍历这个集合(数组),每个元素是一个字典(因为数组中的元素是对象)。4.对于每个元素(字典),我们继续取出其中的键值对,对于值如果是字典或集合,则继续深入,直到基本类型。但是,由于嵌套的深度不确定,我们可能需要递归。然而,转换为数组通常需要确定结构。因此,我们这里假设我们已知结构,并提取特定字段。如果我们想将整个heweather6数组中的每个对象(这里只有一个)的字段提取出来,形成一个二维数组,第一维是记录,第二维是字段值,那么我们可以这样做:由于嵌套的存在,我们需要从嵌套对象中提取值。例如,我们想提取:-basic.city-update.loc-status-now.cloud-now.cond_code我们可以这样写:代码示例:</think>### 在VBA中解析复杂JSON字符串(含嵌套对象/数组)的完整方案
在VBA中解析复杂JSON结构(含嵌套对象和数组)的最可靠方法是使用**VBA-JSON解析器**。以下是详细步骤:
---
#### 步骤1:安装并配置VBA-JSON解析器
1. **下载解析器**:
- 从GitHub获取[VBA-JSON解析器](https://github.com/VBA-tools/VBA-JSON)
2. **导入模块**:
- Excel VBE中 → 文件 → 导入 → 选择`JsonConverter.bas`
3. **添加引用**:
- VBE中 → 工具 → 引用 → 勾选:
- `Microsoft Scripting Runtime`
- `Microsoft XML, v6.0`
---
#### 步骤2:解析多层嵌套JSON的代码实现
```vba
Sub ParseComplexJson()
' 示例JSON(含嵌套对象和数组)[^1]
Dim jsonStr As String
jsonStr = "{""heweather6"":[{""basic"":{""city"":""北京"",""admin"":""中国""},""status"":""ok"",""now"":{""cloud"":""10"",""cond_code"":104}}]}"
' 解析JSON为字典对象
Dim jsonDict As Object
Set jsonDict = JsonConverter.ParseJson(jsonStr)
' 获取heweather6数组(Collection类型)[^1]
Dim weatherData As Collection
Set weatherData = jsonDict("heweather6")
' 创建结果数组(行=数据记录数,列=字段数)
Dim resultArray() As Variant
ReDim resultArray(1 To weatherData.Count, 1 To 5)
' 遍历数组中的每个对象
Dim i As Long
For i = 1 To weatherData.Count
Dim record As Object
Set record = weatherData(i)
' 提取嵌套对象的值[^3]
resultArray(i, 1) = record("basic")("city") ' 城市
resultArray(i, 2) = record("basic")("admin") ' 国家
resultArray(i, 3) = record("status") ' 状态
resultArray(i, 4) = record("now")("cloud") ' 云量
resultArray(i, 5) = record("now")("cond_code") ' 天气代码
Next i
' 输出到Excel
Sheets("Data").Range("A1:E" & weatherData.Count).Value = resultArray
End Sub
```
---
#### 关键处理技术
1. **嵌套对象访问**:
```vba
record("basic")("city") ' 两级嵌套
record("now")("cloud") ' 另一组嵌套
```
2. **处理JSON数组**:
```vba
For i = 1 To weatherData.Count ' 遍历数组元素
Set record = weatherData(i) ' 获取数组中的对象
Next i
```
3. **空值处理**:
```vba
If Not record("optional_field") Is Nothing Then
resultArray(i,6) = record("optional_field")
Else
resultArray(i,6) = "N/A"
End If
```
4. **日期格式转换**:
```vba
resultArray(i,7) = CDate(Replace(record("date"), "T", " "))
```
---
#### 复杂JSON结构解析示例
原始JSON结构:
```json
{
"heweather6": [
{
"basic": {
"city": "北京",
"admin": "中国"
},
"status": "ok",
"now": {
"cloud": "10",
"cond_code": 104,
"daily_forecast": [
{"date":"2023-06-01", "temp_max":28},
{"date":"2023-06-02", "temp_max":30}
]
}
}
]
}
```
解析嵌套数组:
```vba
' 访问daily_forecast数组
Dim forecasts As Collection
Set forecasts = record("now")("daily_forecast")
' 遍历预报数组
For j = 1 To forecasts.Count
Debug.Print forecasts(j)("date"), forecasts(j)("temp_max")
Next j
```
---
#### 常见问题解决
1. **解析错误处理**:
```vba
On Error Resume Next
Set jsonDict = JsonConverter.ParseJson(jsonStr)
If Err.Number <> 0 Then
MsgBox "JSON解析错误: " & Err.Description
Exit Sub
End If
```
2. **动态字段处理**:
```vba
Dim key As Variant
For Each key In record.Keys
If TypeName(record(key)) = "Dictionary" Then
' 处理嵌套对象
ElseIf TypeName(record(key)) = "Collection" Then
' 处理嵌套数组
End If
Next key
```
> **最佳实践**:
> - 使用`JsonConverter`处理所有嵌套结构[^1]
> - 使用`Dictionary`和`Collection`对象遍历层级
> - 在访问嵌套值前检查`Exists`方法
阅读全文
相关推荐


















