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

You can just use the name.

Dim cn As ADODB.ConnectionDim rs As ADODB.Recordset strFile = Workbooks(1).FullNamestrCon = "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 rangestrSQL = "SELECT * FROM [Sheet1$A1:E346]" ''Rangers.Open strSQL, cnDebug.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

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 %

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?