How To Set ActiveWorkbook Worksheet in Recordset

[lipse]

Hi Guys! Me again... im in a challenge now...
Im searching (and not fouding) a method to query a worksheet inside my workbook. Im thinking about use a ADO object, but im dont want to access external file, just use a analytic database that i've imported. I founded the code below in internet, tis code cand do what i want, but its just importing 1 column...

Thanks in advance... i accept any suggestion...



Sub Excel_QueryTable()

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:\SubFile.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

SQL = "Select * from [Sheet1$]"

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

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

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

[Aflatoon]

That code should select all columns on Sheet1.

[lipse]

Thanks Aflatoon, Its really working..
My doubt (not doubt just curiosity) is, how can i query this table? In my view point, i query the spreadsheet and "add a query table" in every execution. How can i add one time and query this table?

THanks

[Aflatoon]

If you wish to add a live query table, then you don't actually need any code at all but, if you do wish to use it, you should not assign a recordset to the query table - you need to assign a connection string and commandtext so that the query can be refreshed rather than adding a new one each time. (Simply recording a macro should give you the syntax you require)

[lipse]

Thx Again,
I understand yout idea... but... using the method that u reffered, i dont query a table, just use autofilter, right? My question is about querying (using SQL commands) in a table...

Sorry if my english apparentely aggressive, i just learned the basic.
Thx!

[Aflatoon]

No, you still get a querytable and you still use SQL but the table is linked to its data source so you can refresh it if the source data changes.
No need to worry about your English - it is really quite good. :)