Announcement

Collapse
No announcement yet.

Retrieve data from open workbook(s)

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Retrieve data from open workbook(s)

    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.

    Code:
    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
    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
    Last edited by Craig Ottley; June 9th, 2011, 08:26.
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

  • #2
    Re: Retrieve data from open workbook(s)

    In Excel 2002 SP3, this works with DAO 3.51, but you get the memory leak if you use DAO 3.6.

    BTW, does the ADO memory leak still occur in Excel 2003? The bug description doesn't mention it.
    Last edited by bugmenot2; November 8th, 2005, 21:12. Reason: added comment on office 2003

    Comment


    • #3
      Re: Retrieve data from open workbook(s)

      Hi,

      Thanks for taking Your time and for Your input on this issue

      Yes, it seems that by using DAO 3.6 we get a similar memory leak but not necessarily of the same size. Under some circumstances when using ADO the memory in use are doubled while with DAO 3.6 I've so far not experience this large leak.

      What are the outcome of Your findings regarding DAO 3.6?

      With Excel 2003 and with the recent published SP-2 the ADO memory leak still exist.
      Kind regards,
      Dennis

      .NET & Excel | 2nd edition PED | MVP

      Comment

      Working...
      X