CODEWITHSUNDEEP
mysqlsqlexcelvba
cronoklee
cronoklee

Reputation: 6702

Use SQL-style query in Excel using VBA

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?

Upvotes: 3

Views: 9627

Answers (1)

cronoklee
cronoklee

Reputation: 6702

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.

Upvotes: 6

Related Questions