Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Retrieve data from open workbook(s)

  1. #1
    Join Date
    25th January 2003
    Location
    Ístersund, Sweden
    Posts
    2,442

    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.

    VB:
    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 at 08:26.
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

  2. #2
    Join Date
    23rd June 2005
    Posts
    20

    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 at 21:12. Reason: added comment on office 2003

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th January 2003
    Location
    Ístersund, Sweden
    Posts
    2,442

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Open & Retrieve Webpage Data
    By 27458 in forum Excel General
    Replies: 2
    Last Post: November 3rd, 2006, 17:25
  2. Retrieve data from Workbook using ADO & VBA
    By Buliwyf in forum Excel General
    Replies: 2
    Last Post: October 11th, 2006, 05:31
  3. Links and Workbook Open to retrieve data
    By Bpoitras in forum Excel General
    Replies: 2
    Last Post: July 27th, 2006, 06:31
  4. Retrieve data from another workbook based on a date
    By shayne-tas in forum Excel General
    Replies: 1
    Last Post: October 3rd, 2005, 19:26

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno