网页数据抓取？VBA让Excel变身爬虫工具
一句话核心价值点：不用学复杂语言，10行左右VBA代码就能让Excel自动抓网页数据，零基础也能把重复查表活儿变成一键完成。
目录
手动复制太慢？一键批量抓表格
办公室常遇到：领导让把某网站的产品报价表贴到Excel，几十页一个个复制粘贴，手酸眼花还易漏。函数公式只能抓固定页面，数据透视表更没法跨网页跑，VBA却能直连网址读表，省去人工搬运。
Sub 抓网页表格() Dim url As String, tbl As Object, r As Long url = " https://example-data-site.com/price2026 " With CreateObject("MSXML2.XMLHTTP") .Open "GET", url, False .send ActiveSheet.Cells.Clear ActiveSheet.Range("A1").Value = "网页表格抓取结果" If .Status = 200 Then With CreateObject("HTMLFile") .write .body.innerHTML Set tbl = .getElementsByTagName("table")(0) For r = 0 To tbl.Rows.Length - 1 Cells(r + 2, 1).Value = tbl.Rows(r).Cells(0).innerText Cells(r + 2, 2).Value = tbl.Rows(r).Cells(1).innerText Next r End With End If End WithEnd Sub
原理很简单：用XMLHTTP发请求拿网页源码，HTMLFile解析后抓<table>标签，把行列文本直接塞进Excel单元格。只要网址不变结构，这招能秒级搬完原本半小时的复制活，小白改个网址就能复用。
动态翻页难搞？循环爬取全拿下
查销售数据时，网站分很多页，点下一页URL会变，函数公式盯不住，手工翻又累还易错。VBA能识别分页规律，自动一路点下去，把每页货品清单收齐。
Sub 循环抓分页() Dim url As String, pg As Integer, html As Object, tbl As Object, r As Long For pg = 1 To 5 url = " https://sales-data-2026.com/list?page= " & pg With CreateObject("MSXML2.XMLHTTP") .Open "GET", url, False .send If .Status = 200 Then Set html = CreateObject("HTMLFile") html.write .responseText Set tbl = html.getElementsByTagName("table")(0) For r = 0 To tbl.Rows.Length - 1 Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = tbl.Rows(r).Cells(0).innerText Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = tbl.Rows(r).Cells(1).innerText Next r End If End With Next pgEnd Sub
这里用页码变量pg拼不同网址，循环里每次请求新页并追加到表格尾部。函数公式只能盯当前页，数据透视表更不会翻页，VBA像请了个小工帮你不停点下一页。
多页合并乱套？自动整理进一表
有时要把同类数据从不同页面合一起，手工复制容易列对不齐，后期统计一团乱。VBA能在抓的同时按统一列排好，生成干净大表，省掉二次整理时间。
Sub 多页合并整理() Dim urls(2) As String, i As Integer, html As Object, tbl As Object, r As Long, c As Integer urls(0) = " https://dept1-2026.com/data " urls(1) = " https://dept2-2026.com/data " urls(2) = " https://dept3-2026.com/data " For i = 0 To 2 With CreateObject("MSXML2.XMLHTTP") .Open "GET", urls(i), False .send If .Status = 200 Then Set html = CreateObject("HTMLFile") html.write .responseText Set tbl = html.getElementsByTagName("table")(0) For r = 0 To tbl.Rows.Length - 1 For c = 0 To tbl.Rows(r).Cells.Length - 1 Cells(Rows.Count, c + 1).End(xlUp).Offset(1, 0).Value = tbl.Rows(r).Cells(c).innerText Next c Next r End If End With Next iEnd Sub
不同网址的表格结构一样时，这段代码按行列遍历直接堆进同一张表，列宽自动对齐。函数公式做不到跨源整合，透视表要先汇总再分析，VBA一步到位收齐还能马上算。
实时刷新卡壳？定时抓最新数据
市场行情天天变，早上抓的数据下午就旧，人工反复刷太费事。VBA可设定时器，每隔一阵自动跑一次抓取，让你打开表就是最新数。
Sub 启动定时抓() Application.OnTime Now + TimeValue("00:10:00"), "抓网页表格"End SubSub 抓网页表格() Dim url As String, tbl As Object, r As Long url = " https://live-price-2026.com/now " With CreateObject("MSXML2.XMLHTTP") .Open "GET", url, False .send Sheets("实时").Cells.Clear If .Status = 200 Then With CreateObject("HTMLFile") .write .body.innerHTML Set tbl = .getElementsByTagName("table")(0) For r = 0 To tbl.Rows.Length - 1 Sheets("实时").Cells(r + 1, 1).Value = tbl.Rows(r).Cells(0).innerText Sheets("实时").Cells(r + 1, 2).Value = tbl.Rows(r).Cells(1).innerText Next r End With End If End With Call 启动定时抓End Sub
OnTime让代码像闹钟，定好间隔自己执行。函数公式和数据透视表不会自动联网刷新，VBA却能让表格“活”起来。
跨站数据混抓？精准定位不怕乱
有时要从多个不同结构的站点抓关键字段，比如价格从A站、库存从B站，手工切来切去很烦。VBA能分别请求不同地址，只挑需要的那块信息，不必管其它干扰内容。
Sub 跨站混合抓() Dim url1 As String, url2 As String, html1 As Object, html2 As Object Dim price As String, stock As String url1 = " https://shopA-2026.com/item123 " url2 = " https://stockB-2026.com/item123 " With CreateObject("MSXML2.XMLHTTP") .Open "GET", url1, False .send Set html1 = CreateObject("HTMLFile") html1.write .responseText price = html1.getElementsByClassName("price-tag")(0).innerText .Open "GET", url2, False .send Set html2 = CreateObject("HTMLFile") html2.write .responseText stock = html2.getElementById("qty").innerText End With Sheets("混合").Range("A1").Value = "价格" Sheets("混合").Range("B1").Value = price Sheets("混合").Range("A2").Value = "库存" Sheets("混合").Range("B2").Value = stockEnd Sub
利用getElementsByClassName或getElementById精准锁定目标节点，不管页面多花哨都能直掏想要的数。函数和透视表只能处理已导入的固定区域，VBA却能在不同结构间灵活穿梭。
金句收尾：
表哥表姐不加班，靠的不是手速，是让代码替你跑腿。VBA像把万能钥匙，能开网页的门，也能串起散落的数据珍珠。学一点会一点，今天的一小步，明天效率一大跃。
VBA让Excel不止会算，还会找、会抓、会串，把跨网页的麻烦事化成稳稳的自动化幸福。别怕陌生，动手试一次，你会发现原来自己也能让数据乖乖排队报到。
更多干货点我头像进主页，每天更新
