Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Size of empty(?) WB (EXCEL 97/2000)

    I have a simple workbook with some VBA code (which was created once out of another project). I noticed it was rather large; what I did was:

    <UL><LI>Removed all sheets (ex. one of course)
    <LI>Removed all names
    <LI>Removed all VBA code (incl that attached to sheets)
    <LI>Removed everything in the last remaining sheet (actually, created a new sheet and deleted the original)
    <LI>There are also no "references" linked[/list]So what I'm left with is... well basically nothing! When I save this, the size is 132 kb !!!!

    When I make a new workbook and save that, the size is 12 kb...

    What am I missing here???

    Erik Jan

  2. #2
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size of empty(?) WB (EXCEL 97/2000)

    Probably have some sheets with un-used formated ranges. Do a End-Home and see where it gets you. If there are cell ranges not being used then Delete-Shift-Up and Delete Shift-Left to get rid of formats. Save file, it should get smaller.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size of empty(?) WB (EXCEL 97/2000)

    Been there... used several utils (ASAP!) and manual action to check that (including "Last Cell" and/or remove unused rows, columns, styles, formats etc.); also recall that I created a new sheet and deleted the original. So wrt sheets all is 'new'.

    When I "hex-look" at the sheet I do see many remains of the 'parent'-sheet inside the XLS. Have no clue where that sits and/or how I can get rid of it...

    Mysteries mysteries...

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size of empty(?) WB (EXCEL 97/2000)

    The way that Excel keeps things in the file, there are some things that, once added to the file, never go away.
    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size of empty(?) WB (EXCEL 97/2000)

    OK, that's clear but isn't that at least a security issue??? As stated, a lot of info still in the file (when looking with a hex-editor) seems 'inherited' from my original file... there's certainly information in there I do not like to be visible to others...

    In any case; isn't there a way to clean this out.... Just copying code & sheets to a new WB??? Will that work (links, names and all).... Anybody with a tool and/or "cookbook"-type of description???

    Erik Jan

    PS. Here's another one: I had access codes in a 'very hidden' sheet, someone just hex-edited the XLS-file and changed a code.. I know; passwords can be broken and sheets can be cracked but isn't this too simple???

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size of empty(?) WB (EXCEL 97/2000)

    If you have XL 2000 available, try saving the workbook to HTML format, then open the HTML file and save it back to .xls format. That might clean out most of the fluff. Make sure that you keep a copy of the original file as this sometimes results in the loss of some formatting from the workbook.
    Legare Coleman

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

    Re: Size of empty(?) WB (EXCEL 97/2000)

    Edited modules that belong to sheets will retain leftovers of the edits, even when you completely delete all code from them. Deleting the entire sheet solves that. The same applies to the thisworkbook module, junk of editing remains in there and can only be removed by opening a fresh workbook and copying everything else into it.

    For cleaning of "normal" and Class modules, try Rob Bovey's Code cleaner from the Excel page below my signature.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size of empty(?) WB (EXCEL 97/2000)

    Tried that, it works to some extent for the already stripped WB's; no impact on the full sheet

  9. #9
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size of empty(?) WB (EXCEL 97/2000)

    I did (and frequently do) use Bovey's Code Cleaner, it's one of the little gems that's always loaded in my EXCEL versions!

    I tried your suggestion and manually copied over sheets and code to a new WB, that indeed worked! Note that earlier I deleted all sheets and created new ones in the existing WB, that didn't help. The trick indeed seems to be to create a fully new WB and copy over all relevant information to that WB.

    Now the next question seems obvious... what exactly would be required for to do all this??? Just copy over the sheets & code? Would names come with these? Could this be automated? Seems like a great potential enhancement to Bovey's code cleaner... i.s.o. exporting and re-importing, what if one could export and re-import into a new-WB?? The next step would/could be to copy over the existing sheets to this new WB, name the thing and save it... Am I too simplistic here???

    Could I maybe ask Rob about this??

    Erik Jan

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

    Re: Size of empty(?) WB (EXCEL 97/2000)

    The trouble with copying sheets, is that their code modules are copied with them.
    I don't know if copying a sheet will "clean" it's code module, I stronlgy suspect it won't.

    To really clean a workbook, I think one would have to <take a deeeeeeep breath here>:

    - Open a new workbook
    - give it as many sheets as the original
    - name them the same as the original
    - copy all *cells* of each sheet of the original into their corresponding sheest of the new one
    - define all names again (use my name manager utiltity to ease this by creating a list sheet in the original workbook, copying this sheet to the new book and then clicking the "Pickup" function after selecting all names in the listing you want in the new workbook)
    - export *all* modules (and forms), including the Thisworkbook module in the original
    - Import all modules (and forms) into the new workbook.
    - whatever else I've forgotten.

    <Breath out now> <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

    I guess you could contact Rob about this: http://www.appspro.com
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size of empty(?) WB (EXCEL 97/2000)

    I agree that might be most complete. I do believe however there might be an intermediate solution that could work very well (it did form me when I did that manually):

    - Open a new workbook
    - Use Rob's Code-Cleaner functionality to save all VBA code to text files (that functionality is used and exisiting in Code-Cleaner now), do NOT re-import into the original sheet
    - Now copy over each individual sheet (which now has no attached VBA code!) to the newWB
    - [Close & don't save the original]
    - Now use the re-import part of Rob-s code to re-attach the VBA code to the sheets in the NEW WB
    - [Name and Save the new WB]
    - Don't know about what would happen to forms here; made they could be just copied over as well...

    Anyway... what would count is the final size of the newly created WB compared with the original...

    I'll do some more manual testing some time and will alert Rob to this thread in a moment...

    Erik Jan

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

    Re: Size of empty(?) WB (EXCEL 97/2000)

    I would be interested to see whether a copied sheet's code module is "clean" or not.

    Saving the entire workbook as HTML and rereading the HTML file might also help cleaning up all sorts of stuff.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size of empty(?) WB (EXCEL 97/2000)

    Tried the HTML; it works to some extent but not fully. Here's the result of some tests:

    * Original (cleaned sheet, no code, nothing in cells etc. but "remains" of big sheet): 133 Kb
    * Save HTML; close, open, Save-As XLS: 14 Kb
    * New WB, copy sheet & save: 12 Kb (code looks clean)

    (With a larger, less cleaned sheet; HTML did worse)
    ....

    Erik Jan

    PS. Jan-Karel, if you want, I can send the "Original" empty sheet to you via regular email. I examined the contents and it does show a bit too much to just put out on the web...

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

    Re: Size of empty(?) WB (EXCEL 97/2000)

    Oh well, a decrease from 133 to 14 K seems quite a good job.

    My main reason to want to clean stuff is just to prevent irratic behaviour.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size of empty(?) WB (EXCEL 97/2000)

    You left out....

    Hours of reformatting the new sheets <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

    This constant cleaning and rebuilding is why I believe Excel isn't practical for large professional projects (lots of big sheets, lots of charts, and thousands of lines of VBA). It's madness to have to do this sort of thing over and over to bypass stepping on internal piles of poop left over from who-knows-what. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Deb <img src=/S/nosleep.gif border=0 alt=nosleep width=27 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

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