excelvbaadosql入门教程004：sql中的excel表

1、Excel VBA ADO SQL 入门教程 004： SQL 中的 Excel 表1. 上期我们聊了 SQL 常用查询语句中的字段查询， 其简 化版语法如下： SELECT 字段名 FROM 表名当时我们说， FROM 关键词指明了要获取字段信息的表的名称。 倘若数据 源是 Excel 表格， 则需要在表名后增加美元符号 $，并用中括 号包起来，例如Sheet1$事实上，上述例子是SQL InExcel 对工作表引用最简单的一种情况，也就是整表引用； 此外还有单元格区域引用、跨工作簿引用表等。所以我们今 天就再来聊一下 SQL 语句中的 Excel 表。2. 区域成表 Excel 工作表和数

2、据库的数据表有很多不同之处， 最显著的地方在于，数据库的数据表是由行列构成的，而 Excel 工作表则是由一个又一个单元格构成的，且这些单元 格拥有独特的地址表述方法（ A1 和 R1C1 ），它们还可以构 成数据相连的单元格区域，例如 A2:H8 。于是问题来了，如 果我们只需要使用 SQL 语言计算某张 Excel 工作表的部分区 域该怎么表述呢？这种问题是很常见的。比如，很多人的 Excel 标题行并不是处于表格的第一行，而是第 2 行如 下图：此时，我们希望 SQL 可以使用 A2:F 列的单元格区域 作为表，而不是整个 Excel 工作表，这样我们更容易使用字 段名处理数据对吧？（我

3、转头看看书柜，书柜不说 话）再比如，一张表里存在两个或更多个“表”这句话什么 意思呢？见下图。 图中所示的表格中， 既存在一份 “教师表”， 又存在一份“学生表”；如果我们只希望 SQL 引用计算 A2:D8 的教师表数据呃，请把刀放下，君子动手不动刀好伐， Excel中的 SQL 其实是支持将工作表的单元格区域作为 “表”使用的。 上图所示的问题，SQL可以写成：SELECT姓名，学科FROM 数据表 $A2:D8 查询结果如下：而第 1 种情况，我们知道数 据开始于 A2 单元格，但不知道结束于 F 列的哪个单元格， SQL 可以写成： SELECT 姓名 ,爱好 FROM 学生表 $A2

4、:F 另外，如果我们需要 SQL 引用计算表格 D:G 整列的数据： SELECT * FROM 学生表$D:G总结以上几种 Excel工作表 区域的表述方式，也就是，工作表名称 美金符号 $ 相对引 用状态下的单元格地址，最后使用中括号包起来。就酱紫。 本节小贴士： 学生表 $A2:F ，我们说该语句可以引用从 A2 至 F 列最后存在数据的单元格区域，但这是有一个限制条件 的，即非自连接状态。 所谓自连接是指 SQL 应用于链接自身 的工作簿。自链接状态下， A2:F 的表达方式最多是 A2:F65536 行；倘若此时需要的引用行超过 65536 行，请使用整表模式。3. 跨工作簿的表一个

5、众所周知的问题是，Excel 函数在处理跨工作薄数据时很是疲态，除了个别几个查找引用类函数（例 如 VLOOKUP ），绝大部分函数都需要打开相关工作簿后才可以使用计算。是的， VLOOKUP 函数并不需要打开相关工 作簿也可以跨工作薄使用， 而且在 VLOOKUP 公式书写完成 后，即便你把它所引用的工作簿给删了，也不妨碍它计算， 这是因为它已经把相关数据缓存到了公式所在的工作簿中， 不过VLOOKUP这种模式并不支持函数嵌套使用打个 响指，关于这一点，如果你感兴趣，我们改天单独聊一下。 说回 SQL 我觉得我今天精神老是不集中， 码个字也老是 跑题，且慢的像蜗牛，也许有人在想我，也许有人在

6、骂我， 不管了，我先去刷会微博，哦，对了，星光俺的微博是 EXCELers，没事记得关注下说回 SQL我们之前分享的 SQL 语句都是处理当前工作簿的表格， 如果我 们所需要处理的数据位于其它工作簿时， SQL 该怎么表述 呢？例如，获取位于计算机 D 盘的“ EH 小学”文件夹下的 “学生表 .xlsx ”工作簿中的“成绩表”的所有数据一口 气读完这话的，送你一脸坏笑 暴击点赞。如果是 OLE DB 法（该方法参考第 1章）使用SQL，语句如下：SELECT * FROM D:EH 小学 学生表 .xlsx. 成绩表 $FROM 后的指定 表字符串有两个部分构成，第一个中括号内是指定工作簿的

7、 存放路径 带后缀的完整工作簿名称，后一个中括号内是工 作表名称，两个中括号之间使用英文句号（.）相连。如果是通过 VBA ADO 使用 SQL 语句嘟嘟敲书柜前方预警： VBA 基础差的童鞋请自行跳过以下内容相比于 OLE DB法，VBA ADO的方法要灵活的多，它可以使用ADO直接创建并打开和指定工作簿的链接， 因此 SQL 语句便无需再指定工作簿完整名称等。代码参考如下： Sub ADO_Sql() 适用于除 2003 版以外的高版本 Excel Dim cnn As Object, rst As Object Dim Mypath As String, Str_cnn As Strin

8、g, Sql As String Dim i As Long Set cnn = CreateObject(adodb.connection) Mypath = D:EH 小学 学 生表.xlsx指定工作簿 Str_cnn = Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source= & Mypath cnn.Open Str_cnn 创建并打开到指定工作簿的链接Sql =SELECT * FROM 成绩表 $ Sql 语句，查询成绩表的所有数 据 Set rst = cnn.Execut

9、e(Sql) 执行 SQL Cells.ClearContents For i = 0 To rst.Fields.Count - 1 Cells(1, i 1) = rst.Fields(i).Name Next Range(a2).CopyFromRecordset rst cnn.Close Set cnn = NothingEnd Sub 但更多的情况是， ADO 创建的链接是一个 工作簿，需要获取的数据在另一个或多个工作簿，例如跨表 格数据查询统计。 此时通常使用的代码如下： Sub ADO_Sql2() 适用于除 2003 版以外的高版本 Excel Dim cnn AsSet c

10、nn =Object, rst As Object Dim Mypath As String, Str_cnn AsString, Sql As String Dim i As LongCreateObject(adodb.connection) Mypath = ThisWorkbook.FullName 代码所在工作簿的完整名称 Str_cnn = Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source= & Mypath cnn.Open Str_cnn 创建到代码所在工作簿的链接S

11、ql =SELECT * FROM Excel 12.0;DATABASE=D:EH 小学 学生 表.xlsm.成绩表$Sql语句，查询成绩表的所有数据Set rst = cnn.Execute(Sql) 执行 SQLCells.ClearContentsFor i = 0 To rst.Fields.Count - 1Cells(1, i 1) =rst.Fields(i).Name Next Range(a2).CopyFromRecordset rst cnn.CloseSet cnn= NothingEnd Sub (代码详细注释及适用于 0316 版本 Excel 的 VBA 代码模版请参考第 2 章，回复关键词 SQL 可以获取 过往系列文章)代码中的 SQL 语句是：SELECT * FROM Excel 12.0;DATABASE=D:EH 小学 学生 表.xlsx.成绩表$FROM指定表的字符串有两部分组成。第 一个中括号中， Excel 12.0 是目标工作簿的版本号，第 2 章 时我们讲过， Excel 12.0 适用于除了 2003 以外的所有 Excel 版本。 DATABASE 指定的是数据源工作簿的路径和名称。 第 2 个中括号内是工作表名