基础扩展 | 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查询语句语法,有兴趣的朋友可以参考相关资料进一步了解。

原文发布于微信公众号 - 完美Excel(excelperfect)

原文发表时间:2019-06-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券