Using SQL to query Excel Data
 

Antonio A. Oliv Wrote:
Yes you could "query" a SQL in Excel Sheet.

First of all you should "name" the "range" of "Excel Table" in your
SOURCE of DATA

Tip #1:
- Open a ODBC connect and use code LIKE "Teste2"

Tip #2:
- Use EXAMPLE like "Teste3"


Sub Teste2()
Dim dbs As Database, rst As Recordset, nTotReg As Double, p As Object
Set dbs = Workspaces(0).OpenDatabase("DataBase_Excel",
dbDriverNoPrompt, True, _
"ODBC;DATABASE=;UID=;PWD=;DSN=DataBase_Excel")
Sheets("Plan2").Select
Set rst = dbs.OpenRecordset("SELECT * FROM Sales")
Cells(1, 1).Select
For Each p In rst.Fields
Cells(ActiveCell.Row, ActiveCell.Column).Value = p.Name
Cells(ActiveCell.Row, ActiveCell.Column + 1).Select
Next
nTotReg = ActiveSheet.Cells(2, 1).CopyFromRecordset(rst)
dbs.Close
End Sub


Sub Teste3()
Dim dbs As Database, tdf As TableDef, rst As Recordset, i As Integer,
nTotFields As Integer, nTotReg As Double
Set dbs = Workspaces(0).CreateDatabase("C:\TEMP\NewDB2.mdb",
dbLangGeneral)
Set tdf = dbs.CreateTableDef("ExcelTable")
tdf.Connect = "Excel
5.0;DATABASE=D:\LIB's\Excel\!_Rotinas_Solicitadas\ DataBase_Excel.xls"
tdf.SourceTableName = "Sales"
dbs.TableDefs.Append tdf

Sheets("Plan3").Select
Set rst = dbs.OpenRecordset("SELECT * FROM ExcelTable")
nTotFields = rst.Fields.Count
For i = 0 To nTotFields - 1
Cells(ActiveCell.Row, i + 1).Value = rst.Fields(i).Name
Next

Cells(2, 1).Select
rst.MoveFirst
Do While Not rst.EOF
For i = 0 To nTotFields - 1
Cells(ActiveCell.Row, i + 1).Value = rst.Fields(i).Value
Next
rst.MoveNext
Cells(ActiveCell.Row + 1, 1).Select
Loop
dbs.Close
End Sub

how can i set the "name" and "range" in the "Excel table"??
can somebody assist??


--
lsy
------------------------------------------------------------------------
lsy's Profile: http://www.excelforum.com/member.php...o&userid=23917
View this thread: http://www.excelforum.com/showthread...hreadid=347252