简介面向需要在 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 ProviderMicrosoft.Jet.OLEDB.4.0;Extended PropertiesExcel 8.0;hdrno;;DataSource ActiveWorkbook.FullName这里有两个容易写错的点。Extended Properties的值必须用单引号包住整个Excel 8.0;hdrno;分号不能丢否则驱动会报无法识别数据库格式。DataSource在 Jet 驱动下接受完整路径在 ACE 驱动下如果路径里有中文建议先用ThisWorkbook.Path拼好再传避免编码问题。参数可选值作用ProviderMicrosoft.Jet.OLEDB.4.0 / Microsoft.ACE.OLEDB.12.0Jet 支持 xlsACE 支持 xls 和 xlsxExtended PropertiesExcel 8.0 / Excel 12.0对应 97-2003 格式和 2007 格式hdryes / no第一行是否作为列名DataSource文件完整路径指向物理文件不要求文件打开提示如果你的 xlsx 文件用 Jet 连接会报错直接换成 ACE 驱动并把Excel 8.0改成Excel 12.0。hdrno时列名变成 f1、f2、f3 这种系统命名hdryes时直接用第一行的中文或英文表头作列名。2.2 Connection.Execute 与 Recordset.Open 的分工同一份数据实例集里给出了两种执行姿势。第一种是直接用conn.Executesql1 select 物料代码,物料描述,属性,单位 from [物料代码表$] where 属性 采购 ThisWorkbook.Sheets(sheet1).Cells(2, 1).CopyFromRecordset conn.Execute(sql1)conn.Execute返回一个只读向前的记录集配合CopyFromRecordset粘贴最快适合一次性把结果倒进工作表。但它不支持主动控制游标位置也不方便读取返回的行数。第二种是Recordset.OpenDim 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. 按列、行、单元格取数的三种写法与 hdrno 的定位规则3.1 hdrno 时 f1/f2 如何对应 Excel 列实例集中引用一列的例子值得仔细看Conn.Open providermicrosoft.jet.oledb.4.0;extended propertiesexcel 8.0;hdrno;datasource ThisWorkbook.Path \1.xls Sql select f1 from [sheet1$] [a1].CopyFromRecordset Conn.Execute(Sql)hdrno的表单里f1 对应 A 列f2 对应 B 列f13 对应 M 列f24 对应 X 列。这个编号规则在select f24 - f25这种表达式里会直接参与运算比如实例里的f24 - f25 f17表示第 24 列减第 25 列小于第 17 列SQL 引擎会按数值类型自动计算。要注意的是f 编号拿到的列类型取决于该列第一行数据的值如果该列第一行是文本、后面都是数字f24 - f25会报类型不匹配处理办法是在Extended Properties里追加IMEX1把混合类型列按文本读取再在 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 ALL4.1 空值判断与字符串条件的 SQL 写法实例集开头那段订单生成系统里的条件写法把两个最常见的坑一起演示了sql select f6,f2,f3,f4,f5,f7,f13,f24-f25 from [sheet1$] _ where f24-f25f17 and (f13C3 or f13 is null)第一个坑是不等于某个值要写对应字符串时要加单引号C3。第二个坑是空值判断必须用is null写成f13 null是永远查不到数据的因为 SQL 的空值不参与等值比较。or f13 is null的括号不能省否则and的优先级会把它和前面的f24 - f25 f17绑在一起逻辑就变了。常见的进销存场景里未填属性的记录和属性不为 C3 的记录是两个不同的筛选目标合并写时一定要用括号明确优先级。4.2 group by 的坑为什么产品代码不能汇总实例集中进销存汇总给了一条会报错的 SQLSql select 产品代码, sum(进货数量), sum(进货金额) from [进货$] group by 产品代码如果原表里存在产品代码相同但进货单价不同的记录直接按产品代码分组没问题。真正报错的场景是 select 里混入了既不在聚合函数里、也不在 group by 里的列比如某次改写加了进货单价却没把它加进 group by引擎会提示产品代码不能汇总。正确处理是让所有非聚合列都进 group bySql select 产品代码, ,sum(进货数量),进货单价,sum(进货金额) _ from [进货$] group by 产品代码, 进货单价 的作用是输出一列空字符串占位保证粘贴到 Excel 里列位置和表头对齐。group by 写列名时要注意大小写不敏感但中文列名必须和表头完全一致多一个空格都会报错。再加一列库存结余时直接在 select 里写sum(进货数量)-sum(销售数量)这个表达式会在内存里先聚合再相减结果放到 Excel 后就是现成的期末结存。4.3 多表连接与 UNION ALL 的汇总差别三表连接是进销存里最常见的需求实例集给出了一条能算毛利和库存的完整 SQLSql 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 列已有内容就跳过——这个设计是为了防止重复汇总。真正干活的是huiz1122f nm .xls Set conn New ADODB.Connection conn.Open providerMicrosoft.ACE.OLEDB.12.0;Extended PropertiesExcel 12.0;hdryes;data source ThisWorkbook.Path \ fhdryes下可以直接用中文列名不用关心 f 编号。这种写法的收益是几十个分表文件不会被逐个打开屏幕不闪烁数据量再大也不会因为 Excel 打开过多工作簿导致内存暴涨。封装的要点是把连接串的Data Source部分动态拼接循环里每次只改文件名连接打开用完就Close。5.2 验证连接释放与筛选日期边界排错时最实用的验证方法是看任务管理器里有没有残留的进程。Jet 和 ACE 驱动在 Excel 中打开连接后如果代码中途Exit Sub前没有Set conn NothingExcel 关闭时偶尔会报内存或磁盘空间不足。一个稳妥的做法是统一用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 里猜快得多。本文还有配套的精品资源点击获取