使用VBA在Excel中使用SQL风格的查询 [英] Use SQL-style query in Excel using VBA

问题描述

  date |名称|年龄|键入
 10/10/2012 |詹姆斯12 | man 
 11/10/2012 |简| 50 |女人
 12/10/2012 |弗雷迪| 2 |狗
 13/10/2012 |鲍勃| 23 | man 
 14/10/2012 |玛丽| 34 |女人
  

我想做的是创建一个新的，动态生成的表，显示所有的人。 p>

在SQL中，这将是一个同步：SELECT * FROM table WHERE type ='men'。我以前从未使用过VBA（我是一名经验丰富的PHP / Javascript程序员，并且已经在MS Access中使用了VBA），所以我正在寻找初学者的指示，让我开始。也许有人可以推荐一个简单的教程或博客文章，做一些像我需要做的事情？

解决方案

这一天，但我已经想到了这一点。这是代码：

  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;扩展属性= Excel 8.0; Persist Security Info = False
设置oCn =新ADODB 。连接
 oCn.ConnectionString = ConnString 
 oCn.Open 
 
 SQL =从[Sheet1 $]中选择* WHERE type ='man'
 
设置oRS =新建ADODB.Recordset 
 oRS.Source = SQL 
 oRS.ActiveConnection = oCn 
 oRS.Open 
 
设置qt = Worksheets（2）.QueryTables .Add（Connection：= oRS，_ 
 Destination：= Range（A1））
 
 qt.Refresh 
 
如果oRS.State& adStateClosed然后
 oRS.Close 
结束如果
 
如果不是oRS是没有设置oRS =没有
如果不是oCn是没有，然后设置oCn =没有
 
 End Sub 
  

要使您在自己的工作簿上工作，您需要将数据源路径更改为您使用的文件名称。

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

工作表（2）是要创建动态表的工作表编号。

另外，您还需要通过转到工具>参考文献 Microsoft Active X数据对象 $ c>在VBA编辑器中的excel。

I have a large excel sheet which looks similar to this:

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?

解决方案

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 Source path to the name of the file youre using.

[Sheet1$] in the query is the name of the sheet you are selecting from (leave in the $).

Worksheets(2) is the number of the sheet where you are creating the dynamic table.

Additionally, you'll need to enable one of the the Microsoft Active X Data Objects libraries by going to Tools>References in the VBA editor in excel.

这篇关于使用VBA在Excel中使用SQL风格的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！