 1. 诸君好。 上期我们认识了SQL：Excel VBA ADO SQL入门教程001：认识SQL In Excel 这期我们聊下ADO。 ADO是什么？为什么要学ADO？ ADO (ActiveX Data Objects，ActiveX数据对象）是微软提出的应用程序接口，用以实现访问关系或非关系数据库中的数据……更多概念信息请自行咨询百度君，无赖脸。 之所以要学习ADO，一个原因是ADO自身的一些属性和方法对于数据处理是极其有益的；而首要原因是，在EXCEL VBA中，一般只有通过ADO，才可以使用强大的SQL查询语言访问外部数据源，进而查、改、增、删外部数据源中的数据。 后面这话延伸在具体编程操作上，就形成了三步走发展战略（鼓掌）…… 1.引用ADO类库。 2.ADO建立对数据源的链接。 3.ADO执行SQL语言。 嗯，这就好比你先找个女（男）朋友，然后谈恋爱，最后结婚……（话筒：啊~有钱没钱租个女友回家过年~ 2. 在VBA中引用ADO类库一般有两种方式。 一种是前期绑定。 所谓前期绑定，是指在VBE中手工勾选引用Microsoft ADO相关类库。 在Excel中，按<Alt F11>快捷键打开VBA编辑窗口，依次单击【工具】→【引用】，打开【引用-VBAProject】对话框。在【可使用的引用】列表框中，勾选“Microsoft ActiveX Data Objects 2.8 Library”库，或“Microsoft ActiveX Data Objects 6.1 Library”库，单击【确定】按钮关闭对话框。 一种是使用代码后期绑定。 Sub 后期绑定() Dim cnn As Object Set 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, Options ConnectionString可选，字符串，包含连接信息。 UserID可选，字符串，包含建立连接时所使用用户名。 Password可选，字符串，包含建立连接时所使用密码。 Options可选，决定该方法是在连接建立之后（异步）还是连接建立之前（同步）返回，默认是同步，adAsyncConnect是异步。 ……语法看起来似乎很复杂？不必烦扰，现在，对我们而言，重点只是参数ConnectionString，也就是连接字符串。虽然不同的数据库或文件有不同的连接字符串，但常用的数据库或文件的连接字符串均是固定的。 举个例子，如果将代码所在的Excel（2016版）作为一个外部数据源建立链接，代码如下： Sub Mycnn() Dim cnn As Object '定义变量 Set cnn = CreateObject('adodb.connection') '后期绑定ADO cnn.Open 'Provider=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，其余以此类推。 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 = Nothing End 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的Execute语句或Recordset对象执行SQL语句，有以下几种方法。 Execute方法（一）： 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 < 12 Then 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 '以上是第二步，建立链接 ' Sql = 'SELECT 姓名,成绩 FROM [Sheet1$] WHERE 成绩>=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 Sub 事实上，也可以不使用rst对象，只使用Execute语句，直接将SQL查询的结果放入指定单元格区域。该方法的优点是代码简洁，缺点是只获得记录，没有字段名，或者说没有标题栏，代码如下。 Execute方法（二）： Sub DoSql_Execute2() 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 < 12 Then 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 '以上是第二步，建立链接 ' Sql = 'SELECT 姓名,成绩 FROM [Sheet1$] WHERE 成绩>=80' 'Sql语句，查询Sheet1表成绩大于80……姓名和成绩的记录 [d:e].ClearContents '清空[d:e]区域内容 Range('d2').CopyFromRecordset cnn.Execute(Sql) 'Execute语句先执行SQL语句 '使用单元格对象的CopyFromRecordset方法将SQL查询到的内容复制到D2单元格为左上角的单元格区域 '此法代码简洁，但未能获取标题栏信息 '以上是第三步，执行SQL语句并将数据读入表格指定区域 ' cnn.Close '关闭链接 Set cnn = Nothing '释放内存 End Sub 除了使用Execute语句，也可以使用Recordset执行SQL。 Recordset方法： Sub DoSql_Recordset() 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') Set rst = CreateObject('adodb.Recordset') '以上是第一步，后期绑定ADO，并创建rst记录集 ' Mypath = ThisWorkbook.FullName If Application.Version < 12 Then 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 '以上是第二步，建立链接 ' Sql = 'SELECT 姓名,成绩 FROM [Sheet1$] WHERE 成绩>=80' 'Sql语句，查询Sheet1表成绩大于80……姓名和成绩的记录 rst.Open Sql, cnn, 1, 3 'rst执行SQL语句，并将查询结果放入记录集 '以上是第三步，编写并执行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查询结果和字段名称写入表格指定区域 ' rst.Close '关闭记录集rst cnn.Close '关闭链接 Set rst = Nothing Set cnn = Nothing '释放内存 End Sub 打个响指，关于第三个Recordset方法，目前知道有这事就好，至于它的语法，以及Recordset和Execute之间的区别，因为牵扯到更多专业的术语和知识，所以等我们聊完SQL常用语句后，详聊ADO时再说。 本节内容我们只需要掌握使用Execute语句执行SQL，以及获取字段名……也就是Execute方法（一）的代码……就OK。 5. 老干部脸，嘶哑嗓音： 各位亲，以上内容看不懂的段落请直接跳过，不要纠结，不要给自己制造心理障碍，啊，这东西好难啊，我不会……看我眼睛，再重复一遍，看不懂的段落请直接跳过，这是一个好习惯。等整个系列进行了大概一半左右，再回头来看，当初觉得困阻的地方原来也就那么回事…… 仰脸，摆摆手，经验之谈，都是经验之谈……心痛。 呵，总结一下： 对于新手而言，本章的重点是知道三步走发展战略，以及懂得复制Execute方法（一）的代码执行SQL语句，仅此而已，其它？摆摆我的小手，看过就算，大概了解下，留个印象，以后再见面好说话也就行了。 最后，还是留个练手题吧： 由于数据源并不存在标题行（HDR），且需要将汇入模式（IMEX）修改为读写，所以请将下面代码中的链接字符串修改完善，并运行成功。 Sub Mycnn3() 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 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 = Nothing End Sub |
|
|
