AI+VBA自动化：从非结构化文本到Excel结构化数据录入

1. 先搞清楚“AI+VBA”到底能帮你做什么，别急着写代码

如果你经常需要处理Excel里的人员信息录入、核对、整理，每次都是手动复制粘贴，或者写一堆复杂的VBA公式，那这个“AI+VBA”的思路值得你花30分钟了解一下。它解决的核心问题，不是让你从零开始学AI大模型，而是把AI当成一个“聪明的数据处理器”，帮你把非结构化的信息（比如一段文字描述）自动整理成Excel表格里规整的字段，然后VBA负责执行最后的“填写”动作。

举个例子，你收到一段文本：“张三，男，28岁，技术部，手机号13800138000，2023年入职”。传统做法是你得自己拆开，分别填到姓名、性别、年龄、部门、电话、入职日期这些单元格里。而“AI+VBA”的思路是：你告诉AI（通过一个简单的接口）这段文本和表格的字段对应关系，AI帮你解析好，返回一个结构化的数据（比如JSON），然后VBA脚本拿到这个数据，自动填入Excel指定位置。

这最适合两类人：一是经常需要从邮件、聊天记录、文档里批量提取人员信息录入Excel的行政、HR或业务人员；二是已经会用VBA做自动化，但苦于处理不规则文本的开发者。最关键的价值在于，它把最耗时的“理解并拆分文本”工作外包给了AI，你只需要关心“怎么把结果填进去”这个确定性动作。

所以，别被“AI”吓到，我们这里谈的不是去训练模型，而是利用现成的、能处理文本的AI服务（比如大模型提供的API）作为工具，VBA作为执行臂，组合成一个全自动的流水线。

2. 动手前的环境与思路准备：别在第一步就卡住

在开始写任何代码之前，先把环境和思路理清楚。很多人一上来就找VBA调用AI的代码，结果连最基本的网络请求都发不出去。

2.1 核心组件与替代方案

这个方案需要三个部分协同工作：

1. AI服务端：负责理解文本并返回结构化数据。你不能在VBA里直接跑一个大模型，所以需要一个能通过HTTP接口调用的AI服务。常见选择有：

   • 各大云厂商的AI平台API：例如，提供自然语言处理（NLP）或大模型服务的API。你需要关注其“信息抽取”或“文本结构化”功能。
   • 开源模型本地部署：如果你有本地服务器，可以部署一些轻量级的信息抽取模型，并封装成HTTP服务。这对普通用户门槛较高。
   • 注意：绝对不要尝试寻找或使用任何绕过正常网络访问限制的工具或服务。所有操作必须基于合法、合规、公开提供的API服务进行。

2. VBA客户端：位于你的Excel中。它的核心任务是：

   • 从Excel单元格或外部文件读取待处理的原始文本。
   • 构建一个HTTP请求，发送给上述AI服务端。
   • 接收并解析AI返回的JSON格式结果。
   • 将解析后的数据填写到Excel指定的单元格。

3. Excel模板：定义好人员信息的字段（如A列姓名，B列性别，C列年龄等），这是VBA填写数据的目标。

对于绝大多数办公室场景，最可行的起点是使用某个云服务提供的、有免费额度的文本理解API。先确保你能用手工方式（比如用Postman或浏览器插件）成功调用这个API并拿到返回结果，这是后续所有自动化的基础。

2.2 VBA的环境准备与权限

VBA本身功能有限，尤其是直接发起网络请求。你需要确保以下几点：

• 启用必要的引用：在VBA编辑器（按Alt+F11）中，点击“工具”->“引用”，勾选Microsoft XML, v6.0（或类似版本）。这是用XMLHTTP对象发送HTTP请求的关键。
• 处理JSON解析：VBA原生不支持JSON。你需要一个解析器。最常用的是VBA-JSON（一个开源的JsonConverter.bas模块）。将其导入到你的VBA工程中，就能用JsonConverter.ParseJson方法把API返回的字符串变成VBA能操作的对象。
  • 注意：如果你遇到“错误424”等问题，通常是因为JsonConverter模块没有正确导入，或者返回的数据不是合法的JSON字符串。务必先单独测试JSON解析功能。
