ExcelBanter - Sql query on a range in the opened worksheet

Shaul

You can use ADO to do that. Set a reference to the ActiveX Data Objects
Library. Here's some example code

Sub GetXLRecords()

Dim Cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim Rng As Range
Dim Constr As String
Dim Sqlstr As String

Constr = "DSN=Excel Files;DBQ=C:\Dick\Tester\Exceldb.xls;" _
& "DefaultDir=C:\Dick\Tester;DriverId=22;MaxBufferSi ze=2048" _
& ";PageTimeout=5;"

Sqlstr = "SELECT MyTable.Name, MyTable.Number1, MyTable.Number2 " _
& "FROM `C:\Dick\Tester\Exceldb`.MyTable MyTable"

Set Cn = New ADODB.Connection

Cn.Open Constr

Set Rs = Cn.Execute(Sqlstr)

ActiveSheet.Cells(1, 1).CopyFromRecordset Rs

Rs.Close
Cn.Close

Set Rs = Nothing
Set Cn = Nothing

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Shaul Bel" wrote in message
...
I would like to know if it is possible to run Sql query on a range in the
opened worksheet and if it is, what is the syntax.
A code sample will be appreciated.

TIA

Shaul Bel