VBA｜以SQL的方式操作（查询、汇总）工作表

在VBA中，可以将Excel工作表作为数据库进行访问，通过ADO（活动数据访问对象）访问Excel中的数据。同时，在Excel中也可以访问数据库（如Access、SQ LServer等）中的数据，并将数据填充到Excel工作表中。

因为Excel具有易用性、通用性和庞大的用户群，在一些小的应用程序中，可以将Excel作为后台数据库，用来保存用户的数据。

ADO（ActiveX Data Objects）是微软的一种数据访问技术，它被设计用来提供通用数据访问。ADO 对象模型定义了一个可编程的分层对象集合，主要由三个对象成员（Connection、Command 和Recordset），以及几个集合对象Errors、Parameters 和Fields 等组成。

可以使用Connection 对象连接到数据源，通过该对象可生成ADO 层次中的其他对象。

在VBA 中使用ADO 对象，必须先为当前工程引用ADO 的对象库，选择菜单“工具/引用”，打开“引用”对话框，在列表框中找到“Microsoft ActiveX Data Objects 2.8 Library”选项并选中，如下所示。

将ADO 对象库添加到当前工程中后，就可以使用ADODB.Connection 定义数据库连接对象变量，以及其他ADO 对象变量。

有如下工作表（表名是数据库）：

按商品名称模糊查询记录：

Sub 以SQL方式查询数据()    Dim cnn As ADODB.Connection    Dim rs As ADODB.Recordset    Dim strSql As String, str1 As String    On Error Resume Next    Set cnn = CreateObject("ADODB.Connection")    Set rs = CreateObject("ADODB.Recordset")    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _            & "Extended Properties=Excel 8.0;" _            & "Data Source=" & ThisWorkbook.FullName    str1 = ActiveSheet.Range("A2")    strSql = "Select * FROM [数据库$] Where 商品名称 like '%" & str1 & "%'"    rs.Open strSql, cnn, adOpenStatic    With ActiveSheet        .Range("A5:G1000").ClearContents        .Range("A5").CopyFromRecordset rs    End With    rs.Close    cnn.Close    Set rs = Nothing    Set cnn = NothingEnd Sub

活动工作表的效果如下：

记录集返回从数据库取回的查询结果集，可使用记录集的Open 方法打开记录集，该方法的语法格式如下：

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

各参数的含义如下。

● Source：为变量名、SQL 语句、表名、存储过程调用等。

● ActiveConnection：为连接数据库的连接字符串。

● CursorType：确定提供者打开Recordset 时应该使用的游标类型。

● LockType：确定提供者打开Recordset 时应该使用的锁定（并发）类型。

● Options：用于指示提供者如何计算Source 参数（如果它代表的不是Command 对象），或从以前保存Recordset 的文件中恢复Recordset。

据按“商品名称”进行汇总：

Sub 汇总数据()    Dim cnn As ADODB.Connection    Dim rs As ADODB.Recordset    Dim strSql As String, str1 As String    On Error Resume Next    Set cnn = CreateObject("ADODB.Connection")    Set rs = CreateObject("ADODB.Recordset")    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _            & "Extended Properties=Excel 8.0;" _            & "Data Source=" & ThisWorkbook.FullName    strSql = "Select 商品名称, sum(期初库存) FROM [数据库$] group by 商品名称"    rs.Open strSql, cnn, adOpenStatic    With Sheet1        .Range("A2:G1000").ClearContents        .Range("A2").CopyFromRecordset rs    End With    rs.Close    cnn.Close    Set rs = Nothing    Set cnn = NothingEnd Sub

将Excel 作为数据库可用在一些小型应用中，对于需要处理大量的数据时，更多的还是使用专业的数据库系统（如Access、SQL Server 数据库系统等）。在很多情况下，用户希望从这些专业的数据库系统中获取部分数据，然后在Excel 中进行分析处理。

ref

吴永佩，成丽君 《征服Excel VBA：让你工作效率倍增的239 个实用技巧 》

－End－