我大概是从第三次手动把Excel里的词条复制进在线翻译网页、再一个个粘回表格的时候决定用VBA写一个自动抓取脚本的。那次要翻译的字段有六百多条中英混杂人工来回折腾了两个多小时眼睛都快看花。后来我改用XMLHTTP对象直连在线翻译接口把整个流程做成了Excel里的一个按钮选中词条点一下翻译结果自动填到旁边单元格。这篇文章就把这个实现过程从头拆开讲一遍包括请求怎么构造、返回的数据怎么解析、批量翻译工具怎么做以及我在实际使用中踩过的坑。这套思路适合所有在Excel或WPS表格里处理过大量词条、术语、产品名的朋友。只要你手里有一列需要中英互译的文本按本文的操作就能得到一个自动翻译的Excel小工具。不需要额外安装软件依赖的控件都是Office和WPS自带的关键是搞清楚XMLHTTP请求-响应这条链路。下面直接进入正题。1. 为什么放着现成的网页爬取方式不用偏要XMLHTTP直连接口1.1 批量翻译的现实痛点先描述一下场景。我手上有几百上千个产品词条既有中文型号、也有英文描述需要统一成中英对照版。大多数人的第一反应是打开在线翻译网页把词条复制进去拿结果再复制回来。单个词条还好一旦数量过百这个流程的时间成本会指数级上涨而且极其容易漏词、错行——A列的英文翻译到B列第几行只要数错了整张表就废了。另一个思路是用VBA模拟浏览器操作打开翻译网站也就是创建InternetExplorer对象定位输入框、写入文本、点击按钮、等页面加载再读取结果。这种方式确实能跑通但问题非常多需要本机安装并启用IE组件页面加载速度完全不可控经常出现元素还没加载完就读取的误判而且每翻译一条就要打开一次页面几百条下来CPU和内存占用非常难看。我在64位Office环境下还遇到过InternetExplorer对象创建失败的情况排查半天最后只好换方案。1.2 VBA抓取网页数据的几种方案对比后来我把VBA侧常见的网页数据获取方式整理了一遍各有各的适用场景方案优点缺点适用场景InternetExplorer对象能执行页面里的JavaScript适合复杂交互依赖IE速度慢资源占用高稳定性差需要模拟点击、登录、翻页的复杂操作WinHttp.WinHttpRequest.5.1更底层的HTTP组件支持超时设置连接复用更好属于底层接口写起来略繁琐对超时控制、HTTPS稳定性有要求的场景MSXML2.XMLHTTP即本文的主角VBA原生支持好代码简单同步模式跑批量很顺手没有内置超时控制长请求可能卡住轻量级接口调用、数据抓取、批量短文本翻译我做翻译抓取时选了XMLHTTP理由很简单翻译是单次请求、短文本、返回JSONXMLHTTP的同步模式足够用代码也比WinHttpRequest直白。如果你做的是大量数据下载或者请求的接口不稳定、经常超时那建议换WinHttpRequest后文我会讲两者的切换方法。1.3 XMLHTTP模式的核心逻辑XMLHTTP抓数据的本质是用VBA发出一个标准的HTTP请求把词条作为参数发给翻译服务器服务器返回一段JSON字符串我们再从JSON里把译文取出来。整套逻辑就三个步骤构造URL、发送请求、解析响应。这里有个很重要的认知转变你不需要真实地打开一个网页网页本身只是服务端返回数据的展示形式真正有价值的是背后的接口。在线翻译网站的文本框和按钮本质上是把词条拼到一个接口URL上然后请求我们用XMLHTTP做的事情和网页内部做的事情是一样的只是省掉了浏览器渲染这一步。这也是为什么它比模拟浏览器快得多——省去了HTML渲染、CSS加载、JavaScript执行所有环节直接拿到最纯粹的JSON数据。2. 拆解HTTP请求把词条送进翻译服务器再拿回结果2.1 把请求想象成填写快递单理解XMLHTTP的请求过程最简单的方式是类比填快递单。你寄快递时需要填收件地址、寄件人、物品信息HTTP请求里对应的概念是URL、请求头、请求体和参数。URL是快递地址告诉服务器去哪、调哪个接口请求头是额外的说明信息比如我是哪种浏览器“我从哪个页面跳过来的”服务器会通过请求头判断你是不是正常访问参数是要翻译的内容本身在GET请求里直接拼在URL问号后面在POST请求里放进请求体。一个典型的翻译接口请求URL长这样https://fanyi.youdao.com/translate?doctypejsontypeAUTOihello问号前面是接口地址问号后面是参数用连接。其中doctypejson表示希望返回JSON格式数据typeAUTO表示自动检测语言方向中译英或英译中都可以ihello就是待翻译的词条。我可以把i的值换成任意单词或短句。这组参数不是我拍脑袋定的我抓取过在线翻译网站的网络请求记录发现页面本身就是在调用这个接口只是浏览器用JavaScript把用户输入自动拼好了URL。我们是把这套请求原样复制到VBA里。2.2 请求头决定服务器怎么看待你的请求发送HTTP请求时服务器首先会看一眼请求头。如果请求头缺失或者明显是脚本构造的部分接口会拒绝返回数据。我在实际调试中遇到过不模拟浏览器请求头就返回空内容的情况所以后来固定加上了User-Agent和Referer两项。User-Agent声明客户端是什么软件通常会填一个常见浏览器版本。Referer声明请求来自哪个页面翻译服务会校验这个信息。另外还有一个容易忽略的点如果词条里包含空格、中文、标点符号这些字符不能直接放在URL里必须百分号编码。中文在URL里要变成%E4%BD%A0%E5%A5%BD这样的字节形式否则服务器解析出来是乱码或者干脆认为请求不合法。我最初用Excel里的URL编码思路只处理了ASCII字符中文全乱套后来才找到一个可靠的办法见下文。2.3 最小可用代码GET请求跑通第一轮翻译先把最核心的VBA代码放出来这段代码能对单个单词完成请求、接收和初步显示Function GetTranslateResult(Word As String) As String Dim url As String Dim xmlHttp As Object Dim stream As Object Dim html As String 拼接请求地址i后面是经过编码的待翻译词条 url https://fanyi.youdao.com/translate?doctypejsontypeAUTOi UrlEncodeUtf8(Word) 创建XMLHTTP对象 Set xmlHttp CreateObject(MSXML2.XMLHTTP) 发送GET请求False表示同步模式也就是等服务器返回后再继续执行 xmlHttp.Open GET, url, False xmlHttp.setRequestHeader User-Agent, Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0 Safari/537.36 xmlHttp.setRequestHeader Referer, https://fanyi.youdao.com/ xmlHttp.Send 用ADODB.Stream把responseBody按UTF-8解码成字符串 Set stream CreateObject(ADODB.Stream) stream.Type 1 1表示二进制模式 stream.Open stream.Write xmlHttp.responseBody stream.Position 0 stream.Type 2 2表示文本模式 stream.Charset UTF-8 html stream.ReadText stream.Close 临时把原始JSON弹出来看一眼结构后续再替换成解析逻辑 GetTranslateResult html End Function这段代码跑通后调用GetTranslateResult(hello)返回的就是一串JSON里面能看到翻译结果。这个阶段的目的不是一步到位而是验证请求-响应链路通不通。2.4 中文参数编码的正确姿势上面用到的UrlEncodeUtf8函数是全流程里最容易踩坑的地方我单独拿出来讲。VBA没有现成的UTF-8编码函数Escape函数会把中文编成%uXXXX的格式服务器不认StrConv转的是系统本地代码页在中文Windows上是GBK也不是服务器要的UTF-8。我验证下来最可靠的做法是借用ADODB.Stream先把字符串按UTF-8编码扔到内存里再逐字节读出十六进制Function UrlEncodeUtf8(Text As String) As String Dim stream As Object Dim data As Variant Dim i As Long Dim r As String Set stream CreateObject(ADODB.Stream) stream.Type 2 文本模式 stream.Charset UTF-8 stream.Open stream.WriteText Text stream.Position 0 stream.Type 1 二进制模式 data stream.Read 拿到UTF-8编码后的字节数组 stream.Close For i 0 To UBound(data) If (data(i) 48 And data(i) 57) Or _ (data(i) 65 And data(i) 90) Or _ (data(i) 97 And data(i) 122) Or _ data(i) 45 Or data(i) 95 Or data(i) 46 Or data(i) 126 Then 字母、数字和部分符号保留原样 r r Chr(data(i)) Else 其他字节转成%XX十六进制形式 r r % Right(0 Hex(data(i)), 2) End If Next i UrlEncodeUtf8 r End Function这段代码里的关键点是stream.Type 1和stream.Type 2的切换顺序。必须先以二进制模式读入responseBody或者写入文本设置Position为0后再切到文本模式读取顺序一旦颠倒拿到的就是空字符串或乱码。这个函数我后来在项目里直接复制到所有需要拼URL的场景屡试不爽。3. 响应数据的两座大山UTF-8编码与JSON解析3.1 为什么responseText读出来是乱码不少刚接触XMLHTTP的人会直接读xmlHttp.responseText这个属性然后发现中文全是乱码比如你好变成浣犲ソ。原因是XMLHTTP的responseText在VBA里默认按ISO-8859-1或系统本地代码页尝试解码而在线翻译服务返回的是UTF-8字节流两边编码对不上自然就乱。要解决这个问题就不能读responseText而要读responseBody原始字节数组再手动指定UTF-8解码。这就是上一节代码里ADODB.Stream的作用。你只需要记住这个标准套路大部分字符集乱码问题都能用同样的方式解决。3.2 用ADODB.Stream强制按UTF-8解码解码部分的代码我再展开说一下因为它承担了两个职责一是把字节数组转成字符串二是明确告诉VBA用UTF-8来解读。实际操作时需要注意三个细节第一stream.Type必须先从2切到1再写responseBody否则Write xmlHttp.responseBody会因为类型不匹配报错。第二stream.Position 0一定要执行因为写入后指针在末尾直接切到文本模式读取会得到空内容。第三Charset UTF-8必须放在Type 2之后设置顺序反了可能不生效。按这个套路解出来的字符串就是干净的中文了。如果你拿到的JSON字符串仍然包含\uXXXX形式的内容那是JSON规范里的Unicode转义不是乱码第3.3节会讲怎么处理。3.3 从JSON字符串中精准提取tgt翻译结果解码后的响应是一个标准JSON字符串长这样{ type: EN2ZH_CN, errorCode: 0, elapsedTime: 1, translateResult: [ [ { src: hello, tgt: 你好 } ] ] }JSON的麻烦之处在于VBA没有原生解析器。虽然网上有VBA-JSON库JsonConverter可以用但它依赖ScriptControl组件而ScriptControl在64位Office环境已经被标记为不受支持的组件用起来不确定性很大。对一个字段比较固定、结构不算复杂的返回数据用正则表达式直接提取反而更可靠、更好维护。提取逻辑很简单我们只要tgt字段对应的值。用VBScript.RegExp匹配所有tgt: xxx模式的片段取第一个匹配结果即可Function ExtractTgt(json As String) As String Dim reg As Object Dim ms As Object Dim item As Object Dim s As String Set reg CreateObject(VBScript.RegExp) reg.Global True 匹配 tgt:任意内容 中的任意内容 reg.Pattern tgt:\s*([^]*) Set ms reg.Execute(json) If ms.Count 0 Then ExtractTgt ms(0).SubMatches(0) Else ExtractTgt End If 处理JSON里的换行转义符还原成真正的换行 s ExtractTgt s Replace(s, \n, vbLf) s Replace(s, \r, vbCr) s Replace(s, \t, vbTab) ExtractTgt s End Function这个正则虽然简单但能应对绝大多数纯文本翻译场景。唯一需要注意的是如果翻译结果本身包含英文双引号正则里的[^]*会在引号处截断。好在我实际翻译的内容里出现双引号的情况极少真遇到的话可以把正则升级成tgt:\s*((?:\\.|[^\\])*)这个版本能跳过JSON里的转义引号。把解码和提取两个函数组合进GetTranslateResult单条翻译的核心链路就完整了Function GetTranslateResult(Word As String) As String ... 前面是请求和解码逻辑html变量存放的是解码后的完整JSON字符串 ... GetTranslateResult ExtractTgt(html) End Function4. 从单条到批量Excel词汇表自动翻译工具落地4.1 需求整理与功能设计单条翻译跑通之后批量工具就是水到渠成的事。做之前先把需求理清楚。我当时的表格是A列放原文B列放译文可能有几百行原文可能重复也可能空单元格有些词条已经翻译过需要跳过避免重复请求。基于这些需求工具设计成四个环节选中区域、逐单元格读取、调用翻译函数、结果写回右侧单元格。翻译过程中用Scripting.Dictionary字典做缓存相同内容只请求一次大幅减少不必要的网络调用。4.2 字典去重与频率控制字典去重是批量处理里收益率最高的优化。想象一下1000行数据里可能有200条重复词条如果不做缓存这些重复内容就会白白产生200次多余请求既慢又增加被服务端限制的风险。VBA里用字典的套路如下Dim dict As Object Set dict CreateObject(Scripting.Dictionary) If Not dict.Exists(key) Then dict.Add key, trans Else trans dict(key) End If频率控制同样关键。在线翻译网页接口不是为高并发设计的如果你以毫秒级速度连续请求几十条很可能触发服务端的限流机制。我的习惯是每翻译一条暂停500毫秒到1秒。这里用Application.Wait可以但我更推荐调用Windows API的Sleep它对Excel的阻塞更小而且时间精度更高#If VBA7 Then Private Declare PtrSafe Sub Sleep Lib kernel32 (ByVal dwMilliseconds As LongPtr) #Else Private Declare Sub Sleep Lib kernel32 (ByVal dwMilliseconds As Long) #End If注意64位Office必须用PtrSafe版本否则编译会报错。这段声明在32位和64位环境下的兼容写法我想可以省去不少人的排查时间。4.3 完整代码和使用步骤把前面的函数串起来批量翻译主程序如下Sub BatchTranslate() Dim rng As Range Dim cell As Range Dim dict As Object Dim key As String Dim trans As String 用户选择待翻译区域 Set rng Application.InputBox(请选择待翻译的单元格区域, Type:8) If rng Is Nothing Then Exit Sub Set dict CreateObject(Scripting.Dictionary) Application.ScreenUpdating False For Each cell In rng.Cells key Trim(CStr(cell.Value)) If key Then If dict.Exists(key) Then 命中缓存直接使用之前翻译过的结果 cell.Offset(0, 1).Value dict(key) Else trans GetTranslateResult(key) If InStr(trans, 【) 0 Then dict.Add key, trans cell.Offset(0, 1).Value trans Else 请求失败时写入占位文本后续统一处理 cell.Offset(0, 1).Value 翻译失败 End If Sleep 500 End If End If Next cell Application.ScreenUpdating True MsgBox 翻译完成共处理 dict.Count 个不重复词条 End Sub使用步骤很简单打开Excel按AltF11进入VBA编辑器插入一个新模块把本文所有函数和子程序粘贴进去运行BatchTranslate选中A列词条范围B列就会自动写入译文。我通常把BatchTranslate绑定到一个按钮上之后非VBA使用者也能一键使用。如果你用的是WPS这套代码同样能跑前提是WPS里已经启用了VBA宏功能。MSXML2.XMLHTTP、ADODB.Stream、Scripting.Dictionary这三个对象的创建方式在WPS的VBA环境中都能正常工作。5. 实战中的翻车现场与对策5.1 返回结果出现errorCode非0怎么办翻译接口通常会返回一个errorCode字段0表示成功其他数字表示出错了比如参数错误、不支持该语言等。我早期调试时没解析这个字段有几次返回了错误正则提取到的tgt却是空值排查了半天才发现是词条本身带了一个不支持的符号。后来我把错误检查加了进去。在完整函数里先检查errorCode再决定要不要提取tgt避免拿到空结果还往下走If InStr(html, errorCode:0) 0 Then GetTranslateResult ExtractTgt(html) Else GetTranslateResult 【接口报错】 End If注意errorCode:0字符串中间没有多余空格如果接口返回的JSON带空格用正则匹配会更稳妥。这个细节属于典型的调试中才能发现的坑——直接比对字符串和实际返回的JSON结构只要差一个空格就匹配不上。5.2 请求太快被暂时限制翻译几十条之后突然全部失败这是我在批量工具没加频率控制之前最常遇到的状况。接口没有明确报错但返回的内容变成了空或一段提示性的HTML。原因就是请求频率太高触发服务端的限流机制。对策分三层。第一每条请求之间加Sleep 500以上的间隔宁可慢一点也要保证稳定。第二不要用同一个连接持续刷新必要时把每次请求都新建一个XMLHTTP对象用完释放。第三如果仍然被限制降低批次规模比如每次只翻译200条歇几秒再继续下一批。我自己的使用经验是500毫秒间隔、单批次不超过500条实际跑下来很少触发限制。如果你有上万条需要翻译别想着靠网页接口一口气跑完那种规模应该去申请官方API并走批量配额网页公开接口只适合小规模内部使用。5.3 XMLHTTP超时无响应的软肋与WinHttpRequest切换方案XMLHTTP有个让很多人头疼的问题同步请求一旦发出如果网络异常或者服务器不响应VBA会一直卡在Send那一步既没有超时机制也没有取消按钮只能靠任务管理器强行结束Excel进程没保存的数据全丢。我踩过一次这个坑之后把代码改成优先用WinHttpRequest对象。它的Open和Send方法跟XMLHTTP非常接近但多了一个SetTimeouts方法可以设置连接超时和接收超时Function GetTranslateResultWinHttp(Word As String) As String Dim http As Object Dim url As String url https://fanyi.youdao.com/translate?doctypejsontypeAUTOi UrlEncodeUtf8(Word) Set http CreateObject(WinHttp.WinHttpRequest.5.1) http.SetTimeouts 5000, 5000, 5000, 5000 解析超时、连接超时、发送超时、接收超时 http.Open GET, url, False http.setRequestHeader User-Agent, Mozilla/5.0 (Windows NT 10.0; Win64; x64) http.setRequestHeader Referer, https://fanyi.youdao.com/ http.Send GetTranslateResultWinHttp ExtractTgt(http.ResponseText) End Function等等http.ResponseText可能仍然存在编码问题。实际使用WinHttpRequest时我通常还是配合http.ResponseBody加ADODB.Stream解码。那段代码跟XMLHTTP版本几乎一样只是把对象名换掉。如果对超时可控性要求不高XMLHTTP也能跑但上了批次、网络又不稳定的场景我强烈建议切换到WinHttpRequest。5.4 缓存和备份避免重复请求的关键技巧批量翻译的理想状态是只翻译一次结果可复用。我见过不少同事把翻译脚本跑两遍第二遍因为网络波动原本翻译成功的内容反而被覆盖成了翻译失败占位符。避免这个问题的技巧是先把翻译结果写进一个隐藏的缓存工作表或者本地文本文件第二次运行时先查缓存存在就直接用不存在才发请求。我没把缓存代码写进主程序因为那会增加代码复杂度但思路值得参考。如果你经常处理几千行的词条翻译强烈建议加一层本地缓存。我实际项目中用的是Excel的辅助列A列原文、B列译文、C列写入公式IF(A2,IF(B2,B2,))这样即使脚本异常退出已翻译的结果仍然留在了B列重新跑时通过CStr(cell.Offset(0,1).Value) 判断跳过。提示在线翻译网页接口是服务端公开页面使用的不是官方批量API切勿用于高并发、商用或有SLA要求的场景。正式项目请申请官方翻译服务并获得授权后再集成。老实说我在写完这个工具的半年里又顺手把它扩展成了英汉双向互译音标提取的模块遇到Excel里需要快速翻译的场景从打开文件到拿到结果不超过几秒钟。回过头来看整条技术链路并不复杂核心就是把HTTP请求-响应模型理解透彻把编码和解析两座大山翻过去。如果你正在纠结怎么让VBA优雅地跟在线接口打交道希望这篇文章能帮你省下我当初排查问题的那两天时间。