使用ADO筛选Excel工作簿中的数据
(目前还没有人投票)
Loading ...这是在datawright中看到的一个示例，辑录于此，与大家分享。
可以先下载示例研究：
有许多种方法筛选Excel中的数据，最常用的方法是使用内置的自动筛选或高级筛选功能。虽然这两种内置功能非常强大，但其局限是需要将筛选的结果放置在与原数据相同的工作表内。如果需要将数据提取到不同的工作表，则需要不同的方式。
使用ADO，您可以创建查询，从而将筛选的数据放置到您想放置的位置。
示例工作簿中有两个工作表：DB_Data和Data2。其中，DB_Data里为包含约160个姓名的列表，Data2中有一个数据有效性下拉列表（即黄色底纹的单元格），可从中选择一个字母。一旦从中选择一个字母，以此字母为开头的姓就会复制到工作表Data2中。
主要的代码清单如下：
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