• WPS用户注意：WPS对VBA的支持可能不完整，特别是某些对象库。如果使用WPS，请确认其VBA环境是否完整支持上述Microsoft XML引用。有时需要寻找兼容的替代方法或确认WPS VBA插件版本。

3. 从单条测试到批量录入：搭建你的自动化流水线

不要想着一口吃成胖子。我们分三步走：先让AI理解一句话，再让VBA填一个格子，最后组合起来处理一堆数据。

3.1 第一步：设计AI的“任务指令”（提示词）

AI不是神仙，你需要清晰地告诉它你要什么。这就是“提示词工程”的简化版。你发给AI API的请求里，除了原始文本，更关键的是一个清晰的“指令”。

假设你的API支持类似ChatGPT的对话格式，你的请求内容（messages）可以这样设计：

[ { "role": "system", "content": "你是一个专业的人员信息提取助手。请从用户提供的文本中，提取出姓名、性别、年龄、部门、手机号和入职年份。如果某项信息不存在，则输出为空。请以严格的JSON格式回复，格式为：{\"name\": \"\", \"gender\": \"\", \"age\": \"\", \"department\": \"\", \"phone\": \"\", \"join_year\": \"\"}" }, { "role": "user", "content": "原始文本：张三，男，28岁，技术部，手机号13800138000，2023年入职" } ]

关键点：system指令里定义了输出格式。这比让AI自由发挥要可靠得多。你需要根据自己表格的字段，调整这个JSON的键名。

3.2 第二步：编写VBA调用AI的核心函数

下面是一个最基础的VBA函数，它调用一个假设的AI API（你需要替换your_api_key和your_endpoint为真实值）。

‘ 首先确保已导入JsonConverter.bas模块，并添加了Microsoft XML引用 Function ExtractPersonInfoFromAI(rawText As String) As Object ‘ 此函数调用AI API，解析返回的JSON Dim http As Object Dim url As String, apiKey As String Dim requestBody As String, responseText As String Dim json As Object ‘ 1. 设置API信息 (此处需替换为你的真实信息) url = “https://api.example.com/v1/chat/completions” ‘ 示例端点 apiKey = “your_api_key_here” ‘ 2. 构建请求JSON体 (基于第一步设计的提示词) requestBody = “{“ & _ “”“model”“: ”“gpt-3.5-turbo”“,” & _ “”“messages”“: [“ & _ “{”“role”“: ”“system”“, ”“content”“: ”“你是一个人员信息提取助手...（同上）”“},” & _ “{”“role”“: ”“user”“, ”“content”“: ”“原始文本：” & rawText & “”“}” & _ “]” & _ “}” ‘ 3. 创建并发送HTTP请求 Set http = CreateObject(“MSXML2.XMLHTTP”) http.Open “POST”, url, False http.setRequestHeader “Content-Type”, “application/json” http.setRequestHeader “Authorization”, “Bearer ” & apiKey http.send requestBody ‘ 4. 检查请求是否成功 If http.Status = 200 Then responseText = http.responseText ‘ 5. 解析返回的JSON (重点！) ‘ 首先从返回的完整响应中提取AI回复的内容 Set json = JsonConverter.ParseJson(responseText) ‘ 假设API返回结构是 {“choices”:[{“message”:{“content”: “{\”name\“:\”张三\“...}”}}]} Dim aiReply As String aiReply = json(“choices”)(1)(“message”)(“content”) ‘ 6. 将AI回复的JSON字符串再次解析为对象 Set ExtractPersonInfoFromAI = JsonConverter.ParseJson(aiReply) Else MsgBox “API请求失败：” & http.Status & “ - ” & http.statusText Set ExtractPersonInfoFromAI = Nothing End If Set http = Nothing End Function

