3

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?

cronoklee
  • 6,482
  • 9
  • 52
  • 80
  • see [this answer](http://stackoverflow.com/a/8766541/445425) – chris neilsen Nov 29 '12 at 11:44
  • possible duplicate of [Excel function to make SQL-like queries on worksheet data?](http://stackoverflow.com/questions/8756802/excel-function-to-make-sql-like-queries-on-worksheet-data) – chris neilsen Nov 29 '12 at 11:45
  • Thanks @chrisneilsen. It looks like the same question all right. Can you please explain how to use your Sub with my example? – cronoklee Nov 29 '12 at 12:19

1 Answers1

6

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.

cronoklee
  • 6,482
  • 9
  • 52
  • 80
  • 1
    Incidentally, If you just want to SUM the totals from one column based on the value of another column (for example, to add all the men's ages together in the table above), you can use this simple cell formula: `SUMIFS(C:C,D:D,"=man")` – cronoklee Dec 01 '12 at 17:46
  • 2
    Unless the active sheet is the destination sheet, you need to specify explicitly the destination sheet: `Set qt = Worksheets(2).QueryTables.Add(Connection:=oRS, _ Destination:=Worksheets(2).Range("A1"))` – mpalanco Feb 07 '15 at 18:10