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 StringDim oListObject As ListObjectDim wb As WorkbookDim ws As WorksheetSet wb = ThisWorkbookFor 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 oListObjectNext wsEnd Function

In your SQL use it like this

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

Building on Joan-Diego Rodriguez's routine with Jordi's approach and some of Jacek Kotowski's code - This function converts any table name for the active workbook into a usable address for SQL queries.

Note to MikeL: Addition of "[#All]" includes headings avoiding problems you reported.

Function getAddress(byVal sTableName as String) as String     With Range(sTableName & "[#All]")        getAddress= "[" & .Parent.Name & "$" & .Address(False, False) & "]"    End WithEnd Function