Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reduce the size of an XLS file (Excel XP)

    Hi

    I have inherited an Excel Spreadsheet which is big, 5 meg plus in size, I have deleted all the sheets except 2, both contain virtually no data, and has one small macro, it remains 5 meg, any way to reduce the size, it the some form of tidy up utility.

    Regards

    Mike

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reduce the size of an XLS file (Excel XP)

    See the thread <!post=Compacting Excel Workbooks,133,048>Compacting Excel Workbooks<!/post> for a couple of macros that should help.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reduce the size of an XLS file (Excel XP)

    Saving the workbook as an HTML file, then saving the HTML file as a workbook again may help.

    <post#=133,055>post 133,055</post: > contains a macro you can run to get rid of unused rows and columns.

    You can use the free VBA"]http://www.appspro.com/Utilities/codecleaner.htm">VBA Code Cleaner[/url] add-in to clean the VBA code.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reduce the size of an XLS file (Excel XP)

    Hi Hans,

    Ok I have tried the 2 macros listed in you response, I have removed all other macros, I have removed all other sheets, only 18 lines of data and it is still 5 meg, any ideas now.

    I would attach a copy, but even zipped it is 384k.


    Regards

    Mike

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reduce the size of an XLS file (Excel XP)

    You can try the following:

    Create a new, blank workbook with 2 worksheets.
    Go back to the 5 MB workbook.
    Select the used range of the first worksheet.
    Copy it to the clipboard.
    Paste it into the first worksheet of the new workbook.
    Repeat for the second worksheet.
    If you had formulas in one worksheet referring to the other, they will now refer to the original in the 5 MB workbook. You can use Edit | Replace to let the formulas refer to the new workbook. For example, if you have formulas that look like

    ='[MyOldWorkbook.xls]Sheet1'!A3

    You can replace [MyOldWorkbook.xls] with nothing.
    Next, create a module in the new workbook in the Visual Basic Editor, and copy the macro(s) from the 5 MB workbook into it.

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

    Re: Reduce the size of an XLS file (Excel XP)

    If you hit F5 and then select Special, Objects, does Excel find any?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reduce the size of an XLS file (Excel XP)

    Hi Jan,

    I think it does and little boxes appear on the screen.

    Regards

    Mike

  8. #8
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reduce the size of an XLS file (Excel XP)

    Hi Jan,

    I have deleted those objects and it has reduced the size by half, it is now only 84 k now... great progress.

    Regards

    Mike

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

    Re: Reduce the size of an XLS file (Excel XP)

    Great that helped.

    Note that you need to repeat that for all worksheets.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reduce the size of an XLS file (Excel XP)

    Thanks Jan,

    Will do.... saved me loads of loading and processing time

    Regards

    Mike

Posting Permissions

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