使用 VBA 在 Excel 中使用 SQL 样式的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13624967/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use SQL-style query in Excel using VBA
提问by cronoklee
I have a large excel sheet which looks similar to this:
我有一个大的 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
What I want to do is create a new, dynamically generated table showing all the men.
我想要做的是创建一个新的、动态生成的表格，显示所有男性。
In SQL this would be a synch: "SELECT * FROM table WHERE type='men'". I've never used VBA in excel before (tho I am an experienced PHP/Javascript programmer and have used VBA in MS Access) so I'm looking for beginners instructions to get me started. Perhaps someone can recommend a simple tutorial or blog post that does something like what I need to do?
在 SQL 中，这将是一个同步："SELECT * FROM table WHERE type='men'". 我以前从未在 excel 中使用过 VBA（虽然我是一位经验丰富的 PHP/Javascript 程序员，并且在 MS Access 中使用过 VBA），所以我正在寻找初学者的说明来帮助我入门。也许有人可以推荐一个简单的教程或博客文章来做我需要做的事情？
回答by cronoklee
It took me most of the day but I have figured this out. Here's the code:
我花了一天的大部分时间，但我已经想通了。这是代码：
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
To get this working on your own workbook, you'll need to change the Data Sourcepath to the name of the file youre using.
要在您自己的工作簿上执行此操作，您需要将Data Source路径更改为您正在使用的文件的名称。
[Sheet1$]in the query is the name of the sheet you are selecting from (leave in the $).
[Sheet1$]在查询中是您要从中选择的工作表的名称（留在 中$）。
Worksheets(2)is the number of the sheet where you are creating the dynamic table.
Worksheets(2)是您要在其中创建动态表的工作表的编号。
Additionally, you'll need to enable one of the the Microsoft Active X Data Objectslibraries by going to Tools>Referencesin the VBA editor in excel.
此外，您需要在 Excel 中的 VBA 编辑器中启用其中一个Microsoft Active X Data Objects库Tools>References。
