Excel-VBA+ADO+SQL.docx

Excel VBA+ADO+SQL入门教程001：认识SQL In Excel那就先说SQL吧。SQL是一种结构化查询语言（Structured Query Language），是一种声明式语言，敲黑板划重点【结构化和声明式】。SQL的核心是对表的引用，声明你想从数据源中获取什么样的结果，而不用告诉计算机如何才能够得到结果后面这句话似乎很难理解，举例来说，倘若我们需要获取上图所示表格（Sheet1）成绩大于等于80分的人员名单，如果用命令式程序语言，比如VBA，是这样的：Sub MyFind() Dim arr, brr, i&, k& arr = Sheet1.a1.CurrentRegion ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2) For i = 1 To UBound(arr) If arr(i, 2) = 80 Then k = k + 1 brr(k, 1) = arr(i, 1) brr(k, 2) = arr(i, 2) End If Next d:f.ClearContents d1.Resize(k, 2) = brrEnd Sub你需要通过VBA编程告诉计算机每一步怎么走，数据从哪里来，从哪里开始遍历，行列是多少，符合条件的数据装入哪里，怎么装等等而如果用声明式SQL语言呢？只要告诉计算机我要什么就可以了。SELECT 姓名,成绩 FROM Sheet1$ WHERE 成绩=80我要Sheet1表（FROM Sheet1$）成绩大于等于80（WHERE 成绩=80）姓名和成绩的数据（SELECT 姓名,成绩）。只要结果，不问过程。就酱紫的声明式霸道总裁范。4.为什么要学习SQL In Excel（Excel支持的SQL语言）呢？换言之，相比于Excel其它功能，例如函数、VBA、POWER PIVOT等，SQL有何优势？首先，必须严肃脸说明的是，对于普通Excel使用者而言，VBA、SQL以及以后提及的ADO并不是非学不可的，非学不可的是基础操作、函数、透视表、图表然而大数据时代，对于另外相当一部分表族而言，Excel用久了，慢慢的，会意识到一个大问题；曾经在你心中无比强大的Excel函数，原来只适合小数据的腾挪躲闪；当数据量稍大后，函数这货就像未嗑士力架的姚明不来劲的很哩。SQL In Excel则可以解决函数处理大数据效率低下的问题，嗯使用SQL语言，你甚至可以将Excel作为前台数据管理界面，数据库（例如ACCESS）作为后台数据储存仓库，进而储存、分析、管理远超Excel体积的数据量。打个响指，我们之前讲过，VBA处理数据的核心是数组+字典，倘若SQL和它比较起来有何优劣？作为一个正努力成为乐观主义者的人，星光还是先说优点吧。通过上面代码的栗子我们很直观的看到，SQL的书写要比VBA编程简洁的多，甚至比小巧灵的函数还要简洁；此外，SQL高效处理的数据量上限，也是远远大于VBA数组+字典的；字典装上50W的数据，一般电脑的计算效率就开始垂直下降了，而SQL 还是风轻云淡脸；最后，SQL+ADO+VBA可以通过Excel直接处理数据库（例如ACCESS）来源的数据。然后说劣势。SQL作为一种数据库结构化查询语言，对表的结构和数据的类型有着严格的要求，而严格来说Excel并非数据库，尽管它支持ADO和SQL（谁说装了数据就是数据库的？拉出去自弹小丁丁500下，好冷）。Excel对表的结构和数据的类型并没有严格的限定，例如合并单元格，多行表头，空记录，一列之内存在多种数据类型等等劣迹存在，因此，字典+数组处理EXCEL数据的灵活性要远远高于SQL，毕竟数组遍历在手，天下我有，什么合并单元格多行表头，统统都是浮云最后，SQL In Excel 和Power BI For Excel（以下简称Power BI）相比优势在哪里？从Excel的角度讲，SQL和Power BI最大的优势是，SQL支持VBA语言。通过ADO执行SQL语言，VBA可以获取、分析、管理多种来源的数据，甚至进而对获取的数据再搭配字典、数组以及各种Excel自带的功能作进一步自动化、智能化处理换句话说，VBA运行SQL语句后，可以再整合Excel所有的功能进一步处理数据，除了Power BI是的，Power BI不支持VBA，耸肩，无奈。从数据的角度讲，Power BI是一款数据分析的软件，包含了M和DAX查询语言，SQL则是一种数据管理的语言。查询和管理有何不同？简而言之，SQL不但可以查询数据，还可以操纵数据，例如增、改、删等等。而M和DAX语言对数据则只能查询，不能操纵。就像我们在Power BI入门教程中讲的，它只能改变自己，永远无法改变对方（指的是数据源，不是我们的爱情）另外，SQL是一门广被接受和支持的语言。Excel,ACCESS,R,Python,JAVA,C等等软件和语言，均是支持SQL的；而POWER BI显然没有这样的待遇。我们很久以说，作为一名数据分析员有三个必须掌握的技能，SQL获得数据，EXCEL分析数据，PPT展现数据。POWER BI出现后，有人说学了POEWR BI，就不用学SQL了。如果你能意识到两者之间的不同，显然就会明白这是低头说话不看前路哦。当然，如果你是一名数据分析员，POWER BI最好也是要学的。原因很简单，它很简单。5.说了这么多，那么，如何在Excel中使用SQL？一般有三种方法。一种是MS Query法，不常用，省略。一种是OLE DB法，具体过程是，单击Excel【数据】选项卡下的【现有链接】，在弹出的【现有链接】对话框中，单击【浏览更多】，选取目标文件后，依次【确定】，得到下面的【导入数据】对话框。这种方法通常搭配数据透视表（上图显示方式选择【数据透视表(P)】），也可以搭配Power Pivot（高级版本Excel勾选上图的【将此数据添加到数据模型】）。单击【属性】按钮后，得到【链接属性】对话框，再单击【定义(D)】选项卡，即可在【命令文本】编辑框中输入SQL语句，并【确定】执行。关于上图【连接字符串】中的关键字和关联值，我们会在以后的AOD部分详加说明，此处先过。最后一种是VBA+ADO法，也是我们后文中常使用的方法。相比于第2种方法，VBA+ADO法的优点首先是自动化，它可以使用VBA代码绑定ADO，设定链接字符串，执行SQL语句，进而一键获取分析数据。其次，VBA编程可以使用变量编辑SQL语句，这远比第2种方法手动输入SQL语句要灵活智能的多，另外，VBA+ADO法不但可以SELECT（查询）数据，还可以INSERT（增）DELETE（删）UPDATE（改）数据库的数据等。Excel VBA+ADO+SQL入门教程002：简单认识ADO ADO (ActiveX Data Objects，ActiveX数据对象）是微软提出的应用程序接口，用以实现访问关系或非关系数据库中的数据更多概念信息请自行咨询百度君，无赖脸。之所以要学习ADO，一个原因是ADO自身的一些属性和方法对于数据处理是极其有益的；而首要原因是，在EXCEL VBA中，一般只有通过ADO，才可以使用强大的SQL查询语言访问外部数据源，进而查、改、增、删外部数据源中的数据。后面这话延伸在具体编程操作上，就形成了四步走发展战略1.VBA引用ADO类库。2.ADO建立对数据源的链接。3.ADO执行SQL语言。4.VBA处理SQL查询结果。嗯，这就好比你先找个女（男）朋友，然后谈恋爱，最后结婚2.在VBA中引用ADO类库一般有两种方式。一种是前期绑定。所谓前期绑定，是指在VBE中手工勾选引用Microsoft ADO相关类库。在Excel中，按快捷键打开VBA编辑窗口，依次单击【工具】【引用】，打开【引用-VBAProject】对话框。在【可使用的引用】列表框中，勾选“Microsoft ActiveX Data Objects 2.8 Library”库，或“Microsoft ActiveX Data Objects 6.1 Library”库，单击【确定】按钮关闭对话框。另一种是使用代码后期绑定。Sub 后期绑定()Dim cnn As ObjectSet cnn = CreateObject(adodb.connection)End Sub两种方式的主要区别是，前期绑定后，在代码编辑过程中，VBE的“自动列出成员”功能，可以提供ADO的属性和方法，这便于代码快捷、准确的编写，但当他人的Excel工作簿并没有手工前期绑定ADO类库时，相关代码将无法运行；因此后期代码绑定ADO的通用性会更强些，它不需要手工绑定相关类库。星光俺老油老江湖的经验是，代码编写及调试时，使用前期绑定，代码完善后，再修改为后期绑定发布使用。3.不论我们使用SQL语言对数据源作何操作，都得首先使用ADO创建并打开一个由VBA到数据源的链接；这就好比得先修路，才能使用汽车运输货物。在VBA中，我们通常使用ADO的Connection.Open语句来显式建立一个到数据源的链接。Connection.Open语法如下：connection.Open ConnectionString, UserID, Password, OptionsConnectionString可选，字符串，包含连接信息。UserID可选，字符串，包含建立连接时所使用用户名。Password可选，字符串，包含建立连接时所使用密码。Options可选，决定该方法是在连接建立之后（异步）还是连接建立之前（同步）返回，默认是同步，adAsyncConnect是异步。语法看起来似乎很复杂？不必烦扰，现在，对我们而言，重点只是大体了解一下参数ConnectionString，也就是连接字符串。虽然不同的数据库或文件有不同的连接字符串，但常用的数据库或文件的连接字符串均是固定的。举个例子，如果将代码所在的Excel（2016版）作为一个外部数据源建立链接，代码如下：Sub Mycnn() Dim cnn As Object 定义变量 Set cnn = CreateObject(adodb.connection) 后期绑定ADO cnn.OpenProvider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;HDR=yes;IMEX=0;Data Source= & ThisWorkbook.FullName 建立链接 cnn.Close 关闭链接 Set cnn = Nothing 释放内存End Sub说一下上面代码连接字符串中各关键字（字体加粗部分）的意思。Provider是Connection 对象提供者名称的字符串值，03版Excel是“Microsoft.jet.OLEDB.4.0”，其它版本可以使用“Microsoft.ACE.OLEDB.12.0”；Extended Properties是Excel版本号及其它相关信息，03版本是Excel 8.0，其它版本可以使用Excel 12.0。其中HDR项是引用工作表是否有标题行，默认值HDR=Yes，意思是引用表的第一行是标题行，标题只能一行，不能多行，亦不能存在合并单元格。HDR=no，意思是引用表不存在标题行，也就是说第一行开始就是数据记录了；此时，相关字段名在SQL语句中可以使用f加序列号表示，第1列字段名是f1，第2列字段名是f2，其余以此类推，f是英文field(字段)的缩写。IMEX项是汇入模式，默认为0（只读模式），1是只写，2是可读写。当参数设置为1时，除了只写，还有默认全部记录数据类型为文本的用途，关于这一点及其限制前提我们以后再谈。Data Source是数据来源工作薄的完整路径。VBA代码Application.Version可以获取计算机的Excel版本号，因此以下代码兼顾了03及各高级版本Excel的情况：Sub Mycnn2() Dim cnn As Object Dim Mypath As String Dim Str_cnn As String Set cnn = CreateObject(adodb.connection) Mypath = ThisWorkbook.FullName 数据源工作簿的完整路径 If Application.Version 12 Then 判断Excel版本号，以使用不同的连接字符串 Str_cnn = Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source= & Mypath Else Str_cnn = Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source= & Mypath End If cnn.Open Str_cnn cnn.Close Set cnn = NothingEnd Sub最后，需要提醒大家的是，链接是一种昂贵的资源（官方语），因此在代码运行完毕后，请养成关闭链接（cnn.Close）并释放内存（Set cnn = Nothing）的好习惯。本节小贴士：3.1，连接字符串中各关键字的对应值可能和大小写有关，这是因为不同数据库的要求可能不一样，但通常来说，关键字和大小写无关，例如Provider，可以写成provider或者PROVIDER。不过，虽然关键字和大小写无关，但和拼写正确与否当然是有关的！（想啥呢哥们？）当手打的连接字符串代码运行出错时，建议先复制正确的运行，再仔细核对个人错漏之处。3.2，连接字符串中各关键字之间使用英文分号（;）间隔，例如（关键字1=值1;关键字2=值2;关键字3=值3），另外，任何包含分号、单引号或双引号的值必须用双引号引起来，由于在VBA中连接字符串的外层已经存在了一个双引号，因此通常使用英文单引号进行转义，例如上例中的Extended Properties=Excel 12.0;HDR=yes;IMEX=2，抄写时，千万别漏了英文单引号哦。3.3，星光俺掐指一算，算出相当一部分童鞋英语水平堪忧，想来拼写这段英文连接字符串错漏百出是很有可能的，因此特呈上锦囊一份，参见下图。别问我这图是哪来的，如果不几道，佛山无银脚，出门右拐重看第一章吧如果这锦囊您也不想用其实收藏本帖，用到时打开帖子复制粘贴相关代码就可以了嘿嘿，木错，这才是最常用的一招。4.聊完了如何绑定ADO以及建立与数据源的链接最后说下如何使用ADO执行SQL语句。别走开，喝杯水先我，咕咚咕咚我们可以使用ADO的Connection对象或Recordset、Commannd执行SQL语句；详细内容我们放到ADO部分再讲；这里大家只需要先了解Connection对象的Execute方法就可以了。这是一个最常用的VBA+ADO+SQL套路化查询代码，通常，我们只需要修改SQL语言以及放置查询结果的单元格位置。Sub DoSql_Execute1() 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) 以上是第一步，后期绑定ADO Mypath = ThisWorkbook.FullName If Application.Version =80 Sql语句，查询Sheet1表成绩大于80姓名和成绩的记录 Set rst = cnn.Execute(Sql) cnn.Execute()执行SQL语句，始终得到一个新的记录集rst 以上是第三步，编写并使用SQL语句 d:e.ClearContents 清空d:e区域的值 For i = 0 To rst.Fields.Count - 1 利用fields属性获取所有字段名，fields包含了当前记录有关的所有字段,fields.count得到字段的数量 由于Fields.Count下标为0，又从0开始遍历，因此总数-1 Cells(1, i + 4) = rst.Fields(i).Name Next Range(d2).CopyFromRecordset rst 使用单元格对象的CopyFromRecordset方法将rst内容复制到D2单元格为左上角的单元格区域以上是第四步，将SQL查询结果和字段名写入表格指定区域 cnn.Close 关闭链接 Set cnn = Nothing 释放内存End SubExcel VBA+ADO+SQL入门教程003：字段的查询还是需要对一些名词做出解释；这些名词是SQL语句释义中经常使用到的，所以请先有一个大概的了解。1.1数据库数据库是按照数据结构来组织、储存和管理数据的仓库，例如最基础的ACCESS软件等。通常情况下，我们将一个Excel工作簿视为一个数据库。是的，如您所知，Excel并不是数据库，但这并不妨碍我们把它看成一个数据库，进而使用SQL语言的某些功能处理数据。这就好比尽管男人不是女人，但一样可以织毛衣、做月嫂不过，得承认，生孩子之类的就确实太富有挑战性了。1.2数据表数据表，或称之为表，是数据库最重要的组成部分之一，一个数据库由一个或一组数据表组成。如果我们把这句话的“数据库”，替换成“Excel工作簿”，就变成这样：表是Excel工作簿最重要的组成部分之一，一个Excel工作簿由一个或一组表组成，你看，同样也是成立的因此，可以先将Excel工作表视为数据库中的“表”，至于异同，以后再聊。1.3记录和字段如上图所示，和Excel表格一样，数据库的表由行和列组成，只是不存在行号和列标，相关值自然就不能使用A6、F4之类的Excel单元格地址来表述。表的每一行描述实体的一个实例，称之为记录；每一列描述实体的一个特征或属性，称之为字段，列的标题称之为字段名或列标题，例如上图中的“姓名”。主键和外键以后用到再说。没了。2.SQL的数据查询和操纵语句包括SELECT、UPDATE、INSERT、DELETE等，也就是所谓的查改增删，其中最重要、使用最频繁的是SELECT查询语句。查询按照复杂性划分，可以从一个简单的语句返回所有表中所有的记录，到用一个语句链接多个表并定义多种搜索条件现在，就让我们从最简单的部分走起吧走你，少年。SELECT语法如下（简化版）：SELECT 字段名 FROM 表名SELECT关键字指明了要查询的字段名称，FROM关键字指明了要获取字段信息的表的名称。倘若数据源是Excel表格，需要在表名后增加美元符号$，并用中括号包起来，例如Sheet1$。另外需要说明的是，SQL语言对关键字大小写不敏感。SELECT、select、Select都可以使用，作用等同；基于书写规范化的原因，可能有些老师会要求关键字统一大写；嗯，我不是老师，我还小；我的意见是新手期怎么喜欢怎么顺眼怎么来你好就好，宠溺脸。3.分享一下SQL In Excel 常用字段查询语句以及常见问题的解决方法。如下图所示，是一份Excel表格，表名为学生表，内容是一些看见星光之类小学生的信息数据。3.1，单个字段查询假设我们需要查询上图所示表格的姓名列数据。语句如下：SELECT 姓名 FROM 学生表$结果如下：3.2，多个字段查询假设我们需要查询字段为姓名/性别/爱好的记录，语句如下：SELECT 姓名,性别,爱好 FROM 学生表$不同字段名之间使用英文逗号间隔，但最后一个字段名不用。另外需要格外注意的是，英文逗号千万别写成中文逗号，当然，初学阶段，别说千万，亿万万也没办法阻止咱们错输中文逗号的情况发生反复切换输入法的中英文状态真的是很烦的一匹。上述语句查询结果如下：3.3，查询所有字段的快捷方式如果我们需要提取所有字段的数据呢？一种方法是将所有字段名写入SELECT语句中：SELECT ID,姓名,性别,年龄,爱好,得分 FROM 学生表$另外一种方法是，使用星号（*）选取所有列：SELECT * FROM 学生表$两者的区别在于，前者只选取指定字段的数据，后者包括了指定表的所有数据。对于Excel表格而言，如果字段名确定，出于安全考虑，通常使用前者更靠谱点儿。3.4，别名的使用如果我们需要更改查询结果中的字段名称，例如我不喜欢字段名“爱好”，我想把它改成“特长”，可以使用关键字as，as是英文alias 的缩写，也就是“别名”的意思。语句如下：SELECT 姓名,爱好 AS 特长 FROM 学生表$3.5，特殊字段名的处理当字段名存在某些特殊字符，例如空格，或者当字段名存在SQL语言的保留字，例如AS，该字段名需要使用中括号括起来，避免因语法识别混乱，系统产生错误信息的提示。举个例子，如上图所示，Excel表名为调查表，该表字段名中有的存在空格（姓 名），有的使用了SQL的保留字as，如果需要查询该表格姓名和地址的信息，SQL语句如下：select 姓 名 , as from 调查表$查询结果如下：3.6，无字段名情况的处理之前讲过，Excel并不是数据库，对数据结构并没有强制性要求，因为这样的缘故，在使用SQL处理数据时，难免会碰上各种有趣的现象。例如，数据表纯数据没有标题行，再或者，标题行存在合并单元格等，以至于字段名无法使用，此时，倘若需要获取字段数据，对字段名通常使用f加序列号表示，引用表的第1列字段名就是f1，第2列字段名是f2，其余依次类推。如上图所示，该表格没有标题栏，如果我们需要查询姓名和性别，代码如下：select f2 as 姓名,f3 as 性别 from 学生表$查询结果如下：砸桌子：还记得大明湖畔使用VBA+ADO执行SQL语句时，如果引用表不存在标题栏，需要修改链接字符串的哪个参数的项吗？出门右拐请重看第2章吧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（代码详细注释及适用于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个中括号内是工作表名。两个中括号之间使用英文句号相连。看起来似乎VBA+ADO方法的SQL语句比OLE DB法更复杂？确实如此，不过前者的功能也更强大。比如，它可以通过VBA对象的属性和方法及循环、判断语句等，有条件的筛选工作簿和工作表等相比之下，OLE DB中的SQL语句就是纯手工原始模式了。当然，更重要的是，前者不但可以查数据，还可以增改删数据，后者却只限于查。Excel VBA+ADO+SQL入门教程005：SQL之字段去重1.今天周末，明天是苦逼的周一所以我们今就聊点简单的，就一个单词DISTINCT。2.在数据分析处理过程中，我们经常需要去除查询结果中的重复值，保留截然不同的唯一值。对于SQL，这类问题，我们通常使用DISTINCT关键字处理。其语法如下：SELECT DISTINCT 字段名 FROM 表名举个简单栗子。如上图所示，Excel工作表的表名为“学生表”，A列是姓名，B列是学科，其中A列的姓名存在重复，比如“看见星光”，现在需要使用SQL语句提取不重复的学生名单。代码如下：SELECT DISTINCT 姓名 FROM 学生表$查询结果如下：从查询结果可以看出，重复的人名已被剔除；但数据排列顺序也和数据源不同了。事实上，系统的操作过程是先对查询结果排序，然后从中剔除重复值。在Excel中，当查询结果是非中文时，比如数字和字母，DISTINCT处理后的结果明显为升序排列，当查询结果是中文呢？其实中文也是按升序排列的只是排序规则不是我们平常所熟知的拼音或笔划顺序。如果需要去重的数据是多列，在DISTINCT关键字后指定多个字段名，彼此间使用英文逗号间隔即可。依然使用上述例子，查询去除姓名和学科同时重复的值，语句如下：SELECT DISTINCT姓名,学科FROM 学生表$查询结果：从查询结果可以看出，只有姓名和学科同时都重复的(看见星光 文言文研究)才被删除了。3.今天的分享系不系很简单呢？但需要说明的是DISTINCT可以处理的字符串最大长度为255，当字符串长度超过255时，会造成计算错误，同时查询结果也只返回255长度的记录。Excel VBA+ADO+SQL入门教程006：排序的应用本章主要内容：1，常规排序2，自定义排序3，排序的扩展应用：提取前n大或后n小的记录1.排序是我们使用Excel处理数据经常面对的问题，Excel甚至专门对此内置了【排序】功能。今天，我们就来聊一下SQL如何对查询结果进行排序操作，也就是ORDER BY 语句，其语法如下：SELECT 字段名 FROM 表名 ORDEY BY 字段名该语句默认对记录进行升序排序，如果需要降序排序，可以使用关键字DESC：SELECT 字段名 FROM 表名 ORDEY BY 字段名 DESC照例举个小例子。如下图所示，工作表名称为“销售表”，A列是月份，B列是产品，C列是生产量，D列是销售量，数据纯属虚拟，如有雷同实属雷人。如果我们需要查询该表月份和销售量两个字段的数据，并将销售量作升序排列，代码如下：SELECT 月份,销售量 FROM 销售表$ ORDER BY 销售量如果我们需要查询月份、生产量、销售量三个字段的数据，其中生产量优先排序，且降序排列，销售量升序排列，代码如下：SELECT 月份,生产量,销售量 FROM 销售表$ ORDER BY 生产量 DESC,销售量 ASC也就是说，ORDER BY语句中，优先排序的字段放在前面，不同字段可以指定不同的排序规则，如果没有指定排序规则，则默认为升序(ASC)排列。代码结果如下：2.依然使用上一节的例子，倘若我们需要对查询结果按月份进行降序排列，可能有些朋友代码会写成如下这般：SELECT 月份,销售量 FROM 销售表$ ORDER BY 月份 DESC但发现结果和我们想的并不一样。月份的排列看起来完全是乱态的，既不是升序也不是降序。上一章我们提过，SQL In Excel对简体中文排序的规则不走寻常路，既不按拼音字母排序，也不按笔画排序，而是可能和其它数据库一样，采用的Chinese_PRC针对大陆简体字UNICODE的排序规则，但和其它数据库所不同的是，我们没有修改该规则参数的权限，以达到拼音或笔划排序的目的不过，事实上，即便是使用Excel自带的排序功能（默认拼音字母排序），排序结果也并非是五四三二一月。此时我们需要自定义排序规则，也就是使用SQL中的iif或者instr函数。IF函数类似于工作表的IF函数，语法如下:=IIF(条件表达式,真值结果,假值结果)使用IIF自定义排序规则的SQL语句如下：SELECT 月份,销售量FROM 销售表$ ORDER BY IIF(月份=五月,1,IIF(月份=四月,2,IIF(月份=三月,3,IIF(月份=二月,4,IIF(月份=一月,5)代码长的吓人？但意思其实很简单。如果月份等于五月，就返回1，否则如果月份等于四月，就返回2，再否则如果月份等于三月以此类推最后ORDER BY语句按IIF返回的结果进行升序排序看了这个代码，是不是瞬间找回当年嵌套N层IF函数的青葱小岁月再说下INSTR函数。INSTR函数有些类似于工作表函数FIND，查找一个字符串在另一个字符串中的位置，和FIND不同的是，当找不到相关值时，结果返回0，而非错误值。INSTR（str, substr）。返回substr在str中的位置，若不存在，则返回0。使用INSTR函数自定义排序规则的SQL语句如下：SELECT 月份,销售量 FROM 销售表$ ORDER BY INSTR(五月,四月,三月,二月,一月,月份)查询结果如下：INSTR函数的处理语句比起IIF函数来明显要简洁清爽的多。因此我们通常使用该函数处理自定义排序的问题。3.提一个问题。如下。如果使用SQL语句查询上述示例中，销售量前三名的月份和销售量，该怎么表述呢？不要想的太复杂，其实很简单。在原本对销量进行降序排序的结果上，提取前三行的记录就OK。如何提取前3行记录呢？可以使用关键词TOP。TOP n 指定检索结果集中的第n行前的记录。完整SQL代码如下：SELECT TOP 3 月份,销售量 FROM 销售表$ ORDER BY 销售量 DESC查询结果如下：那么如果查询销售量后二名的月份和销量呢？4.我们今天分享了一个语句（ORDER BY）；一个关键字（TOP）；两个函数（IIF、INSTR）耸肩，摊手，你看相比于函数，SQL语言在处理去重复、排序、自定义排序、提取前n名数据的问题上，是不是简单太多了？不要困在Excel函数那一个小天地里，它的性能决定了它的功能很有限，更不要迷恋数组公式，除了预防老年痴呆它别无用处只学一点VBA（循环），或学一点SQL（SELECT子句），处理实际问题的能力也强过n多所谓的函数高手。Excel VBA+ADO+SQL入门教程007：条件查询（上）主要内容：单条件查询；多条件查询；模糊匹配查询1.Where？Where！我们上期聊了SQL排序，这期来聊筛选，或者说条件查询在第一期的时候，我们说SQL是声明式语言，当时还举了个小例子，不知道您是否还有印象：那个栗子中的SQL代码我们使用了where子句。它定义了筛选条件，成绩=80，从而对FROM子句返回的结果集进行筛选，将不符合条件的剔除，只保留符合条件的记录。事实上，where子句的筛选条件可以是简单的单条件（如上述示例），也可以是复杂的多条件，可以精确匹配筛选，也可以搭配通配符等进行模糊匹配筛选。SELECT 字段名 FROM 表名 WHERE 筛选条件这是它的语法。下面我们就通过几个栗子来了解下where子句的常用套路。2.一个栗子如下图所示，是一份名为学生表的Excel工作表，A列是班级，B列是姓名，C-E列分别是性别、年龄、爱好。3.单条件查询倘若需要查询上述示例中班级为“插班生”的学生名单，SQL代码如下：SELECT 班级,姓名 FROM 学生表$ WHERE 班级=插班生查询结果如下：班级=插班生就是where子句的筛选条件，它使用了等号来判断班级字段值和条件值是否相等。需要说明的是，和Excel一样，当条件值是文本时，应该使用引号包起来，数值则不用，例如：SELECT 班级,姓名 FROM 学生表$ WHERE 年龄=14昨个公众号后台有朋友问了个和下面的问题类似的问题：为什么班级=插班生使用的是单引号，而不是双引号？这个问题我们之前有解释过，这里再说一下。在OLE DB法使用的SQL语句里，单双引号都可以，只是在VBA中，通常SQL语句本身就是作为字符串存在的，外围已经存在了一对双引号，因此SQL语句内一般就使用单引号。4.多条件查询先说一下“与”关系（也就是并且关系）的多条件筛选。和Excel一样，在SQL中，这类问题通常使用AND运算符。比如，我们需要查找班级为插班生、同时年龄大于等于12岁的学员名单，SQL代码如下：SELECT 班级,姓名,年龄 FROM 学生表$ WHERE 班级=插班生 AND 年龄=12如语句所示，不同条件之间使用AND运算符相连，只有当每一个条件都成立时，才符合WHERE子句的筛选条件。查询结果如下：再说一下“或”关系的多条件筛选，