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

    Update on wkb bloat & 2 requests to enable macros (Excel 97 SR-2)

    I've recently posted two different requests for help on the forum. Thanks to those who responded. Several good suggestions were submitted, none of which managed to identify the real problem.

    As it turns out, the two problems were only symptoms of one underlying difficulty. The purpose of this post is to describe what I think I learned.

    To restate the two problems: 1) A workbook bloomed very quickly from 1.5 megs to over 3 megs, and 2) it asked the enable-macro question twice usually (but not always) when opened.

    The workbook in question is a template used by drilling engineers around the world. To begin working with the workbook s/he imports data from local Access databases into it. I use the QueryTables.Add command to do the import. Turns out that the command creates a persistent connection. Issuing the command a second time for the same database does not replace the old connection. And there is a significant space overhead associated with the connections, i.e., they bulk up the workbook.

    Deleting the dozens of connections that had been created during testing both eliminated the 2nd enable-macros request (generated by xlquery.xla) and shrank the workbook from 1.8 megs to .9.

    Harvey P. Morgan
    Schlumberger

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update on wkb bloat & 2 requests to enable macros (Excel 97 SR-2)

    One question, if I may. Where are the connections stored, and how did you delete them (ok, 2 questions)?

    Thanks

    Jon

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

    Re: Update on wkb bloat & 2 requests to enable macros (Excel 97 SR-2)

    To answer your question will take two posts. The first directly answers your question. The second post will deal with what I now think was the REAL cause of the wkb bloat.

    Your question: Where are the connections stored, and how did you delete them (ok, 2 questions)?

    The answer is given in the two chunks of VBA code below:
    ========================================
    ' 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

    ' Check for external data ranges at wks level
    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
    ========================================

    There seemed to be data stored somewhere in the wkb associated with external data ranges, and the above code found and freed up the space it took. In retrospect I'm not sure how much space that recovered because of what we discovered a little later. See next post.

    Harvey

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

    Re: Update on wkb bloat & 2 requests to enable macros (Excel 97 SR-2)

    We now a wkb that takes is a little under 900k. Depending on a Save As selection, we can double the size. In both Excel 97 and Excel 2000. saving the wkb as type Microsoft Excel Workbook keeps the wkb small. Saving it as type Microsoft Excel 97 & 5.0/95 Workbook doubles its size!!!! (And it performs noticeably more slowly.) If you save as the latter, close it, reload it, and finally save as the former, Excel does the cutest blinking/twitching on my machine, apparently as it is reclaiming space. Excel got a beat!

    So to summarize all we've learned to keep from bloating a wkb:
    1) When copying wks repeatedly, images will be copied, but not ActiveX conrols. And you may end up with multiple copies of an image on the destination wks.
    2) Adding connections to import from Access leaves behind detritus that takes up space.
    3) How you save a wkb can make a dramatic difference in its size.

    Harvey

Posting Permissions

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