2017年罗顿发展重组:使用ADO筛选Excel工作簿中的数据
来源:百度文库 编辑:中财网 时间:2024/04/28 18:03:47
有许多种方法筛选Excel中的数据,最常用的方法是使用内置的自动筛选或高级筛选功能。虽然这两种内置功能非常强大,但其局限是需要将筛选的结果放置在与原数据相同的工作表内。如果需要将数据提取到不同的工作表,则需要不同的方式。
使用ADO,您可以创建查询,从而将筛选的数据放置到您想放置的位置。
假如工作簿中有两个工作表:DB_Data和Data2。其中,DB_Data里为包含约160个姓名的列表,Data2中有一个数据有效性下拉列表(即黄色底纹的单元格),可从中选择一个字母。一旦从中选择一个字母,以此字母为开头的姓就会复制到工作表Data2中。
(以下代码复制到模块或Data2中) Option Explicit
Sub ADO_Self_Excel()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String
Dim sPath As String
Dim MyConn
Dim sFilter As String
sPath = ActiveWorkbook.FullName
'定义筛选和提取姓名的SQL语句.
'在ADO中使用%作为通配符而不是*
sFilter = UCase(Sheets("Data2").Range("H1").Value) & "%"
'在SQL中可以像表一样看待工作表名称
'为此,将后缀$放置在名称的末尾并加上方括号
sSQL = "SELECT * FROM [DB_Data$]" 'DB_Data是源工作表
sSQL = sSQL & " WHERE LastName Like '" & sFilter & "'"
'建立对相同文件的连接
'当连接到Excel而不是数据库时,需要定义扩展的属性为Excel 8.0 (第1个使用ADO的Excel版本)
MyConn = sPath
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open MyConn
End With
'定义基于SQL语句的记录集
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, _
ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockOptimistic, _
Options:=adCmdText
Application.ScreenUpdating = False
'删除目标工作表中已存在的数据
'然后以单元格A2开始填充最新筛选的结果
'完成后,清除引用以避免内存泄漏
With Sheets("Data2") 'Data2是目标工作表
.Range("A1").CurrentRegion.Offset(1, 0).Clear
.Range("A2").CopyFromRecordset rst
End With
rst.Close
cnn.Close
Application.ScreenUpdating = True
End Sub
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Range("H1"), Target) Is Nothing Then Exit Sub
Call ADO_Self_Excel
End Sub
使用ADO,您可以创建查询,从而将筛选的数据放置到您想放置的位置。
假如工作簿中有两个工作表:DB_Data和Data2。其中,DB_Data里为包含约160个姓名的列表,Data2中有一个数据有效性下拉列表(即黄色底纹的单元格),可从中选择一个字母。一旦从中选择一个字母,以此字母为开头的姓就会复制到工作表Data2中。
DB_Data表:
Data2表:
主要的代码清单如下:
(以下代码复制到模块或Data2中) Option Explicit
Sub ADO_Self_Excel()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String
Dim sPath As String
Dim MyConn
Dim sFilter As String
sPath = ActiveWorkbook.FullName
'定义筛选和提取姓名的SQL语句.
'在ADO中使用%作为通配符而不是*
sFilter = UCase(Sheets("Data2").Range("H1").Value) & "%"
'在SQL中可以像表一样看待工作表名称
'为此,将后缀$放置在名称的末尾并加上方括号
sSQL = "SELECT * FROM [DB_Data$]" 'DB_Data是源工作表
sSQL = sSQL & " WHERE LastName Like '" & sFilter & "'"
'建立对相同文件的连接
'当连接到Excel而不是数据库时,需要定义扩展的属性为Excel 8.0 (第1个使用ADO的Excel版本)
MyConn = sPath
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open MyConn
End With
'定义基于SQL语句的记录集
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, _
ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockOptimistic, _
Options:=adCmdText
Application.ScreenUpdating = False
'删除目标工作表中已存在的数据
'然后以单元格A2开始填充最新筛选的结果
'完成后,清除引用以避免内存泄漏
With Sheets("Data2") 'Data2是目标工作表
.Range("A1").CurrentRegion.Offset(1, 0).Clear
.Range("A2").CopyFromRecordset rst
End With
rst.Close
cnn.Close
Application.ScreenUpdating = True
End Sub
下面的代码使用相应工作表Data2中的Worksheet_Change事件。这样,当单元格H1发生变化时,ADO_Self_Excel将使用H1中的内容创建筛选。如果H1为空,那么返回所有的记录。
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Range("H1"), Target) Is Nothing Then Exit Sub
Call ADO_Self_Excel
End Sub
EXCEL中的数据筛选,查找问题!棘手!~
excel数据筛选
excel 筛选重复数据
EXCEL数据筛选
Excel数据筛选怎么用?
Excel的数据筛选设置
Excel跨工作表筛选
Excel高级筛选的使用
EXCEL中的筛选问题?急!
怎样通过asp中的ADO把excel中数据导入到Access数据库中?
在excel使用哪个函数引用另一个工作表中的数据,函数ISNA,VLOOKUP的使用方法?
关于Excel数据筛选的功能?
EXCEL筛选重复数据这样行吗
excel筛选重复数据的问题
excel的共享工作簿使用中的问题
Excel中如何使用筛选工具啊?
电子表格的问题 Excel中的重名筛选
如何使用vba调入其它EXCEL文件中的数据
Excel自动更正选项中的数据可不可以移动使用?
excel工作簿修改图表的源数据弹出“本工作簿不能再使用其它新字体”,这是怎么回事?
excel筛选
EXCEL 筛选
EXCEL筛选
在Excel中,如何实现数据的自动筛选?