重要提示：上面的代码是概念演示。实际API的请求格式、响应结构、鉴权方式可能完全不同。你必须根据你选用的AI服务商提供的文档来修改requestBody的构建和responseText的解析逻辑。先用手工测试工具（如Postman）调通API，是成功的关键。

3.3 第三步：将AI结果填入Excel

有了能返回信息对象的函数，写一个子过程来驱动整个录入流程：

Sub AutoFillPersonInfo() Dim ws As Worksheet Dim lastRow As Long, i As Long Dim rawTextCell As Range Dim personInfo As Object ‘ 设置工作表 Set ws = ThisWorkbook.Sheets(“人员信息表”) ‘ 修改为你的工作表名 ‘ 假设原始文本在A列，从第2行开始 lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row For i = 2 To lastRow ‘ 跳过标题行 Set rawTextCell = ws.Cells(i, “A”) If Len(Trim(rawTextCell.Value)) > 0 Then ‘ 调用AI函数解析文本 Set personInfo = ExtractPersonInfoFromAI(CStr(rawTextCell.Value)) If Not personInfo Is Nothing Then ‘ 将解析结果填入右侧各列 (根据你的表头调整列号) ws.Cells(i, “B”).Value = personInfo(“name”) ‘ B列姓名 ws.Cells(i, “C”).Value = personInfo(“gender”) ‘ C列性别 ws.Cells(i, “D”).Value = personInfo(“age”) ‘ D列年龄 ws.Cells(i, “E”).Value = personInfo(“department”) ‘ E列部门 ws.Cells(i, “F”).Value = personInfo(“phone”) ‘ F列电话 ws.Cells(i, “G”).Value = personInfo(“join_year”) ‘ G列入职年份 Else ws.Cells(i, “B”).Value = “解析失败” End If ‘ 避免请求过快，可添加短暂延迟 Application.Wait (Now + TimeValue(“0:00:01”)) End If Next i MsgBox “信息录入完成！” End Sub

运行这个宏，它就会读取A列的每一行文本，调用AI，然后把结果分别填到B到G列。一个最基础的“全自动人员信息录入系统”就完成了。

4. 让系统更健壮：错误处理、性能与扩展

上面的代码能跑通，但离“健壮”还差得远。在实际使用中，你肯定会遇到各种问题。下面是我踩过坑后总结的几个优化点。

4.1 必须加入的错误处理与日志

网络请求和AI解析充满不确定性。绝对不能一个报错就导致整个流程崩溃。

• 网络超时与重试：XMLHTTP请求可能因为网络波动失败。你需要设置超时并加入重试机制。

  ‘ 在发送请求前设置超时（单位：毫秒） http.setTimeouts 3000, 6000, 10000, 15000 ‘ 解析、连接、发送、接收的超时 ‘ 发送请求后，可以检查状态，如果失败，进行有限次重试（例如3次）

• API响应错误：AI服务可能返回错误，如额度不足、内容违规、服务内部错误等。你的代码需要能捕捉这些错误，并记录到日志或Excel的某一列，而不是直接弹窗中断。

  If http.Status <> 200 Then ws.Cells(i, “H”).Value = “API错误: ” & http.Status & “ | ” & responseText ‘ 记录到H列 GoTo NextRow ‘ 跳过此行，继续下一行 End If

• JSON解析失败：AI返回的内容可能偶尔不符合JSON格式。用On Error Resume Next包裹解析代码，并检查解析后的对象是否有效。

  On Error Resume Next Set personInfo = JsonConverter.ParseJson(aiReply) If Err.Number <> 0 Then ws.Cells(i, “H”).Value = “JSON解析失败: ” & Err.Description Set personInfo = Nothing Err.Clear End If On Error GoTo 0

• 添加进度提示：处理大量数据时，在状态栏显示进度，避免用户以为程序卡死。

  Application.StatusBar = “正在处理第 ” & i & “/” & lastRow & “ 条记录...”

4.2 性能与成本考量

