Performing SQL queries on an Excel Table within a Workbook with VBA Macro

One thing you may be able to do is get the address of the dynamic named range, and use that as the input in your SQL string. Something like:

Sheets("shtName").range("namedRangeName").Address

Which will spit out an address string, something like $A$1:$A$8

Edit:

As I said in my comment below, you can dynamically get the full address (including sheet name) and either use it directly or parse the sheet name for later use:

ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal

Which results in a string like =Sheet1!$C$1:$C$4. So for your code example above, your SQL statement could be

strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2)

strSQL = "SELECT * FROM [strRangeAddress]"

Public Function GetRange(ByVal sListName As String) As String

Dim oListObject As ListObject
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook

For Each ws In wb.Sheets
    For Each oListObject In ws.ListObjects
        If oListObject.Name = sListName Then
            GetRange = "[" & ws.Name & "$" & Replace(oListObject.Range.Address, "$", "") & "]"
        Exit Function
        End If
    Next oListObject
Next ws


End Function

In your SQL use it like this

sSQL = "Select * from " & GetRange("NameOfTable") & ""