Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Dec 2002
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Memory recovery in Excel (Excel 97 SR-2)

    Sometime back I posted a question about a fairly complex Excel spreadsheet bulking up mysteriously. We have learned strategies for reducing the excess fat, at least as regards our circumstances. The problem seems to arise when we import data from an Access database using the QueryTables.Add method. Let me describe the two things we do.

    1) Within the VBA code we execute the following routine after each import:
    ========================================
    Function CleanWorkbook() As Boolean
    On Error GoTo Err_CleanWorkbook

    Dim nm As Name
    Dim wks As Workbook
    Dim qtb As QueryTables
    Dim strName As String

    CleanWorkbook = False ' Assume the worst

    ' Delete all external data ranges at the Workbook level
    For Each nm In ActiveWorkbook.Names
    strName = nm.Name
    If InStr(strName, "ExternalData") Or InStr(strName, "Query_from_MS_Access") _
    Or InStr(strName, "_FilterDatabase") Or InStr(strName, "Auto_Open") _
    Or InStr(strName, "QUERY") Then
    'Debug.Print strName
    nm.Delete
    End If
    Next nm

    ' Delete all QueryTables and external data ranges on the Worksheets
    For Each wks In Worksheets
    For Each qtb In wks.QueryTables
    qtb.Delete
    Next qtb

    For Each nm In wks.Names
    strName = nm.Name
    If InStr(strName, "ExternalData") Or InStr(strName, "Query_from_MS_Access") _
    Or InStr(strName, "_FilterDatabase") Or InStr(strName, "Auto_Open") _
    Or InStr(strName, "QUERY") Then
    'Debug.Print strName
    wks.Names(strName).Delete
    End If
    Next nm
    Next wks

    CleanWorkbook = True

    Exit_CleanWorkbook:
    Exit Function

    Err_CleanWorkbook:
    ShowErrMsg Err.Number & " " & Err.Description, "Error in mdlUtilies: CleanWorkbook", vbExclamation
    Resume Exit_CleanWorkbook
    Resume Next

    End Function
    ========================================

    Deleting any QueryTables seems to be the biggy.

    2) We open and save the workbook using Excel 2000. We do most of our development in Excel 97 as that is the target environment. However one of our developers uses Excel 2000, and we have discovered that the workbook can shrink dramatically he works on it.

    If anyone has more insight into recovering lost space in an Excel 97 workbook or has a warning to offer about our strategy, I would welcome the feedback.

    Harvey P. Morgan
    Schlumberger



    That oft

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memory recovery in Excel (Excel 97 SR-2)

    Maybe you've already done this, but after you have run the cleaning routine, where does control-end take you on the worksheets? If it is way beyond your data, maybe you should add deleting code to delete excess rows/columns.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Dec 2002
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Memory recovery in Excel (Excel 97 SR-2)

    We haven't thought to try that. Good idea. I'll test to see where control-end jumps the cursor to on the worksheets of the current master copy of the development workbook.

    Thanks for the suggestion,
    Harvey P. Morgan

Posting Permissions

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