• 批量处理与速率限制：大多数AI API有每秒请求次数（RPS）限制。不要用For循环无脑快速发送。在循环内加入Application.Wait或Sleep函数进行延迟是必要的。更好的方式是，如果API支持，设计一个能一次性处理多条文本的请求，减少调用次数。
• 本地缓存：对于重复性高的人员信息（比如公司内部常见姓名、部门），可以在首次解析后，将(原始文本， 解析结果)缓存到Excel的另一个隐藏工作表或字典里。下次遇到相同文本，直接使用缓存结果，无需再次调用AI，节省成本和时间。
• 成本控制：关注AI API的计价方式（按次、按token数）。在处理海量数据前，先用几百条数据测试，估算总成本。可以考虑先对数据进行去重处理。

4.3 功能扩展思路

基础系统跑通后，你可以根据需求扩展：

• 多源数据输入：不仅可以从Excel列读取，还可以修改代码，使其能读取txt文件、扫描指定Outlook邮件文件夹、甚至监控某个网络表单。
• 结果校验与清洗：AI可能出错。可以增加一个校验步骤，例如，检查手机号是否为11位数字，年龄是否为合理数字。可以在VBA中写简单的规则进行清洗，或者将“低置信度”的结果标记出来供人工复核。
• 触发自动化：将AutoFillPersonInfo过程与按钮绑定，或设置为打开工作簿时、更改特定单元格时自动运行。
• 生成报告：信息录入后，可以自动触发另一段VBA代码，生成统计报表、人员花名册等。

5. 常见问题排查清单（从结果倒推问题）

当你发现系统不工作时，按照以下顺序排查，能节省大量时间：

1. VBA宏根本不能运行？

   • 检查Excel宏安全性设置（“文件”->“选项”->“信任中心”->“宏设置”）。
   • 检查VBA工程中是否缺少Microsoft XML引用或JsonConverter模块。

2. 运行后，所有行的“解析结果”列都是空的或“解析失败”？

   • 第一步，检查网络请求是否发出：在http.send之后，立即用Debug.Print http.Status和Debug.Print http.responseText打印到立即窗口。如果状态码不是200，问题出在API请求本身（如URL、API Key错误，网络不通）。
   • 第二步，检查API返回内容：将打印出的responseText复制到在线的JSON格式化工具（如 json.cn），看是否是合法JSON。如果不是，说明AI服务返回了错误信息，根据错误信息调整你的请求参数或提示词。
   • 第三步，检查JSON解析逻辑：如果responseText是合法的，但解析aiReply时出错，说明你的代码在提取aiReply字符串时路径不对。仔细对照API文档，找到AI回复文本在返回JSON中的正确位置。

3. 部分行解析成功，部分失败？

   • 检查失败的原始文本是否格式特殊，包含大量换行、特殊符号或AI难以理解的内容。优化你的system提示词，让它更鲁棒。
   • 检查是否触发了API的速率限制，在循环中增加更长的延迟。

4. 解析结果错位（如姓名填到了性别列）？

   • 检查ws.Cells(i, “B”).Value = personInfo(“name”)这行代码中的列标（“B”）和JSON键名（“name”）是否与你的表格设计、AI返回的键名完全匹配。键名大小写敏感。

5. WPS中运行报错？

   • 确认WPS安装的VBA支持库是否完整。尝试使用更早期的Microsoft XML版本（如v3.0）。
   • 考虑将核心的HTTP请求和JSON解析逻辑用更通用的语言（如Python）编写成一个小工具，然后VBA通过Shell调用这个外部工具，绕过WPS VBA的限制。

最后的核心建议：不要试图第一次就做出完美无缺的系统。先用10行样本数据，走通“单条文本 -> AI API -> 结果回填”这个最小闭环。这个闭环通了，剩下的批量、容错、优化都是工程细节。这个“AI+VBA”的组合，其威力不在于VBA多复杂，而在于你能否设计好给AI的“指令”，并处理好两者之间脆弱的数据交接环节。