文档库

最新最全的文档下载
当前位置:文档库 > excelvbaadosql入门教程004:sql中的excel表

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

Excel VBA ADO SQL入门教程004:SQL中的Excel表

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

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

再比如,一张表里存在两个或更多个“表”……这句话什么意思呢?见下图。图中所示的表格中,既存在一份“教师表”,又存在一份“学生表”;如果我们只希望SQL引用计算A2:D8的教师表数据……

呃,请把刀放下,君子动手不动刀…………好伐,Excel中的SQL其实是支持将工作表的单元格区域作为“表”使用的。上图所示的问题,SQL可以写成:SELECT 姓名,学科FROM [数据表$A2:D8]查询结果如下:而第1种情况,我们知道数据开始于A2单元格,但不知道结束于F列的哪个单元格,SQL可以写成:SELECT 姓名,爱好FROM [学生表$A2:F]另外,如果我们需要SQL引用计算表格D:G整列的数据:SELECT * FROM [学生表$D:G]总结以上几种Excel工作表区域的表述方式,也就是,工作表名称美金符号$ 相对引用状态下的单元格地址,最后使用中括号包起来。就酱紫。本节小贴士:[学生表$A2:F],我们说该语句可以引用从A2至F列最后存在数据的单元格区域,但这是有一个限制条件的,即非自连接状态。所谓自连接是指SQL应用于链接自身的工作簿。自链接状态下,A2:F的表达方式最多是A2:F65536行;倘若此时需要的引用行超过65536行,请使用整表模式。

3.跨工作簿的表一个众所周知的问题是,Excel函数在处理跨工作薄数据时很是疲态,除了个别几个查找引用类函数(例如VLOOKUP),绝大部分函数都需要打开相关工作簿后才

可以使用计算。是的,VLOOKUP函数并不需要打开相关工作簿也可以跨工作薄使用,而且在VLOOKUP公式书写完成后,即便你把它所引用的工作簿给删了,也不妨碍它计算,这是因为它已经把相关数据缓存到了公式所在的工作簿中,不过VLOOKUP这种模式并不支持函数嵌套使用……打个响指,关于这一点,如果你感兴趣,我们改天单独聊一下。说回SQL……我觉得我今天精神老是不集中,码个字也老是跑题,且慢的像蜗牛,也许有人在想我,也许有人在骂我,不管了,我先去刷会微博,哦,对了,星光俺的微博是

@EXCELers,没事记得关注下…………说回SQL~~……我们之前分享的SQL语句都是处理当前工作簿的表格,如果我们所需要处理的数据位于其它工作簿时,SQL该怎么表述呢?例如,获取位于计算机D盘的“EH小学”文件夹下的“学生表.xlsx”工作簿中的“成绩表”的所有数据——一口气读完这话的,送你一脸坏笑暴击点赞。如果是OLE DB 法(该方法参考第1章)使用SQL,语句如下:SELECT * FROM [D:\EH小学\学生表.xlsx].[成绩表$]FROM后的指定表字符串有两个部分构成,第一个中括号内是指定工作簿的存放路径带后缀的完整工作簿名称,后一个中括号内是工作表名称,两个中括号之间使用英文句号(.)相连。如果是通过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 String, 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.Execute(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 As Object, rst As Object Dim Mypath As String, Str_cnn As String, Sql As String Dim i As Long Set cnn =

CreateObject('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 '创建到代码所在工作簿的链接Sql =

'SELECT * FROM [Excel 12.0;DATABASE=D:\EH小学\学生表.xlsm].[成绩表$]' 'Sql语句,查询成绩表的所有数据Set rst = cnn.Execute(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(代码详细注释及适用于03~16版本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个中括号内是工作表名。两个中括号之间使用英文句号相连。…………看起来似乎VBA ADO方法的SQL语句比OLE DB法更复杂?确实如此,不过前者的功能也更强大。比如,

它可以通过VBA对象的属性和方法及循环、判断语句等,有条件的筛选工作簿和工作表等……相比之下,OLE DB中的SQL语句就是纯手工原始模式了。当然,更重要的是,前者不但可以查数据,还可以增改删数据,后者却只限于查。…………