How do I access an Excel named table via ADO/ODBC?

I have a workbook with multiple sheets, and have created Named Tables (NOT ranges) in the work book. For illustrative purposes, these table names are tblA, tblB, and tblC. I am unable to find a way to query these tables via ADO or ODBC. I can access named ranges, 开发者_运维问答but this will not work for my needs.

Thanks!

I don't know if it can be done directly but will be interested to see if anyone comes back with a working method. The GetSchema collection of ADO only seems to pick up sheetnames and named ranges but not ListObjects which named tables are. Below is a workaround but it means opening Excel to find the header/data range of the table. It's almost pointless using ADO or similar then as you can copy the data directly but I suppose you could convert to a named range before saving as a one-off task?

Option Explicit

Sub test()
Dim WB As Workbook, WS As Worksheet, strExcelfile As String, strSheetName As String
Dim strTableName As String, objListObj As ListObject, HeaderRange As String
Dim strSQL As String, DataRange As String

strExcelfile = "C:\Users\osknows\Desktop\New folder\test.xlsm"
strSheetName = "Sheet1"
strTableName = "TableName"


Set WB = GetObject(strExcelfile) 'Filepath & Filename
Set WS = WB.Sheets(strSheetName) 'SheetName
Set objListObj = WS.ListObjects(strTableName) 'Table Name

'get range of Table
HeaderRange = objListObj.HeaderRowRange.Address
DataRange = objListObj.DataBodyRange.Address

'write data directly if required
With ThisWorkbook
    With Sheet1
    '.Range(HeaderRange).Value = WS.Range(HeaderRange).Value
    '.Range(DataRange).Value = WS.Range(DataRange).Value
    End With
End With

'or use ADODB which is a bit pointless now!
Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset

cnn1.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & strExcelfile & ";" & _
    "Extended Properties=""Excel 12.0;HDR=No;IMEX=1"";"

strSQL = "SELECT * FROM [" & strSheetName & "$" & Replace(DataRange, "$", "") & "];"
rst1.Open strSQL, cnn1, adOpenStatic, adLockReadOnly


'tidy up
Set objListObj = Nothing
Set WS = Nothing
WB.Close
Set WB = Nothing
End Sub