excelvbaadodb

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;

    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