简介这套Excel VBA链接SQL数据库的实例合集面向需要从Excel操作SQL数据源的办公自动化开发人员重点演示如何通过ADO技术实现数据查询、交互与结果回填。文档内含三个典型示例利用Worksheet_Activate事件连接当前工作簿、通过ADO Connection对象执行查询、使用Recordset记录集获取筛选结果均附有完整代码和中文注释。同时针对SQL语句中的空值判断、单引号转义、[$]工作表名引用、CopyFromRecordset复制记录集、表头赋值等常见易错点做了专门说明并补充了引用整列、多表合并等扩展场景可直接把代码移植到实际项目中。资料为单个doc文档文件大小282KB便于快速查阅与复制代码。目前已有754人学习借鉴适合有一定VBA基础但尚未打通SQL链路或希望规范代码写法的读者。1. Excel 用 VBA 链接 SQL 的实例这套代码能把 Excel 直接当成数据库来查很多人在 Excel 里处理多表汇总、跨工作簿取数、按条件统计时第一反应是写 VLOOKUP 或者数据透视表。等表多了、条件复杂了公式就成了一团乱麻运行还慢。这套《Excel 使用 VBA 链接 SQL 全部实例》解决的就是这个问题——用 ADO 把 Excel 工作簿自身当成数据库直接写 SQL 语句去查 sheet能跨表 join、能 group by 汇总、能按日期区间过滤而且全程不用打开目标工作簿。对每天跟进销存、订单、发票打交道的办公人员来说这是把 Excel 从“电子表格”升级成“查询工具”最直接的一批代码。适合已经会点 VBA 基础、但没系统写过 ADO 查询的读者也适合被多表汇总折磨过的表哥表姐们。2. 连接串是核心Provider、Extended Properties 与 HDR 参数的取舍这套实例里几乎所有代码都围绕同一个动作展开——用 ADODB.Connection 连接当前 Excel 工作簿然后执行 SQL 语句。别看代码长得差不多连接串里的每一个参数都会影响查询结果尤其是 HDR 这个开关。2.1 两种数据源连接的写法对比实例里提供了两种主流的连接方式第一种是后期绑定不依赖 VBE 里的引用勾选Set x CreateObject(ADODB.Connection) x.Open ProviderMicrosoft.Jet.OLEDB.4.0;Extended PropertiesExcel 8.0;hdrno;;DataSource ActiveWorkbook.FullName这段代码出现在订单生成系统的 Worksheet_Activate 事件里。它直接创建 ADO 连接对象不需要用户手动去“工具→引用”里勾选 Microsoft ActiveX Data Objects 库。Provider 用的是 Jet OLEDB 4.0这是 Office 2003 到 Office 2010 时代最常见的选择配合 Excel 8.0 格式正好对应 97-2003 的 .xls 文件。第二种是前期绑定需要在 VBA 编辑器里手动勾选引用Option Explicit Public conn As ADODB.Connection Sub Myquery() Dim sConnect$, sql1$ Set conn CreateObject(adodb.connection) Sheets(sheet1).Cells.ClearContents sConnect providermicrosoft.jet.oledb.4.0;extended propertiesexcel 8.0; _ Data Source ThisWorkbook.Path \ ThisWorkbook.Name sql1 select 物料代码,物料描述,属性,单位 from [物料代码表$] where 属性 采购 ThisWorkbook.Sheets(sheet1).Cells(2, 1).CopyFromRecordset conn.Execute(sql1) End Sub这段代码用了Public conn As ADODB.Connection声明公共连接变量写起来更规范也方便其他子过程复用同一个连接。sConnect$里$是 VBA 的简写声明等价于Dim sConnect As String——这是老 VB 程序员常用的精简写法看到别懵。2.2 HDRno 才是这套实例的精髓很多初学者在这里栽跟头。默认情况下Excel 的 OLEDB 驱动把第一行当表头所以查[物料代码表$]时你写select 物料代码 from [物料代码表$]能查到数据但一旦第一行不是表头或者你根本不需要表头列名就变成了F1、F2、F3这种自动编号。实例里订单生成系统的写法就非常典型sql select f6,f2,f3,f4,f5,f7,f13,f24-f25 from [sheet1$] where f24-f25f17 and (f13C3 or f13 is null)这行 SQL 干了几件事取第 6、2、3、4、5、7、13 列再算一个f24-f25的差值列过滤条件是“第 24 列减第 25 列小于第 17 列的值同时第 13 列要么不是 C3 要么是空值”。注意这里的is null是 SQL 标准写法用来判断空值不能用 null这是很多人容易写错的地方。如果 HDR 设为 yes你得用表头文字去引用列设为 no就得用 f1、f2 这种位置引用。这套实例大量使用了 hdrno配合固定列号取数因为很多业务表的表头是合并单元格、多行表头直接用列名写 SQL 反而麻烦。2.3 Jet 与 ACE 驱动的选择实例里有段代码用的是Microsoft.ACE.OLEDB.12.0这个驱动是 Office 2007 以后配套的能读 .xls 也能读 .xlsxConn.Open providerMicrosoft.ACE.OLEDB.12.0;Extended PropertiesExcel12.0;hdrno;data source ThisWorkbook.Path \ mvvar(i)注意Excel12.0对应的是 Excel 2007 及以上版本的文件格式。如果文件是 .xls 老格式你用Excel12.0也可能能读但不保证万无一失反过来用 Jet 4.0 去读 .xlsx 基本读不了。我一般建议处理旧格式 .xls 用 Jet处理新格式 .xlsx 用 ACE如果机器是 64 位 OfficeJet 可能连注册都没有直接上 ACE 更稳。3. 四个高频取数动作一列、一行、一个单元格、计算值这套实例里最有价值的部分不是它写了多少复杂的 join而是把日常最常用的取数动作单独拆了出来。这些代码放一起看能让你彻底明白CopyFromRecordset的脾气。3.1 取一整列数据Sub onecolumn() Dim Sql$ Set Conn CreateObject(Adodb.Connection) Conn.Open providermicrosoft.jet.oledb.4.0;extended propertiesexcel 8.0;hdrno;datasource ThisWorkbook.Path \1.xls Sql select f1 from [sheet1$] Cells.Clear [a1].CopyFromRecordset Conn.Execute(Sql) Conn.Close Set Conn Nothing End Sub这段代码把 1.xls 中 sheet1 的 A 列即 F1 对应的第一列全部取出放到当前工作表的 A 列。Cells.Clear是清空整张表之后再从 A1 开始填充。这里有个细节CopyFromRecordset会从目标单元格开始自动向下扩展你不需要知道结果有多少行它会把整个记录集全部铺进去。对应的多工作簿版本更实用Dim Sql$, Sht1 As Worksheet, Sht As Worksheet Set Sht1 Sheets(汇总) For Each Sht In Sheets If Sht.Name 汇总 Then Sql select 编码 from [ Sht.Name $] n [b65536].End(xlUp).Row 1 Sht1.Cells(n, 2).CopyFromRecordset Cnn.Execute(Sql) End If Next Sht这段代码遍历当前工作簿所有 sheet只要名字不是“汇总”就把它的“编码”列追加到汇总表的 B 列。n [b65536].End(xlUp).Row 1是老代码里常见的“找最后一行”写法——从 B65536 往上找第一个非空单元格再往下挪一行。如果你用 Excel 2007 以上版本可以把 65536 换成 1048576道理一样。3.2 取一行数据Sub onerow() Dim Sql$ Set Conn CreateObject(Adodb.Connection) Conn.Open providermicrosoft.jet.oledb.4.0;extended propertiesexcel 8.0;hdrno;datasource ThisWorkbook.Path \1.xls Sql select * from [sheet1$a1:iv1] Cells.Clear [a1].CopyFromRecordset Conn.Execute(Sql) Conn.Close Set Conn Nothing End Sub这里的关键是[sheet1$a1:iv1]——用范围来限定工作表的数据区域$a1:iv1表示取第一行的 A 列到 IV 列。这是把整行当成一个记录集来查询返回的是一行多列的数据。类似的思路扩展到多行多列也没问题只要把范围写对就行。3.3 取一个单元格的值并赋给变量Private Sub CommandButton1_Click() Dim Sql$, Conn, rs, str1 Set Conn CreateObject(Adodb.Connection) Set rs CreateObject(adodb.recordset) Conn.Open providermicrosoft.jet.oledb.4.0;extended propertiesexcel 8.0;hdrno;datasource ThisWorkbook.Path \数据.xlt Sql select * from [sheet1$c6:c6] rs.Open (Sql), Conn, 1, 1 aa rs.getrows str1 aa(0, 0) MsgBox str1 Conn.Close Set Conn Nothing End Sub这段代码的目标是把另一个文件里 C6 单元格的值取出来塞进变量然后弹窗显示。rs.getrows方法返回的是一个二维数组第一维是列第二维是行所以aa(0, 0)就是第一列第一行的值也就是 C6 的内容。这个思路比直接用 VBA 打开工作簿再读单元格要快因为全程没有打开目标文件只是数据源的逻辑读取。如果只是想把单元格值写回 Excel 而不是弹窗更常见的做法是用查询结果直接赋给单元格Sub onecell() Dim Sql$ Set Conn CreateObject(Adodb.Connection) Conn.Open providermicrosoft.jet.oledb.4.0;extended propertiesexcel 8.0;hdrno;datasource ThisWorkbook.Path \1.xls Sql select * from [sheet1$k1:k1] Cells.Clear [a1].CopyFromRecordset Conn.Execute(Sql) Conn.Close Set Conn Nothing End Sub3.4 用 SQL 直接做计算实例里还有两个特别适合替代公式的场景——单元格加法和列求和Sub A1_Plus_b1() Dim Sql$ Set Conn CreateObject(Adodb.Connection) Conn.Open providermicrosoft.jet.oledb.4.0;extended propertiesexcel 8.0;hdrno;datasource ThisWorkbook.Path \1.xls Sql select f1f2 from [sheet1$a1:b1] Cells.Clear [a1].CopyFromRecordset Conn.Execute(Sql) Conn.Close Set Conn Nothing End Sub注意select f1f2 from [sheet1$a1:b1]的范围是a1:b1只涉及一行两列所以f1对应 A1f2对应 B1SQL 返回的计算结果就是 A1B1。这里 HDRno 的意义就体现出来了如果 HDRyes你必须写select 表头1表头2而不是f1f2而表头一旦是中文或者带空格写 SQL 就是一场灾难。对应的纵向求和写法Sub sumcolumn() Dim Sql$ Set Conn CreateObject(Adodb.Connection) Conn.Open providermicrosoft.jet.oledb.4.0;extended propertiesexcel 8.0;hdrno;datasource ThisWorkbook.Path \1.xls Sql select sum(f1) from [sheet1$a1:a2] Cells.Clear [a1].CopyFromRecordset Conn.Execute(Sql) Conn.Close Set Conn Nothing End Subsum(f1)返回的是区域内的合计值这里区域只有a1:a2两行算出来直接写进 A1。这套逻辑放在循环里就能逐列汇总比 worksheet function 的 Sum 在跨文件场景下快得多。4. 多表汇总与复杂查询实战进销存、月份合并与条件统计这部分是整套实例里含金量最高的区域。如果你手头有多个月份的明细表要合并成一个总表同时还要按产品代码汇总数量金额SQL 的优势就完全体现出来了。4.1 用 GROUP BY 按产品代码汇总实例里有一段进销存汇总的核心代码Sql select 产品代码,sum(进货数量),sum(进货金额) from [进货$] group by 产品代码 这段 SQL 的意思是从“进货”表里按“产品代码”分组分别汇总进货数量和进货金额。注意 group by 后面必须包含 select 中所有非聚合列否则 JET 引擎会报错。实例里特别提到如果没有 group by 直接 select 产品代码,sum(进货数量)就会提示“产品代码不能汇总”之类的错误——这不是 Excel 的锅是 SQL 语法规则选了普通列就必须 group by。如果要多带一个“进货单价”字段并且单价也要参与分组写法就成了Sql select 产品代码, ,sum(进货数量),进货单价,sum(进货金额) from [进货$] group by 产品代码, 进货单价第 2 列写了个 空字符串纯粹是为了占位。这里有个很多人忽略的细节进货单价参与了 group by意味着同一个产品代码如果有不同的进货单价会被拆成多行输出——这符合业务逻辑不同批次进货价不同就该分开展示。4.2 两张表按产品代码关联查询实例里两表查询的代码是Sql select B.产品代码, ,sum(B.进货数量),B.进货单价,sum(B.进货金额),sum(C.销售数量),C.销售单价,sum(C.销售金额) from [进货$] as B,[销售$] as C where B.产品代码C.产品代码 group by B.产品代码,B.进货单价,C.销售单价这里是用了 SQL 里的“表别名”写法——[进货$] as B把进货表简称为 B[销售$] as C把销售表简称为 C。对 Excel 来说表名带$符号和括号直接写全名很容易出错起别名之后 SQL 看起来清爽很多也便于后面 select 里引用哪张表的哪个列。三表查询是在两表基础上再加产品资料表把产品名称也拉进来Sql select A.产品代码,A.名称,sum(B.进货数量),B.进货单价,sum(B.进货金额),sum(C.销售数量),C.销售单价,sum(C.销售金额) from [产品资料$] as A,[进货$] as B,[销售$] as C where A.产品代码B.产品代码 and B.产品代码C.产品代码 group by A.产品代码,A.名称,B.进货单价,C.销售单价这套三表关联的思路本质是把 Excel 的三个 sheet 当成了关系数据库的三张表。写 join 的时候不需要考虑 sheet 在哪个位置、是否打开只需要盯着产品代码这一关键字段把表串起来。最后那段甚至算出毛利和库存差sum(C.销售数量)*(C.销售单价-B.进货单价),sum(B.进货数量)-sum(C.销售数量)这两列分别是“毛利”和“账面库存结余”直接在 SQL 层算完Excel 拿到的就是最终结果省掉了公式下拉。4.3 用 UNION ALL 纵向合并多个月份表月份表结构相同需要首尾相接纵向合并时实例里的做法是sq1 select 编号,日期,发票号,客户,案类,案号,律师,业务量,合作人,项目,金额,收入,应收,备注 from [1月$] sq2 select 编号,日期,发票号,客户,案类,案号,律师,业务量,合作人,项目,金额,收入,应收,备注 from [2月$] sq3 select 编号,日期,发票号,客户,案类,案号,律师,业务量,合作人,项目,金额,收入,应收,备注 from [3月$] sq4 sq1 UNION ALL sq2 UNION ALL sq3注意用的是UNION ALL而不是UNION。UNION ALL不会去重适合合并明细数据UNION会去掉重复行如果你只要不重复的名单才用 UNION。这里 11 列字段的顺序必须完全一致否则合并后数据会错位。我见过不少人把字段顺序写乱结果 1 月的金额跑到 2 月的备注里去。合并之后还要按发票号排序并分组汇总实例里再包一层子查询sq5 select 编号,日期,发票号,客户,案类,案号,律师,业务量,合作人,项目,SUM(金额),sum(收入),sum(应收),备注 from ( sq4 ) GROUP BY 编号,日期,发票号,客户,案类,案号,律师,业务量,合作人,项目,备注 order by 发票号这里from (sq4)是 SQL 子查询的标准用法——先把三个月份的明细合并成一个临时数据源再对这个数据源做分组汇总。外层 group by 的字段顺序和 select 里非聚合字段的顺序要对应最后order by 发票号控制输出顺序。4.4 日期区间查询与客户条件过滤实例里有一段按日期区间查明细的代码用了标准的 SQL 日期写法Sql select 日期,客户名称,品名及规格,数量,单价,金额,备注 from [明细表$] where (日期 between # dd # and # ee # )日期条件用#号包裹这是 Access/JET 引擎的语法要求。dd和ee是事先从单元格取出的起止日期。注意这里因为是字符串拼接日期变量必须是 yyyy-mm-dd 格式才能正确比较如果你在单元格里写的是“2024/1/5”这种非标准格式拼进 SQL 可能被当作文本处理导致区间查不到数据。按客户名称精确过滤更简单Sql select 日期,客户名称,品名及规格,数量,单价,金额,备注 from [明细表$] where 客户名称 aa aa是从单元格取出的客户名拼进 SQL 时外套单引号——这是 SQL 的字符串边界符。实例里所有 where 条件的字符串值都用单引号包裹数字值不需要日期用#记住这个规律就不会写错。4.5 多条件区间统计的循环嵌套写法实例最后那段 tJ 统计代码把“按客户、按存货编码区间、按日期范围”三个维度组合起来在一个双重循环里逐格填数For i 4 To Myr aa Cells(i, 1) For j 2 To 22 bb Cells(3, j) cc Cells(3, j 1) If j 4 Or j 9 Or j 10 Or j 21 Or j 22 Then Sql select sum(价税合计) from [数据$] where 客户名称 aa and (开票日期 between # dd # and # ee #) and (存货编码 bb ) Else Sql select sum(价税合计) from [数据$] where 客户名称 aa and (开票日期 between # dd # and # ee #) and (存货编码 between bb and cc ) End If Set rs New ADODB.Recordset rs.Open Sql, cnn, adOpenKeyset, adLockOptimistic Sht1.Cells(i, j).CopyFromRecordset rs rs.Close Next j Next i这段代码的思路是外层循环遍历客户列表内层循环遍历第 3 行的条件标题比如存货编码区间每个单元格都执行一次 SQL 聚合查询。adOpenKeyset和adLockOptimistic是记录集打开方式前者允许滚动后者允许乐观锁定在这种只读统计场景下不是必须的但这样写兼容性最好。5. 避坑与常见问题排查连接串、Excel 版本与数据类型的那些坑这套实例里的代码大多来自 2008-2013 年间的论坛帖子。放今天的环境里跑有几个坑几乎人人都会踩一遍这里直接给出排查思路。5.1 连接 Provider 报错找不到 Microsoft.Jet.OLEDB.4.0现象运行代码时提示“未找到提供程序”或“无法启动应用程序”。原因Office 2013 及以上 64 位版本默认不再注册 JET 4.0 驱动另外 64 位 Office 也读不了 32 位驱动。解决改用 ACE 驱动并把连接串换成 Excel 12.0 或 Excel 16.0。列出可替换的连接串供参考场景连接串.xls 文件 32 位 OfficeProviderMicrosoft.Jet.OLEDB.4.0;Extended PropertiesExcel 8.0;.xlsx 文件 任一 OfficeProviderMicrosoft.ACE.OLEDB.12.0;Extended PropertiesExcel 12.0;.xlsx 文件 64 位 OfficeProviderMicrosoft.ACE.OLEDB.12.0;Extended PropertiesExcel 16.0;注意 Excel 16.0 的写法只改 Extended Properties 部分和 ACE.OLEDB.12.0 配合使用不要改 Provider 里的 12.0。5.2 HDRyes 中文列名查不到现象连接串写 hdryesSQL 里 select 中文列名报错或返回空。原因JET 引擎对中文列名的解析依赖文件编码和区域设置部分系统下中文列名被识别成乱码另外列名里带空格、点号、括号也会让 SQL 解析失败。解决最简单是用 hdrno 放弃列名直接用 f1、f2 按位置取列。如果必须用列名用方括号把字段名包起来比如select [物料代码] from [物料代码表$]并且确保文件不是 UTF-8 无 BOM 编码的 CSV。5.3 数字被当成文本求和结果为零现象SQL 里 sum(金额) 返回 0或者明明有数据但查出来是 null。原因目标区域里数字被存成了文本格式比如单元格左上角有绿色三角标记JET 引擎遇到文本型数字sum 直接跳过。解决在 SQL 里用Val()函数强制转换或者用CDbl()转换字段再求和Sql select sum(Val(金额)) from [进货$]如果整列是文本格式更彻底的方案是在 Excel 里先把该列分列转成数字再跑 SQL。5.4 查询结果写回时把原有数据覆盖不干净现象第二次查询的结果比第一次行数少但表格下方还残留着上次的旧数据。原因CopyFromRecordset只覆盖它写入的区域不会主动把旁边区域清空。比如第一次查了 100 行第二次只查 5 行第 6 行到第 100 行还是旧的。解决执行查询前先清空目标区域。实测代码里用了Cells.Clear清整表或者用Range(A1:N5000).ClearContents清指定区域。我习惯是先算出结果可能占用的最大行数然后Range(A1:N maxRow).ClearContents。5.5 .xls 与 .xlsx 混用导致 Extended Properties 失效现象连接串写的是 Excel 8.0但数据源是 .xlsx 文件打开时提示外部表不是预期格式。原因JET 4.0 驱动只认 8.0 格式ACE 12.0 才能处理 Excel 12.0 及以上格式。混搭后驱动直接拒读。解决让连接串和文件格式严格匹配。建议在代码里先判断文件扩展名再选 ProviderIf Right(pathStr, 4) xlsx Then connStr ProviderMicrosoft.ACE.OLEDB.12.0;Extended PropertiesExcel 12.0;HDRno;Data Source pathStr Else connStr ProviderMicrosoft.Jet.OLEDB.4.0;Extended PropertiesExcel 8.0;HDRno;Data Source pathStr End If这样不管用户丢给你什么格式的表格代码都能自适应。6. 进阶用法不打开工作簿批量取值与直接输出文本文件这套实例的最后一部分其实藏着两个特别高效的进阶姿势值得单独说清楚。第一个是遍历文件夹内所有同名格式的文件逐个取值汇总全程不打开目标文件第二个是把查询结果直接输出到纯文本文件跳过 Excel 这一步。这两招用好了日常的报表整理能省掉一半时间。6.1 循环读取文件夹内所有 .xls 文件的指定单元格实例里那段testit子过程配合FileList函数先把指定文件夹下的所有 .xls 文件枚举出来循环打开连接读取指定单元格mvvar FileList(myPath) For i LBound(mvvar) To UBound(mvvar) If mvvar(i) myName Then Conn.Open providerMicrosoft.ACE.OLEDB.12.0;Extended PropertiesExcel12.0;hdrno;data source ThisWorkbook.Path \ mvvar(i) Sql select * from [sheet1$h6:h6] Myr [a65536].End(xlUp).Row 1 If Myr 4 Then Myr 4 Cells(Myr, 3).CopyFromRecordset Conn.Execute(Sql) Cells(Myr, 1) Myr - 3 Cells(Myr, 2) Left(mvvar(i), Len(mvvar(i)) - 4) Conn.Close End If Next这段代码的关键是Myr [a65536].End(xlUp).Row 1每次循环都重新定位当前汇总表的最后一行保证后面的文件从新的一行开始写入不会互相覆盖。Left(mvvar(i), Len(mvvar(i)) - 4)是把“123.xls”里的“.xls”切掉只保留文件名来当来源标识。判断最后一行还有一个更稳的写法用Cells(Rows.Count, A).End(xlUp).Row替代[a65536]这样不管文件是旧版还是新版都能适用不用死记 65536 这个数字。6.2 用 SELECT INTO 把工作表导出为文本文件实例里 OutputTxt 子过程的核心 SQL 只有一句话strsql SELECT * INTO [ strTxtname ] IN strFolder Text; FROM [ strSheetName $ strRange ] cnn.Execute (strsql)这个写法的原理是 JET 引擎支持跨数据源导出——IN后面指定目标路径和驱动类型Text;表示文本文件驱动。执行后 Excel 工作表的指定区域就变成了 txt 文件分隔符默认是制表符 Tab。这在要把数据喂给其他系统的场景里很好用比Workbooks.OpenSaveAs快得多。注意目标文件名不能已经存在否则要提前Kill删除实例代码里已经处理了这个环节。6.3 交叉表之外把 SQL 当透视表用的习惯实例最后那段用group by 产品代码, 进货单价实现交叉统计的思路其实就是在用 SQL 模拟数据透视表。从那以后我每次接到“把这些表合成一个总表”“按 XX 分组求合计”这类需求都强迫自己先想能不能用一句 SQL 解决而不是直接堆公式。这个习惯帮我少写了无数个 VLOOKUP 和 SUMIFS。希望这篇文章里每一个坑的记录和每段代码的拆解都能让你在实际业务里少走一趟弯路。本文还有配套的精品资源点击获取