大家好，我是知了。 上一篇简单了解了ADO与SQL。今天给大家介绍一下在VBA中使用SQL的方法。这里只有两部分内容：一、建立ADO对数据源的链接；二、用ADO执行SQL语句，完成查询。
— 01.建立链接 —
在VBA中使用ADO的方式有两种：
「引用法」和「创建法」。也可以叫做：「前期绑定」和「后期绑定」。还可以叫做：「静态绑定」和「动态绑定」…… 1.引用法/前期绑定/静态绑定
引用ADO相关组件：打开VBA编辑器，在菜单中点选【工具】→【引用】。确保「Microsoft ActiveX Data Objects 2.8 Library」或「Microsoft ActiveX Data Objects 6.1Library」被勾选上，单击【确定】按钮关闭对话框。
引用后再声明:
向左滑动查看更多
Dim cnn As New Connection '声明链接对象Dim rst As New Recordset '声明记录集对象
不知道如何打开VBA编辑器？请戳这里 ☞如何使用VBA代码？ 2.创建法/后期绑定/动态绑定
不需要引用ADO相关组件，直接使用「CreateObject」函数创建ADO对象，即：
向左滑动查看更多
Dim cnn As Object '定义变量Dim rst As Object '定义变量Set cnn = CreateObject("ADODB.connection") '创建ado对象Set rst = CreateObject("ADODB.recordset") '创建记录集
— —
「前期绑定」的优点是运行速度会比使用后绑定方法快，而且在编程时在对象后加.可以出现智能感知列表，更易于开发。
但当他人的Excel工作簿并没有手工前期绑定ADO类库时，相关代码将无法运行，因此「后期绑定」ADO的通用性会更强些，它不需要手工绑定相关类库。
借用前辈的经验：在代码编写时，先用前期绑定偷偷懒，毕竟会出现提示词，编写完成后，再修改为后期绑定，以适用于更多人。 不过知了一直用的后期绑定。(不敢大声说话) 下面以「后期绑定」为例，建立ADO链接，也就是之前文章所说的「套路」。
向左滑动查看更多
Sub Establishcnn()'----------------- 参数声明部分 -------------------Dim cnn As Object '定义变量Dim rst As Object '定义变量Dim SQL As String '定义变量'后期绑定Set cnn = CreateObject("adodb.connection") '创建数据库连接Set rst = CreateObject("adodb.recordset") '创建一个数据集保存数据'----------------- 建立数据库连接 -------------------If Val(Application.Version) 12 Then
cnn.Open "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties='Excel 8.0;HDR=yes;IMEX=0';Data Source=" & ThisWorkbook.FullNameElse
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes;IMEX=0';Data Source=" & ThisWorkbook.FullNameEnd If'----------------- 关闭数据库连接 -------------------
rst.Close '关闭数据库连接
cnn.Close '关闭数据库连接Set rst = Nothing '将rst从内存中删除Set cnn = Nothing '将cnn从内存中删除End Sub
「建立数据库连接」各参数含义如下：
「Provider」
是Connection对象提供者名称的字符串值， 有两种选 择 ： 「Microsoft.jet.OLEDB.4.0」(简称 Jet 引擎)和「Microsoft.ACE.OLEDB.12.0」(简称 ACE 引擎)。 03版Excel是Jet引擎，其它版本可以使用ACE引擎。 ACE引擎可以访问正在打开的Excel文件，而Jet引擎是不可以的。 「Extended Properties」
是Excel版本号及其它相关信息，03版本是Excel 8.0，其它版本可以使用Excel 12.0。 「HDR=Yes」
，这代表第一行是标题，不作为数据使用，如果用「HDR=NO」，则表示第一行不是标题，作为数据来使用。系统默认的是YES。 IMEX 「IMport EXport mode」
有三种模式：
- 当 IMEX=0 时为“输出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
- 当 IMEX=1 时为“输入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
- 当 IMEX=2 时为“链接模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
「Data Source」
是数据来源当前工作簿的完整路径。 VBA代码「Application.Version」可以获取计算机的Excel版本号，因此
以上代码兼顾了03及各高级版本Excel的情况。
关于HDR和IMEX的不同参数的用法，将会在以后的内容提到。 — 02.完成查询 —
说完ADO链接的建立，接下来就是借助ADO执行SQL语句了。通常情况下，我们 只需要修改SQL语句，以及查询结果的存放位置即可。
比如要查询一个叫做“名单”的工作表的所有数据：
结合「套路」部分，完整代码如下：
向左滑动查看更多
Sub ExecuteSQL()'----------------- 参数声明部分 -------------------Dim cnn As Object '定义变量Dim rst As Object '定义变量Dim SQL As String '定义变量'后期绑定Set cnn = CreateObject("adodb.connection") '创建数据库连接Set rst = CreateObject("adodb.recordset") '创建一个数据集保存数据'----------------- 建立数据库连接 -------------------If Val(Application.Version) 12 Then
cnn.Open "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties='Excel 8.0;HDR=yes;IMEX=0';Data Source=" & ThisWorkbook.FullNameElse
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes;IMEX=0';Data Source=" & ThisWorkbook.FullNameEnd If'----------------- 设置SQL语句 -------------------
SQL = " select * from [名单$] " 'SQL语句，查询名单表的所有记录'----------------- SQL结果处理 -------------------Set rst = cnn.Execute(SQL) 'cnn.Execute()执行SQL语句，始终得到一个新的结果集rst
Range("A1:C7").ClearContents '清空区域的值For i = 1 To rst.Fields.Count '利用fields属性获取所有字段名，fields包含了当前记录有关的所有字段,fields.count得到字段的数量，由于Fields.Count下标为0，又从0开始遍历，因此总数-1
Cells(1, i) = rst.Fields(i - 1).Name '字段名，rst.Fields(i).name可以得到指定列的列名，i是从0开始计数的，第一列的i=0。Next
Range("A2").CopyFromRecordset rst '使用单元格对象的CopyFromRecordset方法将rst内容复制到以A2单元格为左上角的单元格区域'----------------- 关闭数据库连接 -------------------
rst.Close '关闭数据库连接
cnn.Close '关闭数据库连接Set rst = Nothing '将rst从内存中删除Set cnn = Nothing '将cnn从内存中删除End Sub
与「套路」相比，代码只增加了「设置SQL语句」和「SQL结果处理」两部分，而最关键的就是SQL语句部分。
请看效果：
— —
以上就是今天的内容了，这部分格式固定且枯燥，重点在于理解，如果理解也有困难……还能说啥，混个脸熟，日后好相见吧。 练一练：
试试将查询结果放在练习文件其他有颜色的位置。敲黑板……这是重点。
提示：
'只需要修改Cells(1, i)和Range("A2")就可以啦
For i = 1 To rst.Fields.Count
Cells(1, i) = rst.Fields(i - 1).Name 'Cells(1, i)是放置标题的第一个单元格Next
Range("A2").CopyFromRecordset rst 'A2是放置数据的第一个单元格
练习文件已上传至公众号，后台回复522，即可收到下载链接。 就到这里吧。 休息，休息一下。 -END-
