Thread: can I query data in the same Excel workbook

Hi,

Hope you can answer this easily; can I use SQL Drill to select data from another sheet, but within the same workbook? I want to make selections and don't how to do this with Excel formulas.

Ciao,

Ger

Hi Ger
hope all is cool.

Ran a quick test and it works. The connection string I tested was like this

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Al\SQL_Test.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";


Its a bit strange but you need to specify Excel 8.0 as the Excel version..

You could do the same thing via VBA and this might be a better way.
Here is an example macro
[highlight=VBA]

Sub ExampleQueryExcelRangeWithSQL()

Dim oConnection As Object
Dim oRecordset As Object
Dim sSQLString As String

Set oConnection = CreateObject("ADODB.Connection")

With oConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=Excel 8.0;"
.Open
End With

Set oRecordset = oConnection.Execute("SELECT * FROM [sheet1$]")

'//CLEAN UP
Set oRecordset = Nothing
Set oConnection = Nothing
End Sub
[/highlight]

There are more detailed examples on the microsoft.com web site..

Hope this gets you going have a nice night. Please let us know if you have any probs with this. Thanks
al