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
'Close.
DAO_rs.Close
DAO_db.Close
DAO_ws.Close
'Release objects from memory.
Set DAO_rs = Nothing
Set DAO_db = Nothing
Set DAO_ws = Nothing
End Sub
Display More
Datatypes
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