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.
What options are there to pull data from a ListObject in an open workbook into VBA?
options may be affected by the amount of data in the list object
can you post a workbook with sample data?
could you read the data into a disconnected recordset, then work from that?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Sure, a disconnected recordset is fine. How? And why? Do you think disconnected would be immune to the leak?
I'm on 2016.
Assume the data is a very small set, say 10 items, 5 fields.
Why does size affect options? What if it was a million items?
I'm able to create a WorkbookConnection object pointing to a ListObject. Not sure how to pulll data from it, tho.
Code:
Dim oCon As WorkbookConnection
Set oCon = ThisWorkbook.Connections.Add2("Test 2", "Another test", "WORKSHEET;MyBook.xlsm", _
"MyBook.xlsm!MyTable", 7, False, False)
It would be cool if i could use the same statement with a SQL statement. Then we're not using ADO. (tho' maybe it's ADO under the hood?)
The following fails. It's identical to the above statement, but i tried to use a SQL statement.
Code:
Set oCon = ThisWorkbook.Connections.Add2("Test 5", "AnotheR test", "WORKSHEET;MyBook.xlsm", _
"Select * From MyBook.xlsm$A2:B4", xlCmdExcel, True, False)
Also fails with "!" instead of "$".
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:
Code:
Sub QryConn()
Dim oRs As New ADODB.Recordset, oCon As ADODB.Connection
' works
Set oCon = ThisWorkbook.Model.DataModelConnection.ModelConnection.ADOConnection
' fails
' Set oCon = ThisWorkbook.Connections("Test1").ModelConnection.ADOConnection
' Table2 is ListObject name. It's ALSO name of table in the Data Model
' so we're not using the WorkbookConnection object at all
' works. DAX, not SQL
oRs.Open "EVALUATE Table2", oCon, adOpenForwardOnly, adLockReadOnly
' works
Sheet3.Range("A1").CopyFromRecordset oRs
' works, but why have to transpose?
Dim vRecs
oRs.MoveFirst
vRecs = oRs.GetRows
vRecs = WorksheetFunction.Transpose(vRecs)
[Sheet3!A1:B3] = vRecs
' works
oRs.MoveFirst
Do Until (oRs.EOF)
Debug.Print oRs.Fields(0).Value, oRs.Fields(1).Value
oRs.MoveNext
Loop
oRs.Close
Set oRs = Nothing
oCon.Close
Set oCon = Nothing
End Sub
But .... still ADO. Does the data model eliminate the memory leak issue?
Last edited by johnywhy; Oct 3rd, 2021 at 05:28 PM.
Having never worked with the Connections, there is one thing i noticed in your second code-block (which you said fails):
Your SQL cannot work, since you tell it to SELECT from a Workbook, and from a Range within that Workbook, but which Sheet?
A Workbook can have multiple sheets (like a database: Sheet = Table)
Next: You're Select * From .... but you define a Range.
SELECT * returns all Columns!
It should be something like "SELECT TOP(3) ColumnA, ColumnB FROM MyWorkBook.Sheets(1)" assuming your Column-Headers are in Row1
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Do you think disconnected would be immune to the leak?
i can not answer this, but as it would not have to read the open workbook, it may not be causing the same issue
i have used ADO against open workbooks many times for limited operations, without any noticeable memory leak, but i was not particularly looking for one at the time
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
but it does have to read the open workbook to obtain the recordset in the first place.
but it could read from the open workbook without using sql, that is why i asked about the size of the data
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
I just ran the test code from the original leak article. The original code failed on the following line:
Code:
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & WorkbookFullName & "Extended Properties=Excel 8.0"
Error: Provider cannot be found. It may not be properly installed.
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.
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.
So i think i can conclude that the memory leak doesn't affect 64-bit Excel with the 64-bit driver.
there is some difference in the ace connection strings for the different type of workbooks, to tell if it is macro enabled or binary type, if you need more on that you should be able to find at connectionstrings.com
i think you need the ace drivers for all workbooks from excel 2007 (maybe 2010), even in a 32 bit environment, because i remember having to use ace drivers for some coding when i was still running XP 32bit
i am not sure if the ace drivers are backwards compatible to earlier versions of excel
as i said previously i had never had a known issue with memory leaks from using ado or ace drivers
maybe if you had posted your connection in the OP someone may have picked up the problem, but i had assumed your connection was valid and your problem was the memory leak
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Yep, my connection was valid. The issue of concern was the memory leak.
The connection in the original leak article fails, prolly because it's for an old version of Excel and 32-bit Jet. The 64-bit ACE connection string works fine.