lipse
11-10-2011, 08:11 PM
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
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