-
How To Set ActiveWorkbook Worksheet in Recordset
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...
[VBA]
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[/VBA]
-
That code should select all columns on Sheet1.
-
Thanks
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
-
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)
-
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!
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules