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
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