使用VBA在Excel中使用SQL风格的查询

Question

我有一个类似这样的大型Excel表格：

date       |  name  |  age  |  type
10/10/2012 | James  |  12   |  man 
11/10/2012 | Jane   |  50   |  woman 
12/10/2012 | Freddy |  2    |  dog
13/10/2012 | Bob    |  23   |  man
14/10/2012 | Mary   |  34   |  woman 

我想要做的是创建一个新的、动态生成的表格，显示所有男性。

在SQL中，这很简单："SELECT * FROM table WHERE type='men'"。虽然我以前从未在Excel中使用过VBA（但我是一名经验丰富的PHP/Javascript程序员，并且曾经在MS Access中使用过VBA），因此我正在寻找初学者指南来帮助我入门。也许有人可以推荐一个简单的教程或博客文章，类似于我需要做的事情？

- cronoklee

---

请查看此答案：https://dev59.com/n2oy5IYBdhLWcg3wHqiB#8766541 - chris neilsen

可能是重复的问题：在工作表数据上执行类似SQL查询的Excel函数？ - chris neilsen

谢谢@chrisneilsen。看起来是同样的问题。您能否请解释如何在我的示例中使用您的子程序？ - cronoklee

Answer 1

我花了一整天的时间才弄明白这个问题。以下是代码：

Sub Excel_QueryTable()

Sheet2.Cells.ClearContents

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String

Dim qt As QueryTable

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\t.xlsm;Extended Properties=Excel 8.0;Persist Security Info=False"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

SQL = "Select * from [Sheet1$] WHERE type='man'"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

Set qt = Worksheets(2).QueryTables.Add(Connection:=oRS, _
Destination:=Range("A1"))

qt.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
End If

If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

End Sub

要使此功能在您自己的工作簿中运行，您需要将“数据源”路径更改为您正在使用的文件名称。

查询中的“[Sheet1 $]”是您选择的工作表的名称（保留“$”）。

“Worksheets（2）”是您创建动态表的工作表编号。

此外，您需要通过转到Excel中的VBA编辑器中的“工具>引用”来启用其中一个“Microsoft Active X Data Objects”库。

- cronoklee

---

顺便提一下，如果您只想根据另一列的值（例如，在上面的表中将所有男性年龄相加）对一列中的总数进行求和，可以使用这个简单的单元格公式：SUMIFS(C:C,D:D,"=man")。 - cronoklee

除非活动工作表是目标工作表，否则需要显式地指定目标工作表：Set qt = Worksheets(2).QueryTables.Add(Connection:=oRS, _ Destination:=Worksheets(2).Range("A1")) - mpalanco