![2f4581146a69e53d099e6449f3c4d149.gif](https://i-blog.csdnimg.cn/blog_migrate/c759b2911059fa69be623521cf24094d.gif)
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 档案可同时支援“读取”与“写入”用途。
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语句部分。
请看效果:
![2610402102a168298557c14450ca7aa8.gif](https://i-blog.csdnimg.cn/blog_migrate/77ee8ecd16acb7243858a59dfc62c58e.gif)
'只需要修改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-
写留言