企业官网建设流程全解析

我大概是从第三次手动把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侧常见的网页数据获取方式整理了一遍，各有各的适用场景：

我做翻译抓取时选了XMLHTTP，理由很简单：翻译是单次请求、短文本、返回JSON，XMLHTTP的同步模式足够用，代码也比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?doctype=json&type=AUTO&i=hello

问号前面是接口地址，问号后面是参数，用&连接。其中doctype=json表示希望返回JSON格式数据，type=AUTO表示自动检测语言方向（中译英或英译中都可以），i=hello就是待翻译的词条。我可以把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?doctype=json&type=AUTO&i=" & 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 Function

4. 从单条到批量：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，按Alt+F11进入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?doctype=json&type=AUTO&i=" & 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优雅地跟在线接口打交道，希望这篇文章能帮你省下我当初排查问题的那两天时间。