How to query an open ListObject?

Sometimes you create a temporary copy of the source file by SaveCopyAs (it will be closed) and use ADO to get data from it. Finally, you delete the copy of the file.

Artik

Originally Posted by Artik

Sometimes you create a temporary copy of the source file by SaveCopyAs (it will be closed) and use ADO to get data from it. Finally, you delete the copy of the file.

Thx Artik. For now, i'd like to try to do it without caching to disk.

I understand the table gets added to the workbook data model, and i understand i can query the data model (hope i don't have to use DAX).

Works:

Please Login or Register  to view this content.

But .... still ADO. Does the data model eliminate the memory leak issue?

Unfortunately, I don't know much about it, so I won't be able to help. I struggled with data in a data model once, but without much results.
From the old technology, as far as my memory is correct, DAO does not make a memory leak on an open workbook.

Artik

I just ran the test code from the original leak article. The original code failed on the following line:

Please Login or Register  to view this content.

The reason is:

The Microsoft OLE DB Provider for Jet and the Jet ODBC driver are available in 32-bit versions only. You can't run them in 64 bit mode.
https://www.connectionstrings.com/us...-environments/

This affects me because i'm on Excel 2016 64 bit. The 64-bit solution is:

With Office 2010, there are new drivers, the 2010 Office System Driver, which will be provided in both 32-bit and 64-bit versions. You can use these drivers to let your application connect to Access, Excel and text files in a 64 bit environment utilizing the new 64-bit drivers. The provider name is "Microsoft.ACE.OLEDB.12.0".

The driver is already installed, so i just need to use the ACE connection string.

Please Login or Register  to view this content.

My data contained 1 million+ records. While running, i watched Excel in Task manager (i didn't use the memory counters from the original MS article). On each loop, Excel memory consumption varied from about 200 MB to about 400 MB. Never grows beyond 400 MB, never crashes, always jumps back to 250 MB on each loop. There was no steady increase, as you'd see with a leak.

Screenshot (95).png

Screenshot (96).jpg

So i think i can conclude that the memory leak doesn't affect 64-bit Excel with the 64-bit driver.