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
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?
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 overhead.
Is there a different/better/easier way?
(Also asked here
https://www.vbforums.com/showthread....ject&p=5538435
https://www.excelforum.com/excel-pro...istobject.html)