基础扩展 | 11. 使用ADO和SQL在Excel工作表中执行查询操作

学习Excel技术，关注微信公众号：

excelperfect

我们可以将存储数据的工作表当作数据库，使用ADO技术，结合SQL查询语句，可以在工作表中获取满足指定条件的数据。

首先，需要添加ADO引用。在VBE中，单击菜单“工具——引用”，在“引用”对话框中，找到并选取“Microsoft ActiveX Data Objects 6.1 Library”，如下图1所示。

图1

下面，需要将工作表Sheet2的数据中物品为“苹果”的数据行复制到工作表Sheet3中，如下图2所示。

图2

可以使用下面的代码：

Sub ReadFromWorksheetADO()
    Dim wksData As Worksheet
    Dim wksResult As Worksheet
    Set wksData =ThisWorkbook.Worksheets("Sheet2")
    Set wksResult =ThisWorkbook.Worksheets("Sheet3")
    '清除已存在的数据
    wksResult.Cells.ClearContents
    Dim conn As New ADODB.Connection
    conn.Open"Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" &ThisWorkbook.FullName & ";" & _
            "ExtendedProperties=""Excel 12.0;HDR=Yes;"";"
    '在字符串中存储查询语句
    Dim query As String
    query = "Select * from [" &wksData.Name _
                    & "$] Where 物品='苹果' "
    '运行查询并存储在记录集中
    Dim rs As New Recordset
    rs.Open query, conn
    '输入标题
    Dim i As Long
    For i = 0 To rs.Fields.Count - 1
        wksResult.Cells(1, i + 1).Value2 =rs.Fields(i).Name
    Next i
    '输入数据
   wksResult.Range("A2").CopyFromRecordset rs
    '关闭连接
    conn.Close
End Sub

代码的图片版如下：

代码中，打开并连接数据库的语句为：

conn.Open"Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" &ThisWorkbook.FullName & ";" & _

"Extended Properties=""Excel12.0;HDR=Yes;"";"

适用于2007及以上版本。在同一代码中，只需要连接数据库一次，接着可以执行多个查询操作，无需每次查询前都进行连接。

SQL查询语句为：

query = "Select * from [" & wksData.Name _

& "$] Where 物品='苹果' "

在工作表wksData中查询物品为“苹果”的记录，*号表示获取记录的所有字段。

我们还可以设置其他条件查询，获得相应的记录。例如：

1.查询物品为“苹果”且数量大于等于100的记录。

query = "Select * from [" & wksData.Name _

& "$] Where 物品='苹果' and 数量>=100"

2. 查询物品为“苹果”或者数量大于等于100的记录。

query = "Select * from [" & wksData.Name _

& "$] Where 物品='苹果' or 数量>=100"

3.查询编号以12开头的记录。

query = "Select * from [" & wksData.Name _

& "$] Where 编号 like '12%' "

4.查询编号以1结尾的记录。

query = "Select * from [" & wksData.Name _

& "$] Where 编号 like '%1' "

5.查询编号中包含200的记录。

query = "Select * from [" & wksData.Name _

& "$] Where 编号 like '%200%' "

也可以选择获取满足条件的记录中的字段。例如，查找并获取物品为“苹果”的记录中的编号、物品和单价：

query = "Select 编号,物品,单价 from [" & wksData.Name _

& "$] Where 物品='苹果' "

结果如下图3所示。

图3

关于ADO对象模型及其属性和方法的应用，以及SQL查询语句语法，有兴趣的朋友可以参考相关资料进一步了解。