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.



    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

  • 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.