1. VBA 解析 JSON 的真实痛点为什么 Excel 自动化总卡在接口数据上如果你用 Excel 做过接口数据自动化大概率遇到过这个场景调用一个天气、物流、地图或订单接口返回一大串 JSON粘到单元格里像一团乱麻想取出distance、duration、status这种字段用InStr、Mid、Split硬切字符串字段一多就崩嵌套一层就找不到北。这就是 VBA 解析 JSON 最典型的痛点——VBA 原生没有 JSON 解析器而接口返回的数据几乎全是 JSON。我试过用正则去抠字段短平快的单层 JSON 还能凑合一旦遇到data.route.paths[0].steps这种多层嵌套加数组的结构正则表达式会写得又长又脆接口字段顺序一变就全废。更麻烦的是很多接口返回的 JSON 里还嵌着字符串形式的 JSON比如某个字段本身是一段被转义的 JSON 文本你得先解一层、再解一层手工处理转义引号能把人逼疯。刘永富老师的 VBA 插件常见于API.JSON这个类模块正好补上了这块短板。它把 JSON 解析封装成一个可以在 VBA 里直接New出来的对象支持类似 JSONPath 的路径取值$.data.route.paths[0].distance这样写就能直接拿到深层字段数组用[0]下标嵌套对象用点号一路走下去。对做 Excel 自动化、需要批量处理接口返回数据的人来说这套东西的性价比很高不用装额外运行库纯 VBA 类模块导入即用配合Debug.Print就能在立即窗口里验证取值对不对。这篇文章面向的就是这类场景你手上有一批接口返回的 JSON想在 Excel 里自动解析、提取字段、写进表格或者拿解析结果做后续计算。我会把插件加载、核心解析代码、嵌套数组处理、调试方法一步步拆开讲同时说明怎么用 TaoToken 统一 Key 和 API 通道拿到稳定的测试数据避免调试阶段被接口限流或鉴权问题打断。整篇按可跟做的步骤来代码都能直接复制进 VBE 跑。先说清楚适用人群会一点 VBA、能打开 VBE、知道怎么插入模块但对 JSON 解析没系统方法的人。如果你连Sub和Dim都没写过建议先补一下 VBA 基础否则后面调试会吃力。下面从插件准备开始。2. TaoToken 前置准备统一 Key 与 API 通道给 VBA 调试供数据在写解析代码之前得先解决“数据从哪来”的问题。调试 JSON 解析最怕两件事一是接口要鉴权Key 散落在各个平台换个接口就得换一套配置二是调试阶段请求频繁容易被限流导致你分不清是代码错了还是接口拒了。我的做法是先用 TaoToken 把 Key 和 API 通道统一起来VBA 里只认一个 Base URL 和一个 Key测试数据从模型对话或接口文档里拿调试链路就干净很多。TaoToken 在这里的角色是统一的 API 接入层你拿到一个 Key配好 Base URL就能通过同一套通道访问不同模型和接口能力。对 VBA 场景来说最直接的用法是先用它生成或获取一段结构稳定的 JSON 测试数据比如让模型返回一段带嵌套数组的 JSON粘到 VBA 里练解析等解析逻辑跑通再换成真实业务接口。这样调试阶段不依赖具体业务系统的鉴权出错也好定位。具体操作上你需要三样东西API Key、Base URL、以及一个可用的 Model ID。Key 在控制台的 API Keys 页面创建Base URL 用https://taotoken.net/apiModel ID 按你实际要调的模型填。这三件套在后面的配置片段里会反复出现建议先记下来。创建 Key 的入口在控制台路径是 API Keys 管理页。拿到 Key 之后不要直接硬编码在 VBA 里尤其是要发给别人用的宏建议先放在一个隐藏工作表或环境变量里代码里用变量读取。调试阶段图省事可以直接写在模块顶部的常量里但上线前一定要挪走。如果你只是想先拿一段 JSON 练手可以直接用模型对话生成测试数据比如让它返回一段包含data.route.paths[0].distance这种结构的 JSON。拿到之后粘进 VBA配合刘永富老师的插件解析验证路径取值是否正确。这一步不需要写 HTTP 请求纯解析练习能快速建立对路径语法的感觉。等解析练熟了再考虑在 VBA 里发 HTTP 请求拿真实数据。VBA 发请求常用MSXML2.XMLHTTP或WinHttp.WinHttpRequest.5.1把 Base URL、Key、Model ID 填进请求头和请求体即可。这里不展开完整请求代码重点是先把解析这关过了因为大部分报错其实出在解析路径写错而不是请求本身。需要提醒的是调试用的 Key 和正式业务的 Key 最好分开避免调试期间的频繁请求影响正式额度。TaoToken 的控制台可以管理多个 Key按用途区分出问题也好排查。下面进入插件加载和核心代码部分。3. 刘永富老师插件加载与可复制配置JSON 解析核心代码片段这一节是全文的技术核心。刘永富老师的 VBA JSON 插件通常以类模块形式提供核心是API.JSON这个类。加载方式有两种一是直接导入.cls文件二是把类模块代码复制进 VBE 新建的类模块并把类模块命名为JSON注意命名要和代码里的New API.JSON对应如果类模块在API命名空间下就保持API.JSON的引用方式。导入步骤打开 Excel按Alt F11进 VBE右键工程 → 导入文件选择插件提供的.cls文件。导入后左侧工程树里会出现对应的类模块。如果没有.cls文件就新建类模块把插件源码粘进去然后在属性窗口把类模块名称改成JSON。如果你的工程里建了一个叫API的文件夹或命名空间引用时写New API.JSON如果直接放在工程根下就写New JSON。这一点很多人第一次会踩坑报“用户定义类型未定义”多半是命名没对上。加载完成后核心解析代码就三行起步声明对象、Parse传入 JSON 字符串、用GetSingleValue按路径取值。下面这段可以直接复制进标准模块运行JSON 用的是带嵌套数组的路线数据Sub ParseJsonDemo() Dim j As API.JSON Set j New API.JSON Dim raw As String raw {data:{route:{destination:121.473701,31.230416,origin:118.796877,32.060255,paths:[{distance:296768,duration:15060,restriction:0,steps:[],strategy:时间最短,toll_distance:266428,tolls:262,traffic_lights:56}]},count:1},errcode:0,errdetail:null,errmsg:OK,ext:null} j.Parse raw Debug.Print j.GetSingleValue($.data.route.paths[0].distance) Debug.Print j.GetSingleValue($.data.route.paths[0].duration) Debug.Print j.GetSingleValue($.data.route.paths[0].strategy) Debug.Print j.GetSingleValue($.data.route.paths) Debug.Print j.GetSingleValue($.data.route.paths[0]) End Sub路径语法要点$是根点号进入对象[0]进入数组取第一个元素。遇到中括号就是数组必须带下标比如paths[0]不带下标直接取paths返回的是带中括号的数组字符串。这一点在调试时很关键GetSingleValue($.data.route.paths)返回[{...}]而GetSingleValue($.data.route.paths[0])返回{...}后者没有外层中括号可以直接再Parse一次。嵌套 JSON 字符串的处理是另一个高频需求。有些接口会把某个字段的值做成转义后的 JSON 字符串比如paths[0]取出来是一段带双引号的文本。VBA 里双引号是字符串定界符直接Parse会出错需要先把双引号替换成单引号再解析Sub ParseNestedJson() Dim j As API.JSON Set j New API.JSON j.Parse {data:{route:{paths:[{distance:296768,duration:15060}]}}} Dim inner As String inner j.GetSingleValue($.data.route.paths[0]) inner Replace(inner, Chr(34), Chr(39)) Dim jj As New API.JSON jj.Parse inner Debug.Print jj.GetSingleValue(distance) Debug.Print jj.GetSingleValue(duration) Dim keys() As String Dim vals() As String keys jj.Keys vals jj.Values Dim i As Long For i LBound(keys) To UBound(keys) Debug.Print keys(i) vals(i) Next i End SubKeys和Values两个方法返回数组配合循环可以遍历当前层所有字段适合字段名不固定、需要动态处理的场景。注意Keys/Values返回的是当前解析层的键值不会递归到深层深层还是要靠路径逐层取。如果你要在 VBA 里发请求拿数据配置片段大致如下以WinHttp为例Key 和 Model ID 用占位符实际替换Dim http As Object Set http CreateObject(WinHttp.WinHttpRequest.5.1) http.Open POST, https://taotoken.net/api/v1/chat/completions, False http.setRequestHeader Content-Type, application/json http.setRequestHeader Authorization, Bearer YOUR_API_KEY http.Send {model:YOUR_MODEL_ID,messages:[{role:user,content:返回一段带嵌套数组的JSON}]} Debug.Print http.ResponseText三件套对应关系Base URL 用https://taotoken.net/apiKey 填YOUR_API_KEYModel ID 填YOUR_MODEL_ID。返回的ResponseText就是 JSON直接丢给j.Parse即可。这样解析和取数就串起来了。4. 验证请求与成功结果Debug.Print 输出与字段核对代码写完必须验证否则你不知道路径写对没有。VBA 里最直接的验证手段是Debug.Print输出到立即窗口Ctrl G打开。跑上面第一段ParseJsonDemo立即窗口应该依次输出296768 15060 时间最短 [{distance:296768,duration:15060,...}] {distance:296768,duration:15060,...}第一行296768是distance第二行15060是duration第三行是strategy的中文值。第四行带中括号说明取的是数组整体第五行不带中括号说明取的是数组第一个元素对象。这五行输出能同时验证三件事路径语法对不对、数组下标有没有生效、嵌套对象能不能继续解析。如果输出是空的先检查路径拼写。常见错误是漏了$或点号比如写成data.route.paths[0].distance少了根符号插件可能返回空。另一个常见错误是数组下标越界paths[1]在只有一个元素时取不到返回空而不是报错容易误判成解析失败。验证嵌套解析那段ParseNestedJson立即窗口应该输出296768、15060然后遍历输出distance 296768、duration 15060。如果Replace那步没做jj.Parse inner会报错或解析出空值因为双引号在 VBA 字符串里是定界符直接传进去语法就断了。再进一步把解析结果写进单元格验证。比如Sub WriteToSheet() Dim j As API.JSON Set j New API.JSON j.Parse {data:{route:{paths:[{distance:296768,duration:15060}]}}} Dim ws As Worksheet Set ws ThisWorkbook.Sheets(1) ws.Range(A1).Value j.GetSingleValue($.data.route.paths[0].distance) ws.Range(A2).Value j.GetSingleValue($.data.route.paths[0].duration) End Sub跑完看 A1、A2 是不是 296768 和 15060。这一步能验证解析结果能不能正常参与 Excel 后续计算比如拿distance做汇总、拿duration算平均耗时。如果单元格里出现的是带引号的字符串而不是数字说明取出来的是文本需要CLng或CDbl转换一下再写入。验证通过的标准很简单立即窗口输出和预期字段值一致单元格写入正确嵌套解析能拿到内层字段。三样都过解析逻辑就算跑通了。接下来换成真实接口返回的 JSON路径按实际结构调整即可。5. 本篇常见报错排查401、local proxy failed、reading choices、OAuth调试阶段报错集中在几类逐个对照排查。401 Unauthorized请求头里 Key 没带对或者 Key 已失效。检查Authorization头是不是Bearer YOUR_API_KEY格式中间有空格Bearer 首字母大写。Key 本身去控制台确认没过期、没被删。VBA 里字符串拼接容易多空格少空格Bearer apiKey这种写法比手写整串更稳。local proxy failed这类报错通常出现在请求根本没发出去或者本地网络环境拦截了。先确认 Base URL 写的是https://taotoken.net/api没有多余斜杠或路径。再确认 VBA 用的 HTTP 组件能正常访问外网WinHttp比XMLHTTP在某些环境更稳。如果公司网络有出口限制换一台能正常访问的机器验证排除环境因素。reading choices 相关报错这类多半出在解析响应结构时路径写错。模型返回的 JSON 结构是choices[0].message.content如果你按data.route.paths的路径去取自然取不到。先用Debug.Print http.ResponseText把原始响应打出来看清结构再写路径。不要凭记忆猜字段名接口返回的字段名大小写、下划线都可能和文档不一致。OAuth 相关报错如果你用的是需要 OAuth 的接口VBA 里手工处理 token 刷新比较麻烦。调试阶段建议先用 API Key 方式避开 OAuth 流程。等解析逻辑稳定了再考虑接入 OAuth。TaoToken 的 Key 方式对 VBA 更友好少一层鉴权复杂度。用户定义类型未定义类模块命名没对上。检查New API.JSON里的API和JSON是否和工程里的命名空间、类模块名一致。最省事的做法是类模块直接命名JSON代码里写New JSON。解析返回空但没报错路径写错或数组下标越界。用GetSingleValue($)先取根确认解析本身成功再逐层加路径每加一层Debug.Print一次定位到哪一层开始变空。双引号转义问题JSON 字符串里本身带双引号VBA 字符串拼接时要用Chr(34)或双写。嵌套解析前先Replace成单引号再Parse。排查顺序建议先看原始响应有没有拿到再看解析有没有成功最后看路径取值对不对。三步分开验证比一上来就盯着最终结果猜要快得多。6. 从解析到落地把 JSON 数据接进 Excel 自动化的实用建议解析跑通之后真正落地到 Excel 自动化还有几个细节值得注意。第一是字段类型转换GetSingleValue返回的是字符串数字字段写进单元格前用CDbl或CLng转一下否则后续求和、排序会按文本处理结果不对。第二是数组遍历paths这种数组可能有多个元素用For i 0 To n配合paths[i]逐条取不要假设只有一条。第三是错误处理接口返回的 JSON 里errcode不为 0 时data可能为空直接取深层路径会返回空值。先判断errcode再决定要不要解析data能避免很多无意义的空值排查。第四是 Key 管理调试用的 Key 和正式 Key 分开代码里不要硬编码放在隐藏工作表或配置文件里读取。如果你需要长期跑批量任务建议把解析逻辑封装成一个函数输入 JSON 字符串和路径输出字段值主流程只负责循环调用。这样换接口、改路径时只动一处维护成本低。配合 TaoToken 的统一通道测试数据和正式数据用同一套解析代码切换时只改 Base URL 和 Key解析部分不用动。最后提醒一点JSON 解析的路径语法是核心技能$、点号、[0]这三个符号的组合能覆盖绝大多数结构。遇到新接口先用Debug.Print把原始 JSON 打出来对着结构写路径比查文档猜字段快。解析这关过了Excel 自动化处理接口数据就只剩循环和写表的体力活了。