When using ADO to query and retrieve data from open workbooks a memory leaks occurs and Excel's performance decrease. This is a known bug and it's described in the following KB-article at MSFT:
Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO)
But what if we want to use a "database-approach" to query open workbooks? One solution is simple to use DAO (Data Access Objects), a technology that was replaced by ADO in the mid 90's. In short, DAO was explicit targeting and optimized for the Microsoft Database Jet Engine and since MSFT wanted to have a more wider platform ADO was developed, which is also the present standard, even if ADO.NET is coming up.
What we need:
* Microsoft Windows 98/2000 and later.
* Microsoft Excel 2000 and later.
* The library DAO 3.5 and later.
As the example use early binding You need to set a reference to the Microsoft DAO 3.5 Object Library.
- Option Explicit
- Sub DAO_Database_Approach()
- Const stExtens As String = "Excel 8.0;HDR=Yes;"
- Const stSQL As String = "SELECT * FROM [Sheet2$] WHERE Dept='cc';"
- 'Variables for DAO.
- Dim DAO_ws As DAO.Workspace
- Dim DAO_db As DAO.Database
- Dim DAO_rs As DAO.Recordset
- Dim strDb As String
- 'Variables for Excel.
- Dim wbBook As Workbook
- Dim wsSource As Worksheet
- Dim wsTarget As Worksheet
- Dim rnTarget As Range
- Set wbBook = ActiveWorkbook
- Set wsTarget = wbBook.Worksheets(1)
- With wsTarget
- Set rnTarget = .Range("A2")
- End With
- strDb = wbBook.FullName
- 'Instantiate the DAO objects.
- Set DAO_ws = DBEngine.Workspaces(0)
- Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens)
- Set DAO_rs = DAO_db.OpenRecordset(stSQL, dbOpenForwardOnly)
- 'Write the Recordset to the target range.
- rnTarget.CopyFromRecordset DAO_rs
- 'Release objects from memory.
- Set DAO_rs = Nothing
- Set DAO_db = Nothing
- Set DAO_ws = Nothing
- End Sub
One common issue when working with workbooks as data sources is about the datatypes. This can be a complicated issue unless we have insight about datatypes. Here is a KB-article at MSFT that gives the basic:
Excel Values Returned as NULL Using DAO OpenRecordset
An excellent source that gives a deeper insight and workarounds is the following post: Mixed datatypes