简介：面向需要在 Excel 中通过 VBA 连接 SQL 数据库的办公自动化人员与数据分析师，这份梳理文档聚焦 ADO 技术的实际落地，内容深浅适中，适合已掌握 Excel 基础操作、希望进一步提升数据自动化处理能力的读者。包内含 1 个 doc 文件，约 282KB，以代码片段与注释说明为主，覆盖使用 Worksheet_Activate 事件触发查询、基于 ADO Connection 对象执行 SQL、以及通过 Recordset 完成单次查询等典型场景，并针对字段引用、空值判断、表头赋值、单列数据读取等细节给出完整具体示例。已有 754 人学习下载。读者可从中获得可直接改用的 VBA 代码模板，理解连接字符串、SQL 语句写法与 Excel 工作表之间的数据交互方式，也能看到日常订单生成、物料查询等实际业务场景中的调用思路，对快速搭建自己的 Excel 数据查询工具很有帮助，能帮助读者少走弯路。
1. 用 VBA 把 Excel 变成 SQL 查询终端：从 ADO 连接串讲起
Excel 用户最大的错觉，是以为 SQL 必须装个 SQL Server 才能用。实际上只要机器上有 Jet 或 ACE 驱动，Excel 自己就能通过 VBA 的 ADO 对象把工作簿当成数据库来查，而且查询对象可以是当前文件、另一个 xls 文件，甚至是根本没打开的 xls。这套写法的核心价值在于：数据量几千行时 VBA 循环还能扛，一旦到几万行，用数组遍历和字典匹配就会明显变慢，而一条 group by 聚合的 SQL 往往毫秒级返回。本文整理的这套实例覆盖了空值判断、行列定位、多表连接、跨工作簿汇总和按时间段筛选，适合每天要和进销存、物料表、发票明细打交道的 Excel 重度用户，也适合想把 Excel 当轻量 BI 工具用的数据分析岗。所有代码都能在 Excel 2007 到 365 之间直接运行，区别只在驱动选 Jet 还是 ACE。
2. ADO 连接串与两种执行对象：Connection 与 Recordset 的取舍
2.1 连接串参数逐个拆解
先看一段最常见的连接代码，它来自实例集中的"订单生成系统"：
Dim x As Object Set x = CreateObject("ADODB.Connection") x.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=no;';DataSource=" & ActiveWorkbook.FullName这里有两个容易写错的点。Extended Properties的值必须用单引号包住整个Excel 8.0;hdr=no;，分号不能丢，否则驱动会报"无法识别数据库格式"。DataSource在 Jet 驱动下接受完整路径，在 ACE 驱动下如果路径里有中文，建议先用ThisWorkbook.Path拼好再传，避免编码问题。
| 参数 | 可选值 | 作用 |
|---|---|---|
| Provider | Microsoft.Jet.OLEDB.4.0 / Microsoft.ACE.OLEDB.12.0 | Jet 支持 xls，ACE 支持 xls 和 xlsx |
| Extended Properties | Excel 8.0 / Excel 12.0 | 对应 97-2003 格式和 2007+ 格式 |
| hdr | yes / no | 第一行是否作为列名 |
| DataSource | 文件完整路径 | 指向物理文件，不要求文件打开 |
提示：如果你的 xlsx 文件用 Jet 连接会报错，直接换成 ACE 驱动，并把Excel 8.0改成Excel 12.0。hdr=no时列名变成 f1、f2、f3 这种系统命名，hdr=yes时直接用第一行的中文或英文表头作列名。
2.2 Connection.Execute 与 Recordset.Open 的分工
同一份数据，实例集里给出了两种执行姿势。第一种是直接用conn.Execute：
sql1 = "select 物料代码,物料描述,属性,单位 from [物料代码表$] where 属性= '采购'" ThisWorkbook.Sheets("sheet1").Cells(2, 1).CopyFromRecordset conn.Execute(sql1)conn.Execute返回一个只读向前的记录集，配合CopyFromRecordset粘贴最快，适合一次性把结果倒进工作表。但它不支持主动控制游标位置，也不方便读取返回的行数。
第二种是Recordset.Open：
Dim rd As ADODB.Recordset Set rd = New ADODB.Recordset sql1 = "select 物料代码,物料描述,属性,单位 from [物料代码表$] where 属性= '采购'" rd.Open sql1, sConnect, adOpenForwardOnly, adLockReadOnlyrd.Open的第二个参数sConnect是连接串，不是 Connection 对象，这意味着可以不开conn.Open直接查，适合一次性查询。adOpenForwardOnly对应游标类型，adLockReadOnly对应锁定类型，这两个值组合是查询场景性能最好的配置。如果需要修改数据，才换成adOpenKeyset加adLockOptimistic。用过之后记得rd.Close和Set rd = Nothing，否则 Excel 关闭时可能提示内存不足。
2.3 CopyFromRecordset 输出与表头重建
CopyFromRecordset只会写入数据，不会写入字段名，所以实例集的所有示例都用Array或逐格赋值的方式重建表头：
Range("a1:h1") = Array("编号", "品名", "规格", "产地", "单位", "件装", "属性", "计划") [a2].CopyFromRecordset yyRange("a1:h1") = Array(...)是给连续区域的单元格一次性赋值，效率比Cells(1,1) = "编号"这种逐格写法高。数据从 A2 开始写，是因为 A1 被表头占了。如果你的查询结果可能为空，建议先清空目标区域再写，避免上一轮的残留数据和新结果混在一起。On Error Resume Next只能用于连接测试阶段，正式交付的代码里应该删掉，否则 SQL 语法错误会被静默吞掉，排查时非常痛苦。
3. 按列、行、单元格取数的三种写法与 hdr=no 的定位规则
3.1 hdr=no 时 f1/f2 如何对应 Excel 列
实例集中"引用一列"的例子值得仔细看：
Conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=no';datasource=" & ThisWorkbook.Path & "\1.xls" Sql = "select f1 from [sheet1$]" [a1].CopyFromRecordset Conn.Execute(Sql)hdr=no的表单里，f1 对应 A 列，f2 对应 B 列，f13 对应 M 列，f24 对应 X 列。这个编号规则在select f24 - f25这种表达式里会直接参与运算，比如实例里的f24 - f25 < f17表示"第 24 列减第 25 列小于第 17 列"，SQL 引擎会按数值类型自动计算。要注意的是，f 编号拿到的列类型取决于该列第一行数据的值，如果该列第一行是文本、后面都是数字，f24 - f25会报类型不匹配，处理办法是在Extended Properties里追加IMEX=1，把混合类型列按文本读取，再在 SQL 里用CInt或CDbl转换。
3.2 区间查询：从引用一行到引用一个单元格
select * from [sheet1$a1:iv1]是引一行，select * from [sheet1$k1:k1]是引一个单元格。这个写法的原理是把工作表的某个矩形区域当作独立的"表"来查询：
Sql = "select * from [sheet1$k1:k1]" [a1].CopyFromRecordset Conn.Execute(Sql)[sheet1$k1:k1]表示只读取 K1 单元格，[sheet1$a1:iv1]表示读取整个第一行。区域引用在连接串里不需要额外声明，SQL 引擎会把区域内的单元格当成二维表数据返回。用这个特性做"从各分表取固定位置的指标"非常灵活，比如每个月报表的结构固定，要把每个文件的 C14、C15、C16 三个单元格抽到汇总表，就可以循环打开每个文件执行三次select。
3.3 遍历文件夹批量提取单元格：FileList 与动态数据源
实例集里有一段完整的文件遍历代码，核心是Dir函数配合Split生成文件列表：
Function FileList(fldr, Optional fltr As String = "*.xls") As Variant Dim sTemp As String, sHldr As String If Right$(fldr, 1) <> "\" Then fldr = fldr & "\" sTemp = Dir(fldr & fltr) If sTemp = "" Then FileList = False Exit Function End If Do sHldr = Dir If sHldr = "" Then Exit Do sTemp = sTemp & "|" & sHldr Loop FileList = Split(sTemp, "|") End FunctionFileList返回一个数组，数组元素是目录下所有 xls 文件名。它的巧妙之处在于利用Dir无参调用时返回下一个匹配文件名的特性，把零散的文件名用|拼接，最后再用Split切成数组。外层调用代码里，先判断返回值是不是 Boolean 类型的False，避免空目录时直接赋值给For循环。拿到文件列表后，逐个Conn.Open读取指定单元格，再写到汇总表的Myr行。这个模式非常适合处理"几十个分表结构相同、需要汇总到一张总表"的重复劳动。
4. 聚合、连接与多表汇总：从 group by 到 UNION ALL
4.1 空值判断与字符串条件的 SQL 写法
实例集开头那段"订单生成系统"里的条件写法，把两个最常见的坑一起演示了：
sql = "select f6,f2,f3,f4,f5,f7,f13,f24-f25 from [sheet1$] " & _ "where f24-f25<f17 and (f13<>'C3' or f13 is null)"第一个坑是"不等于某个值"要写<>，对应字符串时要加单引号'C3'。第二个坑是空值判断必须用is null，写成f13 = null是永远查不到数据的，因为 SQL 的空值不参与等值比较。or f13 is null的括号不能省，否则and的优先级会把它和前面的f24 - f25 < f17绑在一起，逻辑就变了。常见的进销存场景里，"未填属性的记录"和"属性不为 C3 的记录"是两个不同的筛选目标，合并写时一定要用括号明确优先级。
4.2 group by 的坑：为什么"产品代码"不能汇总
实例集中进销存汇总给了一条会报错的 SQL：
Sql = "select 产品代码, sum(进货数量), sum(进货金额) from [进货$] group by 产品代码"如果原表里存在产品代码相同但进货单价不同的记录，直接按产品代码分组没问题。真正报错的场景是 select 里混入了既不在聚合函数里、也不在 group by 里的列，比如某次改写加了进货单价却没把它加进 group by，引擎会提示"产品代码不能汇总"。正确处理是让所有非聚合列都进 group by：
Sql = "select 产品代码,' ',sum(进货数量),进货单价,sum(进货金额) " & _ "from [进货$] group by 产品代码, 进货单价"' '的作用是输出一列空字符串占位，保证粘贴到 Excel 里列位置和表头对齐。group by 写列名时要注意大小写不敏感，但中文列名必须和表头完全一致，多一个空格都会报错。再加一列库存结余时，直接在 select 里写sum(进货数量)-sum(销售数量)，这个表达式会在内存里先聚合再相减，结果放到 Excel 后就是现成的期末结存。
4.3 多表连接与 UNION ALL 的汇总差别
三表连接是进销存里最常见的需求，实例集给出了一条能算毛利和库存的完整 SQL：
Sql = "select A.产品代码,A.名称,sum(B.进货数量),B.进货单价,sum(B.进货金额)," & _ "sum(C.销售数量),C.销售单价,sum(C.销售金额), " & _ "sum(C.销售数量)*(C.销售单价-B.进货单价),sum(B.进货数量)-sum(C.销售数量) " & _ "from [产品资料$] as A,[进货$] as B,[销售$] as C " & _ "where A.产品代码=B.产品代码 and B.产品代码=C.产品代码 " & _ "group by A.产品代码,A.名称,B.进货单价,C.销售单价"[产品资料$] as A这种写法是把 Excel 工作表当成关系表起别名，where里的等值连接条件决定三张表怎么关联。group by里必须包含所有非聚合列，所以A.产品代码、A.名称、B.进货单价、C.销售单价一个都不能少。这里最容易被忽略的是B.进货单价和C.销售单价是"按单价分组"，如果同一种商品进货价有两种，它会分成两行输出，这正是设计意图——让你看清每个价位的进货量和销售量分别多少。
跨月汇总时用UNION ALL比用循环遍历再累加更省事：
sq4 = sq1 & " UNION ALL " & sq2 & " UNION ALL " & sq3 sq5 = "select 编号,日期,发票号,客户,案类,案号,律师,业务量,合作人,项目," & _ "SUM(金额),sum(收入),sum(应收),备注 from (" & sq4 & ") " & _ "GROUP BY 编号,日期,发票号,客户,案类,案号,律师,业务量,合作人,项目,备注 order by 发票号"UNION ALL会把三个子查询的结果纵向堆叠，(sq4)子查询生成临时结果集，外层再对它 group by。注意UNION ALL保留重复行，UNION会去重；发票明细这类业务数据应该用UNION ALL，不然同日同客户两条记录可能被错误合并。排序放在外层查询的末尾，order by只能出现在最外层。
5. 不打开工作簿也能汇总：ADO 直读外部文件的两种实践
5.1 用 Worksheet_SelectionChange 触发外部文件读取
实例集最后的"不打开工作簿汇总"把 ADO 的优势发挥到了极致——目标文件处于关闭状态也能查。它的触发方式很实用，写在Worksheet_SelectionChange事件里，当用户在 A 列点击单元格时自动读取同名外部文件：
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column <> 1 Then Exit Sub If Target.Offset(0, 1) <> "" Then Exit Sub Call huiz1122 End SubTarget.Count > 1过滤掉多选区域，Target.Column <> 1限定只有点在 A 列才触发，Target.Offset(0, 1) <> ""表示 B 列已有内容就跳过——这个设计是为了防止重复汇总。真正干活的是huiz1122：
f = nm & ".xls" Set conn = New ADODB.Connection conn.Open "provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;hdr=yes';data source=" & ThisWorkbook.Path & "\" & fhdr=yes下可以直接用中文列名，不用关心 f 编号。这种写法的收益是：几十个分表文件不会被逐个打开，屏幕不闪烁，数据量再大也不会因为 Excel 打开过多工作簿导致内存暴涨。封装的要点是把连接串的Data Source部分动态拼接，循环里每次只改文件名，连接打开用完就Close。
5.2 验证连接释放与筛选日期边界
排错时最实用的验证方法是看任务管理器里有没有残留的进程。Jet 和 ACE 驱动在 Excel 中打开连接后，如果代码中途Exit Sub前没有Set conn = Nothing，Excel 关闭时偶尔会报"内存或磁盘空间不足"。一个稳妥的做法是统一用Function封装连接对象，每次在End Sub前集中清理：
conn.Close Set conn = Nothing Set rs = Nothing日期筛选的边界也要单独提一下。between #" & dd & "# and #" & ee & "#的写法里，#是 Access/Jet 的日期分隔符。这里dd和ee取自单元格，如果单元格里是文本格式的日期，Jet 可能按字符串比较导致结果错位，建议先CDate转换再拼进 SQL。多条件与区间统计的实例里，循环里反复用GoTo 100跳过某些列，这种写法在逻辑上没有问题，但正式项目里更推荐用If 条件 Then 执行 summary Else 执行 range的结构，避免跳转把阅读顺序打散。调试时如果 SQL 报语法错误，把拼接好的sql变量用Debug.Print sql打出来，粘到 Access 查询设计器里执行，定位问题比在 VBA 里猜快得多。
本文还有配套的精品资源，点击获取
