Writing SQL Queries Against Virtual Tables in Excel VBA

[robnjay]

OK. I got it to work:

Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rData = ws.Range("Query_from_MS_Access_Database")  
Set rs = CreateObject("ADODB.Recordset")
With rs
    Set .ActiveConnection = cn
    .Source = "Select [TimeStamp], [Value], [Last6MinAvg]  From [Sheet1$" & rData.Address(False, False) & "] " & _
    "Where [Last6MinAvg] >= 10 Order By [Timestamp] DESC"
    .Open , , 3, 3
    ws.[D14].CopyFromRecordset rs
    .Close
    .Source = "Select [TimeStamp], [Value], [Last6MinAvg]  From [Sheet1$" & rData.Address(False, False) & "] " & _
    "Where [Last6MinAvg] >= 25 Order By [Timestamp] DESC"
    .Open , , 3, 3
    ws.[H14].CopyFromRecordset rs
    .Close
End With

This is the _only_ way I can get the reference to work.... Even though there is REALLY a name in the workbook named "Query_from_MS_Access_Database", "From Query_from_MS_Access_Database" won't work.

PLEASE NOTE: the "secret" here was to retrieve the address of the range as RELATIVE (rData.Address(False, False)) and append it to the Worksheet name.

So "From [Sheet1$A1:C1340] ...." works!