How to query an open ListObject?

[johnywhy]

I want to pull data from a ListObject into VBA using SQL statements. I don't need to pivot. I don't need any front-end display or controls.

My first thought was ADO, but a memory leak has been reported when querying an open workbook with ADO:

https://www.access-programmers.co.uk...ordset.313008/
https://stackoverflow.com/questions/...ource-exceeded
https://www.msofficeforums.com/excel...ce-script.html
https://www.decisionmodels.com/memlimitsd.htm

I don't know if this affects Excel 2016 onward.

What options are there to pull data from a ListObject in an open workbook into VBA?

I'm looking at "Connections" and "Data Model". It seems that creating a "new Connection" is the simplest way to connect to a ListObject, correct? Can i execute a SQL statement against a Connection? Is it necessary to add the table to the Data Model?

Is there a different/better/easier way?

I think i don't need to use a Query object, because i think that's a front-end display component which sits on top of Connections, and therefor just adds more overhead, correct?

I think i might make use of one of the Power addins, but prefer to use the core features without addins, if possible. Again, seems like unnecessary front-end overhead.

(also asked here
http://www.vbaexpress.com/forum/show...bject&p=411636
https://www.excelforum.com/excel-pro...istobject.html)