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

Question

This is a very simple question which I am pulling my hair out with. No amount of web searching seems to help!

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.

Can anyone please help me before I quit programming forever!

Your help is much appreciated.

Mike

Answer 1

Hi,

You will find all You need below:

First link is for VB.NET, links 2 - 6 are for VBA.

One

Two

Three

Four

Five

Six

Found all in google, about 2 - 4 minutes.

Answer 2

I don't know about VBA, but there are code online in Delphi and C# that uses the format

SELECT * FROM [SheetName$A1:B2]

Have you tried it?

Answer 3

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 4

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.

Many thanks for all your help!

-Mike

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