本文所有论述和观点均是基于Excel平台,更准确的说是MS Excel……如无特殊情况,文中将不再作特殊说明。
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中,按
一种是使用代码后期绑定。
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").CopyFromRecordsetcnn.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
安~拥抱~
閱讀更多 我要報告 的文章