Sql query on a range in the opened worksheet

[Shaul Bel]

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

 

[Dick Kusleika]

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;MaxBufferSize=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