Refering to a ListObject in SQL (VBA, Access OLEDB, Excel data source)

haba713 1 Reputation point
2021-10-10T07:46:38.047+00:00

You can use SQL in Excel VBA to query ListObjects (aka Format as Table) like this:

   Dim conn As ADODB.Connection  
   Dim rs As ADODB.Recordset  
   Set conn = CreateObject("ADODB.Connection")  
   conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _  
     & ThisWorkbook.FullName _  
     & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"  
   Set rs = CreateObject("ADODB.Recordset")  
   s = ThisWorkbook.Sheets("MyWorkSheet").ListObjects("MyTableName").Range.Address(0, 0)  
   rs.Open "SELECT [MyFirstLineHeader] FROM [MyWorkSheet$" & s & "]", conn  
   While (Not rs.EOF)  
       ...  
   Wend  

How can I refer directly to a table (ListObject) name in SQL? FROM [MyTableName] does not work.

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,707 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
853 questions
{count} votes