Excel/Vba/ADODB: how to connect to Table (ListObject) only with its name, to perform a query?

I have tried to get a solution to my problem, looking also to the past questions here reported, but I didn't find a solution.

Problem: in VBA, how I can specify the name of a Excel table, inside an ADODB query, if I know only the name of the table (not its address range)?

I have to query some data of a given named table ("Table_1A"), stored in a given worksheet (named "1A") of an other closed workbook saved in resource data directory/folder of my PC.

It seems that ADODB is not able to get a connection to the specified table (it seems that Tables are invisible to ADODB).

Note: classical "hand made" table (ListObject) with the first column containing a set of materials and the other columns contains the physical property of the material versus temperature (temperature value identify the column name). No pivot table or other table created querying databases.

A connection to a Table is only possible if I specify the sheet name plus the address range of the table. If I specify only the name of the Table ... it doesn't exist!

The problem is that the address range of the table to be queried, may change during the time (sheet name where the table is stored and updated, doesn't change along the time).

Thanks in advance for the help.

Ann

Solution

I wrote an answer about this a few hours ago, but I had to delete it after GSerg noticed my mistake due to lack of sleep, I guess. Thanks for GSerg's warnings...

Now, I've prepared another alternative where;

The closed workbook named "Employee.xlsx" has a "ListObject" named "Table1" on sheet "Sheet1". (Note that, "Table1" is not a defined-name.)
The workbook named "Book1.xlsm" is housing the below macro which; converts the closed workbook ("Employee.xlsx") to a zip file, extract the contents of the zip file to a temporary folder (named "zipFolder") and gets address of the data range of the ListObject ("Table1") from the related XML file which is ".....\xl\tables\Table1.xml"
Using this range address, the data in the closed workbook is dumped on the active sheet starting from cell A2 by using ADO

This approach may not be neat but if anyone who wants to work with the name of the "ListObject" rather then a defined name, this may be an alternative.

Both workbooks should be in the same folder...

So, the code is;

Option Explicit
'
Sub Test()
    Dim ListObjectName As String, objShell As Object, zipFile As Variant, myFile As String, zipFolder As Variant
    Dim xDoc As Object, myNode As Object, DataRange As String, adoCN As Object, RS As Object, strSQL As String
    
    ListObjectName = "Table1"
    
    zipFolder = ThisWorkbook.Path & Application.PathSeparator & "zipFolder"
    
    If Dir(zipFolder, vbDirectory) = "" Then
        MkDir ThisWorkbook.Path & "\zipFolder"
    End If
    
    myFile = ThisWorkbook.Path & "\Employee.xlsx"

    zipFile = ThisWorkbook.Path & "\zipFolder\Employee.zip"
    
    Name myFile As zipFile
    
    Set objShell = CreateObject("Shell.Application")
    objShell.Namespace(zipFolder).CopyHere objShell.Namespace(zipFile).items
    
    Name zipFile As myFile
    
    Set xDoc = CreateObject("MSXML2.DOMDocument")
    xDoc.async = False
    xDoc.validateOnParse = False
    
    xDoc.Load zipFolder & "\xl\tables\" & ListObjectName & ".xml"
    
    Set myNode = xDoc.SelectSingleNode("//table")
    DataRange = myNode.Attributes.getNamedItem("ref").Text
    
    Range("A2:C" & Rows.Count) = Empty
    
    Set adoCN = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
    
    adoCN.Provider = "Microsoft.ACE.OLEDB.12.0"
    adoCN.Properties("Data Source") = myFile
    adoCN.Properties("Extended Properties") = "Excel 12.0 Macro; HDR=Yes; IMEX=1"
    adoCN.Open
        
    strSQL = "Select * From [Sheet1$" & DataRange & "]"
    
    RS.Open strSQL, adoCN
    
    Range("A2").CopyFromRecordset RS
    
    CreateObject("Scripting.FileSystemObject").DeleteFolder zipFolder
        
    RS.Close
    adoCN.Close
    
    Set RS = Nothing
    Set adoCN = Nothing
    Set adoCN = Nothing
    Set xDoc = Nothing
    Set objShell = Nothing
End Sub