Results 1 to 8 of 8
  1. #1
    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

    can't ZIP workbook (xl97/xl2000)

    I have a 34MB workbook with some VBA code (not tons of it) and when I run PKZIP on it, it only compress 1%. Any ideas why? It used to compress just fine, but after I added about an additional 100 lines of VBA it doesn't anymore. It doesn't matter if I set compression to high either.

    I know Excel is bad at internal garbage collection so maybe I hit some limit where it won't compress. I have no idea.

    Ideas anyone? Deb <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: can't ZIP workbook (xl97/xl2000)

    Try creating a blank workbook, then copy all existing sheets and code into the new workbook- you may well come up with a smaller .xls file. It's worth a try anyway.

    I believe there is a lot of garbage stored in Excel, most of all with VBA.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    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: can't ZIP workbook (xl97/xl2000)

    AAHHH! That's not the answer I wanted to hear <img src=/S/frown.gif border=0 alt=frown width=15 height=15>. I rebuilt other workbooks this way and it's a real pain the ass especially since I have lots of named ranges which do not copy, I have to reassign them manually. I've run some code cleaner tools on this WB but it didn't help. I do know that too much futzing (technical term) over time greatly contributes to the internal garbage pile and there's no solution (even MS supported admitted it to me).

    Thnx, Deb <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: can't ZIP workbook (xl97/xl2000)

    You might try exporting from XL2000 to html, then re-importing (this will not work in XL97)- although I just tried this, and I got a larger file. At least it kept macros, named ranges, graphs and everything.

    What code cleaner did you use?

    If it's something you do often (and it sounds like it is), you might make a macro to do the copy- first workshhets, code, and then the named ranges:

    For Each xlnName In Excel.Names
    sName = xlnName.RefersTo

    and copying the named ranges to the new workbook.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    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: can't ZIP workbook (xl97/xl2000) - SOLVED

    It's not corrupted, just cant' zip it.

    I FOUND THE SOLUTION!!!! It turns out that if a workbook is protected it can not be zipped. I had two people tell me this the other day when I was explaining my problem and I proved it to myself as well.

    My workbook opens in protected mode (the VBA protects and hides some sheets). The user has to login to unprotect a sub-set of these worksheets. I ended up removing some protection code at workbook_close since I reapplied the protection there again (in case the user ran without macros enabled the next time the workbook was opened - this allows me to hide worksheets even if macros disabled).

    So I now open the workbook, go through my login procedure, save it and it zips nicely (34MB down to 5MB).

    Aaaahhhh, so relieved!

    Thanks all for your great ideas.
    Deb <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: can't ZIP workbook (xl97/xl2000)

    <P ID="edit"><FONT SIZE=-1>Edited by WebGenii on 16-Aug-01 21:48.</FONT></P>Microsoft has a tool to recover a damaged XL 97 file. I believe the KB reference is <A target="_blank" HREF=http://support.microsoft.com/directory/article.asp?id=KB;EN-US;Q142117>Q142117-Summary of methods to recover data from corrupted files</A>. I have used it and it does recover all range names, formulas, data, code and etc.. It may be worth your time to recover the XL file and then Zip the recovered file.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: can't ZIP workbook (xl97/xl2000) - SOLVED

    I may misremember some of this, but many encryption techniques use tokenization to reduce character repetition and therefore lower the odds against brute force attacks (there used to be a pretty good explanation of this in the PGP for DOS documentation, you might poke around at <A target="_blank" HREF=http://www.pgpi.org/>PGP</A>). Tokenization is one of the techniques also used in compression, so encryption tends to reduce compressibility. However it always puzzled me that protected Excel Workbooks are not smaller, which they ought to be if they are encrypted.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: can't ZIP workbook (xl97/xl2000)

    Have you tried to reset the active range of the worksheets in your workbook?
    1 - Select the cells in columns to the right of your worksheet (last used column), then hit the delete key.
    2 - Select the cells in rows below your worksheet (last used row), then hit the delete key.
    3 - Then run this macro
    'Reset_Used_Range
    '
    '
    Sub Reset_Range()
    ActiveSheet.UsedRange
    End Sub

Posting Permissions

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