How can I run SQL statements on a named range within an excel sheet?

Question

All I am trying to do is take a standard range on an excel sheet (i.e. a named range, or even A1:F100), and run some sql queries on it, and return a recordset that I can either step through in VBA code, or even just paste into some other sheet in the same workbook.

Using ADODB was one thought, but how could I setup the connectionstring to point to some range within the current workbook?

I know before I have made use of the Microsoft query wizard, which was not ideal, but would work. I can't seem to get this to refer to a range within the sheet, only other excel files.

---

Here is the function I am left with. When I run it a few times my excel crashes with the usual out of resources error message. I have removed this function from my spreadsheet, and everything runs seamlessly multiple times, thus it is definitely caused by the code here.

I have cleaned up all the objects (correctly?). Does anyone have any ideas what could be going wrong? Could there be something in the connection string that could be tweaked, or could it be something to do with the variant that is returned from the GetRows method?

I am using MS ADO 2.8, and have also tried 2.5 with the same behaviour.

Function getTimeBuckets() As Collection

Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim dateRows As Variant
Dim i As Integer
Dim today As Date

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set getTimeBuckets = New Collection

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
cn.Open strCon

strSQL = "SELECT DISTINCT(Expiration) FROM [PositionSummaryTable] where [Instrument Type] = 'LSTOPT'" 

rs.Open strSQL, cn


dateRows = rs.GetRows
rs.Close

'today = Date
today = "6-may-2009"

For i = 1 To UBound(dateRows, 2)
    If (dateRows(0, i) >= today) Then
        getTimeBuckets.Add (dateRows(0, i))
    End If
Next i

Set dateRows = Nothing
Set cn = Nothing
Set rs = Nothing
End Function

Answer 1

You can just use the name.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset 

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

''Pick one:
strSQL = "SELECT * FROM DataTable" ''Named range
strSQL = "SELECT * FROM [Sheet1$A1:E346]" ''Range

rs.Open strSQL, cn

Debug.Print rs.GetString

In response to question part 2

I notice that you only want today's records, so you should be able to modify the sql to:

strSQL = "SELECT DISTINCT(Expiration) FROM [PositionSummaryTable] " _
& "where [Instrument Type] = 'LSTOPT' AND [Expiration]=#" _
& Format(Date(),"yyyy/mm/dd") & "#"

You have not closed the connection:

cn.Close

And then

 Set rs=Nothing
 Set cn=Nothing

Answer 2

How about using of LIKE clause?

I tried to use:

select * from [PES$] where PID_TAG like '*5400001'

without success....

this works:

select * from [PES$] where PID_TAG = 'PIT5400001'

but this is not I want.

EDIT

hummm.... we need to change wildcards to work... don't use *, use %