前阵子遇到一个需求每天从公司内部网站抓一张报价表手动开浏览器、复制、粘贴偶尔还会漏掉新加的行。后来我直接用Excel VBA写了一段请求代码双击按钮几秒钟数据就进了工作表。这就是XMLHTTP的功劳。这确实可以用VBA走网络请求大多数时候不需要引入什么重量级库一个XMLHTTP对象就够了。它能抓网页、调接口、模拟POST表单提交做完之后数据仍在Excel里继续用VBA清洗、计算、生成报表都很顺手。这篇是“VBA信息获取与处理专题六第一节”我先不急着上复杂案例而是把XMLHTTP这个对象掰开揉碎讲清楚它是什么、有哪些属性和方法、怎么用同步请求抓一个网页、调用接口时常见的坑在哪里。后面的专题小节里我们还会深入JSON解析、登录状态维持这些进阶话题但前提是先把地基打好。这篇文章适合两类人一类是VBA基础已经比较熟但想从“只会操作单元格”跨到“会拿外部数据”的另一类是刚接触VBA没多久想系统了解网络请求到底是怎么回事的。我尽量用大白话讲让你看完能直接写出一段能跑的代码。1. 需求驱动的选型为什么是XMLHTTP而不是WebBrowser和QueryTables很多人在VBA里第一次想到“联网”用的方案五花八门有人用WebBrowser控件有人用Excel自带的QueryTables导入网页数据还有人直接调用InternetExplorer.Application再去操作页面。这些方案不是不能用而是各有各的累赘。我花几年时间踩完一圈之后现在的选择很明确凡是只需要拿数据、不需要渲染JS的请求一律用XMLHTTP。1.1 看起来能联网的几种方案先说说WebBrowser控件。它本质上把一个完整浏览器内核塞进了你的窗体里能加载页面、能执行JavaScript、能模拟点击理论上什么网站都能打开。代价是慢而且控制起来非常别扭——你经常要写一个循环去等页面加载完成页面一改版你的DOM选择器就要跟着改。它适合“必须让JS跑完才能看到数据”的场景日常抓数据用它属于高射炮打蚊子。再说Excel的QueryTables。这个功能可以导入网页上的表格胜在操作简单几行代码就能把网页表格拉回单元格里。但它对网页结构要求比较高适合那种目标数据刚好在一个规规矩矩的HTML表格里的情况。如果接口返回的是纯JSON、XML或者页面里根本没有表格结构QueryTables就派不上用场了。还有一个更古老的选择是InternetExplorer.Application通过IE的COM接口去控制浏览器。这个方案以前很流行能模拟真实用户操作但现在IE已经停止维护越来越多网站不再兼容代码写着写着就会碰到各种莫名奇妙的问题。能不碰浏览器自动化就别碰这是我现在的一条铁律。1.2 把XMLHTTP和它们放一起比XMLHTTP是MSXML组件库里的一个COM对象名字很直白XML HTTP。它最初的设计目的是给客户端发HTTP请求、接收XML数据但后来大家发现它完全可以直接当通用HTTP客户端用。它与浏览器自动化最大的区别在于它不渲染页面。它发出去一个请求收回来的是最原始的HTTP响应内容——可能是HTML源码、JSON字符串、XML文档或者文件二进制数据。方案速度是否需要渲染页面适合场景上手难度WebBrowser控件慢需要必须执行JS后才有数据的页面较高QueryTables中等不需要网页里现成的HTML表格低InternetExplorer.Application慢需要需要模拟完整用户操作高XMLHTTP快不需要抓HTML源码、调API、POST提交低XMLHTTP的另一个优势是代码结构清晰。一遍Open、一次Send、判断Status、读取responseText完事。没有等待页面加载的循环也不必操心里面某个按钮的id变了。1.3 什么样的情况不建议用XMLHTTP我也得坦白XMLHTTP不是万能的。它的短板在于请求发出去之前它不会主动去执行页面里那些JavaScript。很多现代网页表格数据是由JS发起二次请求之后动态渲染出来的HTML源码里根本看不到数据。遇到这种页面你先用浏览器开发者工具F12看一眼Network面板如果发现数据是通过XHR接口拿的那恭喜你反而更好办——直接用XMLHTTP去请求那个接口就行。如果数据显示逻辑特别绕前端堆了很多自定义框架那才需要考虑回退到WebBrowser控件这类方案。另外如果目标页面有非常严格的反爬机制要求验证码、人脸识别、滑块拖动这类交互XMLHTTP也搞不定因为它在设计上就不是给你模拟真人操作用的。2. 一个网络请求的完整生命周期XMLHTTP核心方法与状态流转XMLHTTP用起来其实只有四步创建对象、发送请求、等待响应、读取结果。但网上很多教程把代码直接一贴没有人讲清楚每一步背后的机制导致遇到问题不知道怎么排查。我这一节把它的方法和状态讲透后面你写代码心里就有底了。2.1 创建对象时常见的三种写法在VBA里创建XMLHTTP对象常见写法有这么几种Dim req As Object Set req CreateObject(MSXML2.XMLHTTP) 或者 Set req CreateObject(Microsoft.XMLHTTP) 或者指定版本 Set req CreateObject(MSXML2.XMLHTTP.6.0)MSXML2.XMLHTTP是标准写法大部分Windows机器都能正常创建。Microsoft.XMLHTTP是早期版本遗留的ProgID老代码里经常见到兼容性也很好但既然有更新的选择新代码不建议用。MSXML2.XMLHTTP.6.0指定的是MSXML 6.0版本更安全更稳定如果你的运行环境确定装了MSXML6指定版本会更严谨。我在代码里一般写成不指定版本号的MSXML2.XMLHTTP理由很简单它在不同机器上的兼容性最好。有些老机器或者精简版系统可能没有注册MSXML6但MSXML2.XMLHTTP这个ProgID基本上开箱即用。如果你在某个客户机器上遇到创建失败就换另一个ProgID试试三个总有一个能用。2.2 核心方法Open、Send、SetRequestHeaderOpen方法是整个请求的起点它告诉对象“你想怎么请求、请求哪里”req.Open GET, https://example.com/api/data, False第一个参数是请求方法常见的有GET和POST后面会细讲。第二个参数是URL地址。第三个参数最容易被新手忽略——它控制同步还是异步False同步模式。代码会卡在这一行直到服务器返回响应才继续往下走。VBA初学者极其推荐先用这个模式因为程序逻辑是顺序的不容易出幺蛾子。True异步模式。代码不会等服务器返回立刻往下执行。后续需要通过onreadystatechange事件来感知请求是否完成。异步的优点是界面不会假死但代码复杂度高不少一般等熟练了再说。Send方法负责把请求真正发出去。如果是GET请求直接写req.Send不需要传参数如果是POST请求就把请求体内容作为参数传进去比如req.Send usernameadminpassword123456。SetRequestHeader用来设置请求头必须在Send之前调用。最常见的两个请求头是User-Agent和Content-Type我们在后面“踩坑”部分再展开这里先记住一句话请求头是服务器判断“你是谁、你想干嘛”的第一道关卡。2.3 readyState与status看懂请求到哪一步了XMLHTTP对象有一个readyState属性表示请求当前处于什么状态readyState含义说明0未初始化已经创建XMLHTTP对象但Open还没调用1正在加载Open已调用请求还没发出去2已加载Send已调用已经拿到响应头3交互中正在接收响应体数据4完成响应接收完毕可以读取内容了如果你用的是同步模式Send这一行执行完readyState一定是4因为代码逻辑上就是“等它干完活再放你走”。这也是同步请求写起来省心的原因——你根本不需要关心中间状态。只有异步模式下才需要在一个事件回调里判断readyState是否等于4。status属性则是HTTP状态码你只需要记住几个常见的200代表成功301和302代表重定向404代表资源不存在500表示服务器内部出错。判断请求是否成功永远要同时看status和readyState。我见过不少初学者只判断responseText是否为空结果服务器返回了一个错误提示页面他以为是正常数据后面解析时各种报错。3. 三个可以直接拿去改的实战示例抓网页、调接口、发POST概念讲再多不如代码来得直接。这一节我给出三个最常用的实战模板代码都验证过你直接复制到VBA编辑器里改成自己的目标地址就能跑。3.1 示例一抓取一个静态网页的HTML源码这是最基础的需求用XMLHTTP把网页源码拿回来然后你可以用正则表达式或者字符串函数从里面提取自己想要的内容。Sub FetchPageDemo() Dim req As Object Dim sUrl As String Dim sResult As String sUrl https://example.com/page 改成你自己的目标地址 Set req CreateObject(MSXML2.XMLHTTP) req.Open GET, sUrl, False req.Send If req.Status 200 Then sResult req.responseText 这里就可以对 sResult 做提取了 Debug.Print Left(sResult, 500) Else Debug.Print 请求失败 req.Status req.StatusText End If Set req Nothing End Sub注意我这个代码里加了status判断。你可能会问不就抓个网页吗直接请求不就完了不网络请求这件事失败才是常态。目标网站可能临时挂了、可能改地址了、可能因为你请求头不对把你拦了。如果不做状态判断拿到一个404页面你后面的解析代码全是白跑而且你找不到原因。3.2 示例二调用一个JSON接口并取出数据现在的Web应用很少直接返回HTML了更多的接口直接给你JSON。VBA里没有内置的JSON解析器这是很多新手觉得JSON接口难用的原因。这一节我先用最简单的方式讲思路完整的JSON解析在专题后面会专门展开。Sub FetchJsonDemo() Dim req As Object Dim sResult As String Set req CreateObject(MSXML2.XMLHTTP) req.Open GET, https://example.com/api/weather?citybeijing, False req.Send If req.Status 200 Then sResult req.responseText 假设返回的是 {city:beijing,temp:25} 简单提取 temp 的值 Dim sTemp As String Dim lPos As Long lPos InStr(sResult, temp:) If lPos 0 Then sTemp Mid(sResult, lPos 7) sTemp Split(sTemp, })(0) Debug.Print 温度 sTemp End If End If Set req Nothing End Sub用InStr和Split去手工拆JSON只适用于结构非常简单的接口。一旦JSON嵌套层数变多、有数组有对象手工拆就是灾难。我建议你后续用成熟方案比如引入VBA-JSON模块或者配合ScriptControl调用JavaScript的JSON.parse。这部分的完整方案专题后面专门安排章节讲。3.3 示例三模拟表单POST提交很多登录操作、搜索提交、表单录入本质都是向服务器发一个POST请求。POST和GET最大的区别是GET把参数拼在URL问号后面POST把参数放在请求体里。XMLHTTP处理POST非常简单Sub FetchPostDemo() Dim req As Object Dim sParam As String sParam usernameadminpassword123456tokenabc Set req CreateObject(MSXML2.XMLHTTP) req.Open POST, https://example.com/api/login, False req.setRequestHeader Content-Type, application/x-www-form-urlencoded req.Send sParam If req.Status 200 Then Debug.Print req.responseText End If Set req Nothing End Sub有个细节必须提醒你表单POST一定要设置Content-Type这个请求头否则很多服务器会拒绝解析你的请求体。这是新人最容易漏掉的一步。另外像密码这类敏感信息千万不要直接硬编码到代码里更不要往URL里拼这是个坏习惯。3.4 处理带中文的URL参数做网络请求中文参数是绕不开的坑。URL本身只支持ASCII字符所以中文参数必须做百分号编码UTF-8的每个字节都转成%XX的形式。VBA没有内置的EncodeURIComponent函数我提供一个自己常用的方案Function URLEncodeUTF8(ByVal sText As String) As String Dim stream As Object Dim binData As Variant Dim i As Long Dim byteVal As Byte Set stream CreateObject(ADODB.Stream) stream.Type 2 文本模式 stream.Charset UTF-8 按UTF-8编码写入 stream.Open stream.WriteText sText stream.Position 0 stream.Type 1 切到二进制模式 binData stream.Read stream.Close For i 0 To UBound(binData) byteVal binData(i) URLEncodeUTF8 URLEncodeUTF8 % Right(0 Hex(byteVal), 2) Next i End Function使用方式很简单Dim sUrl As String sUrl https://example.com/api/search?keyword URLEncodeUTF8(苹果)原理不复杂先把字符串按UTF-8编码转成字节数组再把每个字节转成两位十六进制前面加百分号。用ADODB.Stream做转换的好处是Windows自带这个组件不需要额外引用。如果你的目标服务器是GBK编码而不是UTF-8把Charset那行改成GBK即可。核心思路就是一句话URL里别直接放中文先编码再说。4. 那些年我在XMLHTTP上面栽过的坑编码、超时与请求头XMLHTTP的使用门槛不高但实际开发里翻车次数绝对不少。这一节我把踩过最深的几个坑拿出来说每一个都让你省几个小时。4.1 中文乱码responseText与responseBody的选择这是网上问得最多的问题。用XMLHTTP请求一个UTF-8编码的网页responseText读出来全是乱码RequestBody乱码、页面标题乱码、JSON解析失败。你可能会觉得XMLHTTP太不智能了其实问题在于responseText字符串本身已经用某种编码解过一次码了但MSXML对字符集的识别并不总是准确尤其当响应头里没有明确声明charset时。我的做法是不直接用responseText而是从responseBody拿到原始的二进制数据再用ADODB.Stream按我指定的编码转成字符串Function BytesToText(ByVal binData As Variant, ByVal charset As String) As String Dim stream As Object Set stream CreateObject(ADODB.Stream) stream.Type 1 二进制模式 stream.Open stream.Write binData stream.Position 0 stream.Type 2 文本模式 stream.Charset charset 如 UTF-8 或 GBK BytesToText stream.ReadText stream.Close End Function调用方式 拿原始字节 Dim bBody As Variant bBody req.responseBody 按UTF-8解码 Debug.Print BytesToText(bBody, UTF-8)这一招几乎能解决所有乱码问题。你先用F12看一眼响应头里的charset如果页面是GBK编码就把第二个参数改成GBK。代码不用大改一个统一函数全搞定。4.2 请求卡死XMLHTTP超时问题与替代方案XMLHTTP这个对象有一个很尴尬的短板它没有内置的Timeout属性。如果目标服务器突然不响应了你的代码可能会一直挂在那里Excel界面直接假死等十几秒甚至几分钟都是常事。网上有些“曲线救国”的写法比如用异步加定时器配合Abort方法实现超时但代码复杂度一下就上去了。一个更省事的方案是在需要严格超时控制的场景改用WinHttp.WinHttpRequest.5.1。它和XMLHTTP的用法类似但自带SetTimeouts方法Dim http As Object Set http CreateObject(WinHttp.WinHttpRequest.5.1) http.SetTimeouts 5000, 5000, 5000, 8000 解析/连接/发送/接收 超时时间单位毫秒 http.Open GET, sUrl, False http.Send Debug.Print http.ResponseText顺序别记错四个超时时间分别是域名解析、建立连接、发送请求、接收响应。我把接收超时设为8秒其它都设5秒基本能满足日常需要。还有一个容易被忽略的坑如果请求使用的是HTTPS而服务器证书刚好有问题比如公司内网用的自签名证书XMLHTTP可能会直接报错或者一直挂住。WinHttp可以通过Option调整证书校验策略这个在信任的内部测试环境里可以了解一下生产环境建议还是保证证书正确别为了省事牺牲安全性。4.3 被服务端拦截User-Agent和Referer的伪装有些网站会检查请求的来源发现User-Agent是MSXML就直接拒绝。这时候你需要在请求头里伪装成正常浏览器。我的惯例是拿到什么站就模拟什么浏览器的请求头下面是一段常用的配置req.setRequestHeader User-Agent, Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36 req.setRequestHeader Referer, https://example.com/ req.setRequestHeader Accept, application/json, text/javascript, */*; q0.01有个实操技巧不知道该怎么填这些请求头时用Chrome打开目标网页F12打开开发者工具切到Network网络面板刷新页面找到对应请求右键选择“Copy as cURL”。这段cURL里的Header信息就是你伪装请求的最佳参考。4.4 4开头和5开头的状态码别忽略我在前面已经强调过一次status判断这一步实在太重要了再单独拿出来讲一下。很多人写代码的时候只判断了“有没有返回内容”没判断“服务器到底给了我什么状态”。服务器返回一个404页面内容也很多你的正则照样匹配不到东西最后一脸蒙圈。我的代码里统一这么处理If req.Status 200 Then 正常处理 Else Debug.Print HTTP错误 req.Status req.StatusText Debug.Print 响应内容 Left(req.responseText, 200) Exit Sub End If把状态码和错误页面的前200个字符一起打出来排查时一眼就能看清是地址错了、被拦截了还是服务器真出问题了。5. 站在HTTP协议角度看XMLHTTPURL、Header与状态码速成这一节是“相关概念介绍”的重点。很多人用XMLHTTP只会照抄代码遇到问题却不知道怎么排查根本原因是不懂HTTP协议的基本概念。我花点篇幅把最关键的知识串一遍这些内容以后不管你是用VBA还是Python都用得上。5.1 URL的组成协议、主机、端口、路径、查询参数一个完整URL长这样https://example.com:443/api/getdata?citybeijingpage1#top拆开看就是https协议告诉客户端用HTTPS加密通信example.com主机名:443端口号HTTPS默认443可以省略HTTP默认80也可以省略/api/getdata路径服务器上资源的定位?citybeijingpage1查询参数GET请求传递数据的主要方式#top锚点纯前端概念不会发送给服务器你在XMLHTTP的Open方法里填的URL本质上就是把这些信息拼好。写代码时最常见的错误是查询参数忘了URL编码或者路径里带了空格——记住URL里不要出现中文和空格有就编码。5.2 请求方法GET与POST以及更多HTTP请求方法有好几种GET、POST、PUT、DELETE、PATCH等。日常用得最多的是GET和POST。GET向服务器要数据。参数拼在URL里适合查询操作。优点是可以直接复制链接分享缺点是参数会暴露在日志里不适合传递敏感信息而且URL长度有限制。POST向服务器提交数据。参数放在请求体里适合创建资源、提交表单。请求体格式由Content-Type声明常见的有application/x-www-form-urlencoded表单、application/jsonJSON、multipart/form-data文件上传。你可以把GET理解成“在超市货架上拿货”把POST理解成“把购物车里的东西交给收银台结账”。XMLHTTP对这两者都支持得很好代码就一两行区别。5.3 请求头与响应头你与服务器的“暗号”HTTP Header是请求和响应里的“元信息”它告诉对方“我是谁、我要什么、我带了什么格式的数据”。常用的请求头Header作用User-Agent客户端身份标识服务器靠它识别你是浏览器还是脚本Referer告诉服务器你从哪个页面跳转过来的Content-Type请求体的媒体类型Accept期望服务器返回的数据格式Cookie携带会话状态表示“我已经登录过了”响应头里也有几个常用字段Content-Type服务器返回的数据类型、Content-Encoding响应内容是否压缩、Set-Cookie服务器要求客户端保存的Cookie。在VBA里可以通过req.getResponseHeader(Content-Type)来读取单个响应头用req.getAllResponseHeaders()读取全部响应头。排查问题时把响应头打出来看一下很多玄学问题都能找到线索。比如Content-Type是text/html而你以为是application/json那后面的解析逻辑自然就是错的。5.4 状态码2xx、3xx、4xx、5xx状态码是服务器给你的最直接的回应两位数开头的数字决定了请求的结局状态码范围含义常见代码2xx成功200正常、201已创建3xx重定向301永久重定向、302临时重定向4xx客户端错误404不存在、403禁止访问、401未认证5xx服务器错误500服务器内部错误、502网关错误、503服务器过载XMLHTTP在大多数情况下会自动跟随重定向所以你在VBA里通常看到的是重定向之后的最终状态码。但4xx和5xx不会自动跳过你必须自己在代码里处理。5.5 字符集与Cookie两个最常被忽略的概念字符集就是编码方式。网页的Content-Type里通常有一个charset参数表示页面用什么编码。同一个“中”字UTF-8编码和GBK编码的字节完全不同。这也是为什么我们前面的BytesToText函数一定要指定charset。Cookie则是服务器在客户端存的小数据片段主要用来维持登录状态。XMLHTTP本身不处理Cookie——它会把响应里的Set-Cookie存下来后续请求同一个域名时自动带上但跨域名、手动管理Cookie这些操作需要你自己处理。在专题后续讲“模拟登录”时我会专门演示怎么利用这一机制保持会话。明白了HTTP的这套基础你就不会再把XMLHTTP看成一堆陌生的英文单词它本质上就是一个“翻译”把你的请求意图交给服务器再把服务器的答复原样交回给你。最后闲谈几句个人经验。我在实际项目里用XMLHTTP的频率非常高但真正决定“这个接口能不能抓”的其实不是VBA代码而是你愿不愿意打开F12去分析网络请求。与其反复试代码不如先花十分钟看清楚目标页面发的是什么请求、参数怎么构造、响应是什么格式。有了这个习惯XMLHTTP对你来说就不是一个“神秘的联网黑盒”而是一把趁手的数据钥匙。下一节我们正式进入JSON数据的结构化处理到时候你就能体会到“获取数据只是开始处理数据才是大头”这句话的意思了。