[dba-Tech] Excel bloat question

Oh god yes, track changes is VERY bad, and so is sharing a workbook. Just
don't do it. Ever.

Ever!



Jon

-----Original Message-----
From: dba-tech-bounces at databaseadvisors.com
[mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
Sent: 18 May 2007 00:00
To: Discussion of Hardware and Software issues
Subject: Re: [dba-Tech] Excel bloat question

If you turn on Track Changes, the file may bloat

I believe Excel stores VBA code as P-Code or maybe just some meta-data
about the P-Code.  Much the same way access does.  Problem is this is a
one way growing operation.  Every change made requires the metadata to
be rebuilt and as such it will generally take up more space.

Now, even though you might fully cut 1/3 of the modules and code out of
the project, when you save it is not going to get any smaller, it's just
going to have much more 'whitespace' in the meta data.

To reduce file size , you could get "VBA Code Cleaner" add-in
from Baarns Consulting (www.baarns.com) (But one thing you need to note is
that, that add-in was not designed for Excel 2000. In case it doesn't run
in Excel 2000, you may need to modify the code a bit.)

http://archive.baarns.com/software/

Or see this for other methods

http://www.ozgrid.com/Excel/ExcelProblems.htm


http://www.bmsltd.ie/MVP/Default.htm
Look for Rob Bovey code cleaner

http://exceltips.vitalnews.com/Pages/T1248_Sudden_Increases_in_Workbook_File
_Size.html

I am guessing that code methods above use something like this to
reduce file size, similar to Access SaveAsText method moving
code to a new mdb.


Function SaveVBAasText()
Dim iCount As Integer
Dim tFileName As String
Dim tPath As String
Dim SuffixTxt As String

    tPath = ThisWorkbook.Path & "\ObjectsAsText" & Format(Now(), 
"yyyymmddhhnnss") & "\"
    MkDir tPath
    SuffixTxt = ".txt"
      Debug.Print tPath
    'Now save
    For iCount = 1 To Application.VBE.ActiveVBProject.VBComponents.Count
        tFileName = tPath & 
Application.VBE.ActiveVBProject.VBComponents.Item(iCount).Name & SuffixTxt
        Application.VBE.ActiveVBProject.VBComponents.Item(iCount).Export 
tFileName
    Next
Debug.Print tFileName
End Function


Susan Harkins wrote:

>Jim, this was what I planned to write about. 
>
>Jon, have you ever tried this? Anybody? I'm wondering what the difference
is
>between just deleting rows and columns and creating a new sheet or
workbook.
>
>
>Susan H. 
>
>
>I found this on the net:
>
>Jim Hale
>
>Honey Can I Shrink the Spreadsheet ? 
>
>Rodney POWELL <mailto:rodney at beyondtechnology.com>
>Microsoft MVP - Excel
>
>__
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada

_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com

--
This email has been verified as Virus free
Virus Protection and more available at http://www.plus.net