+ Reply to Thread
Results 1 to 11 of 11

Excel File Size Reduction Tool

  1. #1
    Rima
    Guest

    Excel File Size Reduction Tool

    When making excel workbooks, I found that excel puts in a lot of extra
    programming, therefore making the size of a workbook go up to 25MB. Which
    causes difficulty when e-mailing the workbook. A solution found is by
    deleting all extra empty cells, yet this is impractical.
    Is there a way to clean it up without having to manually erase all of the
    extra cells.

    Thanks

  2. #2
    Gord Dibben
    Guest

    Re: Excel File Size Reduction Tool

    Rima

    Debra Dalgleish has VBA code to reset the used range on each worksheet in a
    workbook.

    http://www.contextures.on.ca/xlfaqApp.html#Unused


    Gord Dibben MS Excel MVP

    On Mon, 26 Jun 2006 07:23:02 -0700, Rima <[email protected]> wrote:

    >When making excel workbooks, I found that excel puts in a lot of extra
    >programming, therefore making the size of a workbook go up to 25MB. Which
    >causes difficulty when e-mailing the workbook. A solution found is by
    >deleting all extra empty cells, yet this is impractical.
    >Is there a way to clean it up without having to manually erase all of the
    >extra cells.
    >
    >Thanks



  3. #3
    Rima
    Guest

    Re: Excel File Size Reduction Tool

    Thanks Gord,
    I am able to do it, yet I was wondering if there is an actual tool that does
    that. When I searched on Google, I found that Sobolsoft has a tool. Yet due
    to the fact that all of the trial versions need you to actually sign up and
    pay to use it, I am reluctant to buy it without trying it.
    I want a tool that our users are able to use so they can fix this process
    themselves.

    Rima

    "Gord Dibben" wrote:

    > Rima
    >
    > Debra Dalgleish has VBA code to reset the used range on each worksheet in a
    > workbook.
    >
    > http://www.contextures.on.ca/xlfaqApp.html#Unused
    >
    >
    > Gord Dibben MS Excel MVP
    >
    > On Mon, 26 Jun 2006 07:23:02 -0700, Rima <[email protected]> wrote:
    >
    > >When making excel workbooks, I found that excel puts in a lot of extra
    > >programming, therefore making the size of a workbook go up to 25MB. Which
    > >causes difficulty when e-mailing the workbook. A solution found is by
    > >deleting all extra empty cells, yet this is impractical.
    > >Is there a way to clean it up without having to manually erase all of the
    > >extra cells.
    > >
    > >Thanks

    >
    >


  4. #4
    Gord Dibben
    Guest

    Re: Excel File Size Reduction Tool


    I have no knowledge of any third-party tool such as you describe.

    However, I did download the Sobolsoft Excel file reducer setup.exe and ran it.

    If you just keep clicking No to the "do you want to register etc." you can get
    the file reducer to work.

    It dropped the sizes on a couple of workbooks I had set up with artificially
    large used ranges.

    Sobolsoft deleted all the unused rows and columns on sheets, which Debra's code
    does.


    Gord Dibben MS Excel MVP


    On Mon, 26 Jun 2006 09:10:02 -0700, Rima <[email protected]> wrote:

    >Thanks Gord,
    >I am able to do it, yet I was wondering if there is an actual tool that does
    >that. When I searched on Google, I found that Sobolsoft has a tool. Yet due
    >to the fact that all of the trial versions need you to actually sign up and
    >pay to use it, I am reluctant to buy it without trying it.
    >I want a tool that our users are able to use so they can fix this process
    >themselves.
    >
    >Rima
    >
    >"Gord Dibben" wrote:
    >
    >> Rima
    >>
    >> Debra Dalgleish has VBA code to reset the used range on each worksheet in a
    >> workbook.
    >>
    >> http://www.contextures.on.ca/xlfaqApp.html#Unused
    >>
    >>
    >> Gord Dibben MS Excel MVP
    >>
    >> On Mon, 26 Jun 2006 07:23:02 -0700, Rima <[email protected]> wrote:
    >>
    >> >When making excel workbooks, I found that excel puts in a lot of extra
    >> >programming, therefore making the size of a workbook go up to 25MB. Which
    >> >causes difficulty when e-mailing the workbook. A solution found is by
    >> >deleting all extra empty cells, yet this is impractical.
    >> >Is there a way to clean it up without having to manually erase all of the
    >> >extra cells.
    >> >
    >> >Thanks

    >>
    >>



  5. #5
    Rima
    Guest

    Re: Excel File Size Reduction Tool

    Thanks Gord,

    I did try it and the tool reduced the size of the file temendously. I am
    just curious, In a workbook where would the code be placed? Is there a
    certain page which would affect all of them? or would it have to be done page
    for page.

    Rima

    "Gord Dibben" wrote:

    >
    > I have no knowledge of any third-party tool such as you describe.
    >
    > However, I did download the Sobolsoft Excel file reducer setup.exe and ran it.
    >
    > If you just keep clicking No to the "do you want to register etc." you can get
    > the file reducer to work.
    >
    > It dropped the sizes on a couple of workbooks I had set up with artificially
    > large used ranges.
    >
    > Sobolsoft deleted all the unused rows and columns on sheets, which Debra's code
    > does.
    >
    >
    > Gord Dibben MS Excel MVP
    >
    >
    > On Mon, 26 Jun 2006 09:10:02 -0700, Rima <[email protected]> wrote:
    >
    > >Thanks Gord,
    > >I am able to do it, yet I was wondering if there is an actual tool that does
    > >that. When I searched on Google, I found that Sobolsoft has a tool. Yet due
    > >to the fact that all of the trial versions need you to actually sign up and
    > >pay to use it, I am reluctant to buy it without trying it.
    > >I want a tool that our users are able to use so they can fix this process
    > >themselves.
    > >
    > >Rima
    > >
    > >"Gord Dibben" wrote:
    > >
    > >> Rima
    > >>
    > >> Debra Dalgleish has VBA code to reset the used range on each worksheet in a
    > >> workbook.
    > >>
    > >> http://www.contextures.on.ca/xlfaqApp.html#Unused
    > >>
    > >>
    > >> Gord Dibben MS Excel MVP
    > >>
    > >> On Mon, 26 Jun 2006 07:23:02 -0700, Rima <[email protected]> wrote:
    > >>
    > >> >When making excel workbooks, I found that excel puts in a lot of extra
    > >> >programming, therefore making the size of a workbook go up to 25MB. Which
    > >> >causes difficulty when e-mailing the workbook. A solution found is by
    > >> >deleting all extra empty cells, yet this is impractical.
    > >> >Is there a way to clean it up without having to manually erase all of the
    > >> >extra cells.
    > >> >
    > >> >Thanks
    > >>
    > >>

    >
    >


  6. #6
    Gord Dibben
    Guest

    Re: Excel File Size Reduction Tool

    Rima

    The code from Debra's site would be copy/pasted to a General Module in the
    workbook or your Personal.xls if you want to have the macro available for all
    open workbooks.

    First...create a backup copy of your original workbook.

    To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

    Hit CRTL + R to open Project Explorer.

    Find your workbook/project by name and select it.

    Right-click and Insert>Module. Paste the code in there. Save the
    workbook and hit ALT + Q to return to your workbook.

    Run the macro by going to Tool>Macro>Macros.

    You can also assign this macro to a button or a shortcut key combo.

    To place a button on a Toolbar...Tools>Customize>Commands>Macros.

    Drag the smiley-face button to your Toolbar.

    Right-click and "Assign Macro". Select TOGGLECENTERACROSS and OK

    Note: when you right-click you will also see "Change Button Image". There are
    a bunch of button faces to pick from if you don't like smiley.


    Gord

    On Mon, 26 Jun 2006 12:39:02 -0700, Rima <[email protected]> wrote:

    >Thanks Gord,
    >
    >I did try it and the tool reduced the size of the file temendously. I am
    >just curious, In a workbook where would the code be placed? Is there a
    >certain page which would affect all of them? or would it have to be done page
    >for page.
    >
    >Rima
    >
    >"Gord Dibben" wrote:
    >
    >>
    >> I have no knowledge of any third-party tool such as you describe.
    >>
    >> However, I did download the Sobolsoft Excel file reducer setup.exe and ran it.
    >>
    >> If you just keep clicking No to the "do you want to register etc." you can get
    >> the file reducer to work.
    >>
    >> It dropped the sizes on a couple of workbooks I had set up with artificially
    >> large used ranges.
    >>
    >> Sobolsoft deleted all the unused rows and columns on sheets, which Debra's code
    >> does.
    >>
    >>
    >> Gord Dibben MS Excel MVP
    >>
    >>
    >> On Mon, 26 Jun 2006 09:10:02 -0700, Rima <[email protected]> wrote:
    >>
    >> >Thanks Gord,
    >> >I am able to do it, yet I was wondering if there is an actual tool that does
    >> >that. When I searched on Google, I found that Sobolsoft has a tool. Yet due
    >> >to the fact that all of the trial versions need you to actually sign up and
    >> >pay to use it, I am reluctant to buy it without trying it.
    >> >I want a tool that our users are able to use so they can fix this process
    >> >themselves.
    >> >
    >> >Rima
    >> >
    >> >"Gord Dibben" wrote:
    >> >
    >> >> Rima
    >> >>
    >> >> Debra Dalgleish has VBA code to reset the used range on each worksheet in a
    >> >> workbook.
    >> >>
    >> >> http://www.contextures.on.ca/xlfaqApp.html#Unused
    >> >>
    >> >>
    >> >> Gord Dibben MS Excel MVP
    >> >>
    >> >> On Mon, 26 Jun 2006 07:23:02 -0700, Rima <[email protected]> wrote:
    >> >>
    >> >> >When making excel workbooks, I found that excel puts in a lot of extra
    >> >> >programming, therefore making the size of a workbook go up to 25MB. Which
    >> >> >causes difficulty when e-mailing the workbook. A solution found is by
    >> >> >deleting all extra empty cells, yet this is impractical.
    >> >> >Is there a way to clean it up without having to manually erase all of the
    >> >> >extra cells.
    >> >> >
    >> >> >Thanks
    >> >>
    >> >>

    >>
    >>


    Gord Dibben MS Excel MVP

  7. #7
    Rima
    Guest

    Re: Excel File Size Reduction Tool

    Very cool Gord!
    One last thing. I've noticed that both the tool and the programming changed
    on the workbook. Is there a workaround or should we just go in and manually
    fix it?
    Rima

    "Gord Dibben" wrote:

    > Rima
    >
    > The code from Debra's site would be copy/pasted to a General Module in the
    > workbook or your Personal.xls if you want to have the macro available for all
    > open workbooks.
    >
    > First...create a backup copy of your original workbook.
    >
    > To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
    >
    > Hit CRTL + R to open Project Explorer.
    >
    > Find your workbook/project by name and select it.
    >
    > Right-click and Insert>Module. Paste the code in there. Save the
    > workbook and hit ALT + Q to return to your workbook.
    >
    > Run the macro by going to Tool>Macro>Macros.
    >
    > You can also assign this macro to a button or a shortcut key combo.
    >
    > To place a button on a Toolbar...Tools>Customize>Commands>Macros.
    >
    > Drag the smiley-face button to your Toolbar.
    >
    > Right-click and "Assign Macro". Select TOGGLECENTERACROSS and OK
    >
    > Note: when you right-click you will also see "Change Button Image". There are
    > a bunch of button faces to pick from if you don't like smiley.
    >
    >
    > Gord
    >
    > On Mon, 26 Jun 2006 12:39:02 -0700, Rima <[email protected]> wrote:
    >
    > >Thanks Gord,
    > >
    > >I did try it and the tool reduced the size of the file temendously. I am
    > >just curious, In a workbook where would the code be placed? Is there a
    > >certain page which would affect all of them? or would it have to be done page
    > >for page.
    > >
    > >Rima
    > >
    > >"Gord Dibben" wrote:
    > >
    > >>
    > >> I have no knowledge of any third-party tool such as you describe.
    > >>
    > >> However, I did download the Sobolsoft Excel file reducer setup.exe and ran it.
    > >>
    > >> If you just keep clicking No to the "do you want to register etc." you can get
    > >> the file reducer to work.
    > >>
    > >> It dropped the sizes on a couple of workbooks I had set up with artificially
    > >> large used ranges.
    > >>
    > >> Sobolsoft deleted all the unused rows and columns on sheets, which Debra's code
    > >> does.
    > >>
    > >>
    > >> Gord Dibben MS Excel MVP
    > >>
    > >>
    > >> On Mon, 26 Jun 2006 09:10:02 -0700, Rima <[email protected]> wrote:
    > >>
    > >> >Thanks Gord,
    > >> >I am able to do it, yet I was wondering if there is an actual tool that does
    > >> >that. When I searched on Google, I found that Sobolsoft has a tool. Yet due
    > >> >to the fact that all of the trial versions need you to actually sign up and
    > >> >pay to use it, I am reluctant to buy it without trying it.
    > >> >I want a tool that our users are able to use so they can fix this process
    > >> >themselves.
    > >> >
    > >> >Rima
    > >> >
    > >> >"Gord Dibben" wrote:
    > >> >
    > >> >> Rima
    > >> >>
    > >> >> Debra Dalgleish has VBA code to reset the used range on each worksheet in a
    > >> >> workbook.
    > >> >>
    > >> >> http://www.contextures.on.ca/xlfaqApp.html#Unused
    > >> >>
    > >> >>
    > >> >> Gord Dibben MS Excel MVP
    > >> >>
    > >> >> On Mon, 26 Jun 2006 07:23:02 -0700, Rima <[email protected]> wrote:
    > >> >>
    > >> >> >When making excel workbooks, I found that excel puts in a lot of extra
    > >> >> >programming, therefore making the size of a workbook go up to 25MB. Which
    > >> >> >causes difficulty when e-mailing the workbook. A solution found is by
    > >> >> >deleting all extra empty cells, yet this is impractical.
    > >> >> >Is there a way to clean it up without having to manually erase all of the
    > >> >> >extra cells.
    > >> >> >
    > >> >> >Thanks
    > >> >>
    > >> >>
    > >>
    > >>

    >
    > Gord Dibben MS Excel MVP
    >


  8. #8
    Gord Dibben
    Guest

    Re: Excel File Size Reduction Tool

    Rima

    Don't understand your description so the fix is not apparent to me.

    Please, what is "the tool and the programming changed on the workbook"

    Also note in my instructions I used a canned response which had the
    TOGGLECENTERACROSS macro name.

    I should have used Debra's macro name.


    Gord

    On Tue, 27 Jun 2006 10:00:02 -0700, Rima <[email protected]> wrote:

    >Very cool Gord!
    >One last thing. I've noticed that both the tool and the programming changed
    >on the workbook. Is there a workaround or should we just go in and manually
    >fix it?
    >Rima
    >
    >"Gord Dibben" wrote:
    >
    >> Rima
    >>
    >> The code from Debra's site would be copy/pasted to a General Module in the
    >> workbook or your Personal.xls if you want to have the macro available for all
    >> open workbooks.
    >>
    >> First...create a backup copy of your original workbook.
    >>
    >> To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
    >>
    >> Hit CRTL + R to open Project Explorer.
    >>
    >> Find your workbook/project by name and select it.
    >>
    >> Right-click and Insert>Module. Paste the code in there. Save the
    >> workbook and hit ALT + Q to return to your workbook.
    >>
    >> Run the macro by going to Tool>Macro>Macros.
    >>
    >> You can also assign this macro to a button or a shortcut key combo.
    >>
    >> To place a button on a Toolbar...Tools>Customize>Commands>Macros.
    >>
    >> Drag the smiley-face button to your Toolbar.
    >>
    >> Right-click and "Assign Macro". Select TOGGLECENTERACROSS and OK
    >>
    >> Note: when you right-click you will also see "Change Button Image". There are
    >> a bunch of button faces to pick from if you don't like smiley.
    >>
    >>
    >> Gord
    >>
    >> On Mon, 26 Jun 2006 12:39:02 -0700, Rima <[email protected]> wrote:
    >>
    >> >Thanks Gord,
    >> >
    >> >I did try it and the tool reduced the size of the file temendously. I am
    >> >just curious, In a workbook where would the code be placed? Is there a
    >> >certain page which would affect all of them? or would it have to be done page
    >> >for page.
    >> >
    >> >Rima
    >> >
    >> >"Gord Dibben" wrote:
    >> >
    >> >>
    >> >> I have no knowledge of any third-party tool such as you describe.
    >> >>
    >> >> However, I did download the Sobolsoft Excel file reducer setup.exe and ran it.
    >> >>
    >> >> If you just keep clicking No to the "do you want to register etc." you can get
    >> >> the file reducer to work.
    >> >>
    >> >> It dropped the sizes on a couple of workbooks I had set up with artificially
    >> >> large used ranges.
    >> >>
    >> >> Sobolsoft deleted all the unused rows and columns on sheets, which Debra's code
    >> >> does.
    >> >>
    >> >>
    >> >> Gord Dibben MS Excel MVP
    >> >>
    >> >>
    >> >> On Mon, 26 Jun 2006 09:10:02 -0700, Rima <[email protected]> wrote:
    >> >>
    >> >> >Thanks Gord,
    >> >> >I am able to do it, yet I was wondering if there is an actual tool that does
    >> >> >that. When I searched on Google, I found that Sobolsoft has a tool. Yet due
    >> >> >to the fact that all of the trial versions need you to actually sign up and
    >> >> >pay to use it, I am reluctant to buy it without trying it.
    >> >> >I want a tool that our users are able to use so they can fix this process
    >> >> >themselves.
    >> >> >
    >> >> >Rima
    >> >> >
    >> >> >"Gord Dibben" wrote:
    >> >> >
    >> >> >> Rima
    >> >> >>
    >> >> >> Debra Dalgleish has VBA code to reset the used range on each worksheet in a
    >> >> >> workbook.
    >> >> >>
    >> >> >> http://www.contextures.on.ca/xlfaqApp.html#Unused
    >> >> >>
    >> >> >>
    >> >> >> Gord Dibben MS Excel MVP
    >> >> >>
    >> >> >> On Mon, 26 Jun 2006 07:23:02 -0700, Rima <[email protected]> wrote:
    >> >> >>
    >> >> >> >When making excel workbooks, I found that excel puts in a lot of extra
    >> >> >> >programming, therefore making the size of a workbook go up to 25MB. Which
    >> >> >> >causes difficulty when e-mailing the workbook. A solution found is by
    >> >> >> >deleting all extra empty cells, yet this is impractical.
    >> >> >> >Is there a way to clean it up without having to manually erase all of the
    >> >> >> >extra cells.
    >> >> >> >
    >> >> >> >Thanks
    >> >> >>
    >> >> >>
    >> >>
    >> >>

    >>
    >> Gord Dibben MS Excel MVP
    >>


    Gord Dibben MS Excel MVP

  9. #9
    Rima
    Guest

    Re: Excel File Size Reduction Tool

    Sorry Gord,

    my fault for not making myself clear.
    When I ran the sobolsoft tool, and the file size was reduced, the formatting
    on the sheets were changed.
    As well, when I used the programming that Debra had, the formatting was
    changed as well, the same way as the tool did on the worksheets.
    Is there a work around or will this of course change some of the formatting
    of the worksheets (such as: if the cells all the way across and down on the
    worksheet where filled in grey, when the macros were run, the grey turned
    into white under the charts that were there)

    Rima

    "Gord Dibben" wrote:

    > Rima
    >
    > Don't understand your description so the fix is not apparent to me.
    >
    > Please, what is "the tool and the programming changed on the workbook"
    >
    > Also note in my instructions I used a canned response which had the
    > TOGGLECENTERACROSS macro name.
    >
    > I should have used Debra's macro name.
    >
    >
    > Gord
    >
    > On Tue, 27 Jun 2006 10:00:02 -0700, Rima <[email protected]> wrote:
    >
    > >Very cool Gord!
    > >One last thing. I've noticed that both the tool and the programming changed
    > >on the workbook. Is there a workaround or should we just go in and manually
    > >fix it?
    > >Rima
    > >
    > >"Gord Dibben" wrote:
    > >
    > >> Rima
    > >>
    > >> The code from Debra's site would be copy/pasted to a General Module in the
    > >> workbook or your Personal.xls if you want to have the macro available for all
    > >> open workbooks.
    > >>
    > >> First...create a backup copy of your original workbook.
    > >>
    > >> To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
    > >>
    > >> Hit CRTL + R to open Project Explorer.
    > >>
    > >> Find your workbook/project by name and select it.
    > >>
    > >> Right-click and Insert>Module. Paste the code in there. Save the
    > >> workbook and hit ALT + Q to return to your workbook.
    > >>
    > >> Run the macro by going to Tool>Macro>Macros.
    > >>
    > >> You can also assign this macro to a button or a shortcut key combo.
    > >>
    > >> To place a button on a Toolbar...Tools>Customize>Commands>Macros.
    > >>
    > >> Drag the smiley-face button to your Toolbar.
    > >>
    > >> Right-click and "Assign Macro". Select TOGGLECENTERACROSS and OK
    > >>
    > >> Note: when you right-click you will also see "Change Button Image". There are
    > >> a bunch of button faces to pick from if you don't like smiley.
    > >>
    > >>
    > >> Gord
    > >>
    > >> On Mon, 26 Jun 2006 12:39:02 -0700, Rima <[email protected]> wrote:
    > >>
    > >> >Thanks Gord,
    > >> >
    > >> >I did try it and the tool reduced the size of the file temendously. I am
    > >> >just curious, In a workbook where would the code be placed? Is there a
    > >> >certain page which would affect all of them? or would it have to be done page
    > >> >for page.
    > >> >
    > >> >Rima
    > >> >
    > >> >"Gord Dibben" wrote:
    > >> >
    > >> >>
    > >> >> I have no knowledge of any third-party tool such as you describe.
    > >> >>
    > >> >> However, I did download the Sobolsoft Excel file reducer setup.exe and ran it.
    > >> >>
    > >> >> If you just keep clicking No to the "do you want to register etc." you can get
    > >> >> the file reducer to work.
    > >> >>
    > >> >> It dropped the sizes on a couple of workbooks I had set up with artificially
    > >> >> large used ranges.
    > >> >>
    > >> >> Sobolsoft deleted all the unused rows and columns on sheets, which Debra's code
    > >> >> does.
    > >> >>
    > >> >>
    > >> >> Gord Dibben MS Excel MVP
    > >> >>
    > >> >>
    > >> >> On Mon, 26 Jun 2006 09:10:02 -0700, Rima <[email protected]> wrote:
    > >> >>
    > >> >> >Thanks Gord,
    > >> >> >I am able to do it, yet I was wondering if there is an actual tool that does
    > >> >> >that. When I searched on Google, I found that Sobolsoft has a tool. Yet due
    > >> >> >to the fact that all of the trial versions need you to actually sign up and
    > >> >> >pay to use it, I am reluctant to buy it without trying it.
    > >> >> >I want a tool that our users are able to use so they can fix this process
    > >> >> >themselves.
    > >> >> >
    > >> >> >Rima
    > >> >> >
    > >> >> >"Gord Dibben" wrote:
    > >> >> >
    > >> >> >> Rima
    > >> >> >>
    > >> >> >> Debra Dalgleish has VBA code to reset the used range on each worksheet in a
    > >> >> >> workbook.
    > >> >> >>
    > >> >> >> http://www.contextures.on.ca/xlfaqApp.html#Unused
    > >> >> >>
    > >> >> >>
    > >> >> >> Gord Dibben MS Excel MVP
    > >> >> >>
    > >> >> >> On Mon, 26 Jun 2006 07:23:02 -0700, Rima <[email protected]> wrote:
    > >> >> >>
    > >> >> >> >When making excel workbooks, I found that excel puts in a lot of extra
    > >> >> >> >programming, therefore making the size of a workbook go up to 25MB. Which
    > >> >> >> >causes difficulty when e-mailing the workbook. A solution found is by
    > >> >> >> >deleting all extra empty cells, yet this is impractical.
    > >> >> >> >Is there a way to clean it up without having to manually erase all of the
    > >> >> >> >extra cells.
    > >> >> >> >
    > >> >> >> >Thanks
    > >> >> >>
    > >> >> >>
    > >> >>
    > >> >>
    > >>
    > >> Gord Dibben MS Excel MVP
    > >>

    >
    > Gord Dibben MS Excel MVP
    >


  10. #10
    Gord Dibben
    Guest

    Re: Excel File Size Reduction Tool

    Ok.

    See what you mean.

    By deleting the unused rows, the formatting is lost and your gray rows/columns
    are back to default.

    That's the price you pay for cleaning up the unused range.

    Debra warns about the loss of formatting at her website.

    Your original need was to reduce the size of the workbook.....remember?

    If you want the formatting, you have the workbook size.


    Gord

    On Tue, 27 Jun 2006 11:41:02 -0700, Rima <[email protected]> wrote:

    >Sorry Gord,
    >
    >my fault for not making myself clear.
    >When I ran the sobolsoft tool, and the file size was reduced, the formatting
    >on the sheets were changed.
    >As well, when I used the programming that Debra had, the formatting was
    >changed as well, the same way as the tool did on the worksheets.
    >Is there a work around or will this of course change some of the formatting
    >of the worksheets (such as: if the cells all the way across and down on the
    >worksheet where filled in grey, when the macros were run, the grey turned
    >into white under the charts that were there)
    >
    >Rima
    >
    >"Gord Dibben" wrote:
    >
    >> Rima
    >>
    >> Don't understand your description so the fix is not apparent to me.
    >>
    >> Please, what is "the tool and the programming changed on the workbook"
    >>
    >> Also note in my instructions I used a canned response which had the
    >> TOGGLECENTERACROSS macro name.
    >>
    >> I should have used Debra's macro name.
    >>
    >>
    >> Gord
    >>
    >> On Tue, 27 Jun 2006 10:00:02 -0700, Rima <[email protected]> wrote:
    >>
    >> >Very cool Gord!
    >> >One last thing. I've noticed that both the tool and the programming changed
    >> >on the workbook. Is there a workaround or should we just go in and manually
    >> >fix it?
    >> >Rima
    >> >
    >> >"Gord Dibben" wrote:
    >> >
    >> >> Rima
    >> >>
    >> >> The code from Debra's site would be copy/pasted to a General Module in the
    >> >> workbook or your Personal.xls if you want to have the macro available for all
    >> >> open workbooks.
    >> >>
    >> >> First...create a backup copy of your original workbook.
    >> >>
    >> >> To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
    >> >>
    >> >> Hit CRTL + R to open Project Explorer.
    >> >>
    >> >> Find your workbook/project by name and select it.
    >> >>
    >> >> Right-click and Insert>Module. Paste the code in there. Save the
    >> >> workbook and hit ALT + Q to return to your workbook.
    >> >>
    >> >> Run the macro by going to Tool>Macro>Macros.
    >> >>
    >> >> You can also assign this macro to a button or a shortcut key combo.
    >> >>
    >> >> To place a button on a Toolbar...Tools>Customize>Commands>Macros.
    >> >>
    >> >> Drag the smiley-face button to your Toolbar.
    >> >>
    >> >> Right-click and "Assign Macro". Select TOGGLECENTERACROSS and OK
    >> >>
    >> >> Note: when you right-click you will also see "Change Button Image". There are
    >> >> a bunch of button faces to pick from if you don't like smiley.
    >> >>
    >> >>
    >> >> Gord
    >> >>
    >> >> On Mon, 26 Jun 2006 12:39:02 -0700, Rima <[email protected]> wrote:
    >> >>
    >> >> >Thanks Gord,
    >> >> >
    >> >> >I did try it and the tool reduced the size of the file temendously. I am
    >> >> >just curious, In a workbook where would the code be placed? Is there a
    >> >> >certain page which would affect all of them? or would it have to be done page
    >> >> >for page.
    >> >> >
    >> >> >Rima
    >> >> >
    >> >> >"Gord Dibben" wrote:
    >> >> >
    >> >> >>
    >> >> >> I have no knowledge of any third-party tool such as you describe.
    >> >> >>
    >> >> >> However, I did download the Sobolsoft Excel file reducer setup.exe and ran it.
    >> >> >>
    >> >> >> If you just keep clicking No to the "do you want to register etc." you can get
    >> >> >> the file reducer to work.
    >> >> >>
    >> >> >> It dropped the sizes on a couple of workbooks I had set up with artificially
    >> >> >> large used ranges.
    >> >> >>
    >> >> >> Sobolsoft deleted all the unused rows and columns on sheets, which Debra's code
    >> >> >> does.
    >> >> >>
    >> >> >>
    >> >> >> Gord Dibben MS Excel MVP
    >> >> >>
    >> >> >>
    >> >> >> On Mon, 26 Jun 2006 09:10:02 -0700, Rima <[email protected]> wrote:
    >> >> >>
    >> >> >> >Thanks Gord,
    >> >> >> >I am able to do it, yet I was wondering if there is an actual tool that does
    >> >> >> >that. When I searched on Google, I found that Sobolsoft has a tool. Yet due
    >> >> >> >to the fact that all of the trial versions need you to actually sign up and
    >> >> >> >pay to use it, I am reluctant to buy it without trying it.
    >> >> >> >I want a tool that our users are able to use so they can fix this process
    >> >> >> >themselves.
    >> >> >> >
    >> >> >> >Rima
    >> >> >> >
    >> >> >> >"Gord Dibben" wrote:
    >> >> >> >
    >> >> >> >> Rima
    >> >> >> >>
    >> >> >> >> Debra Dalgleish has VBA code to reset the used range on each worksheet in a
    >> >> >> >> workbook.
    >> >> >> >>
    >> >> >> >> http://www.contextures.on.ca/xlfaqApp.html#Unused
    >> >> >> >>
    >> >> >> >>
    >> >> >> >> Gord Dibben MS Excel MVP
    >> >> >> >>
    >> >> >> >> On Mon, 26 Jun 2006 07:23:02 -0700, Rima <[email protected]> wrote:
    >> >> >> >>
    >> >> >> >> >When making excel workbooks, I found that excel puts in a lot of extra
    >> >> >> >> >programming, therefore making the size of a workbook go up to 25MB. Which
    >> >> >> >> >causes difficulty when e-mailing the workbook. A solution found is by
    >> >> >> >> >deleting all extra empty cells, yet this is impractical.
    >> >> >> >> >Is there a way to clean it up without having to manually erase all of the
    >> >> >> >> >extra cells.
    >> >> >> >> >
    >> >> >> >> >Thanks
    >> >> >> >>
    >> >> >> >>
    >> >> >>
    >> >> >>
    >> >>
    >> >> Gord Dibben MS Excel MVP
    >> >>

    >>
    >> Gord Dibben MS Excel MVP
    >>



  11. #11
    Rima
    Guest

    Re: Excel File Size Reduction Tool

    Got it!

    Thank you very much for your help Gord! You have been very helpful!!!
    Truely appreciated.

    Rima

    "Gord Dibben" wrote:

    > Ok.
    >
    > See what you mean.
    >
    > By deleting the unused rows, the formatting is lost and your gray rows/columns
    > are back to default.
    >
    > That's the price you pay for cleaning up the unused range.
    >
    > Debra warns about the loss of formatting at her website.
    >
    > Your original need was to reduce the size of the workbook.....remember?
    >
    > If you want the formatting, you have the workbook size.
    >
    >
    > Gord
    >
    > On Tue, 27 Jun 2006 11:41:02 -0700, Rima <[email protected]> wrote:
    >
    > >Sorry Gord,
    > >
    > >my fault for not making myself clear.
    > >When I ran the sobolsoft tool, and the file size was reduced, the formatting
    > >on the sheets were changed.
    > >As well, when I used the programming that Debra had, the formatting was
    > >changed as well, the same way as the tool did on the worksheets.
    > >Is there a work around or will this of course change some of the formatting
    > >of the worksheets (such as: if the cells all the way across and down on the
    > >worksheet where filled in grey, when the macros were run, the grey turned
    > >into white under the charts that were there)
    > >
    > >Rima
    > >
    > >"Gord Dibben" wrote:
    > >
    > >> Rima
    > >>
    > >> Don't understand your description so the fix is not apparent to me.
    > >>
    > >> Please, what is "the tool and the programming changed on the workbook"
    > >>
    > >> Also note in my instructions I used a canned response which had the
    > >> TOGGLECENTERACROSS macro name.
    > >>
    > >> I should have used Debra's macro name.
    > >>
    > >>
    > >> Gord
    > >>
    > >> On Tue, 27 Jun 2006 10:00:02 -0700, Rima <[email protected]> wrote:
    > >>
    > >> >Very cool Gord!
    > >> >One last thing. I've noticed that both the tool and the programming changed
    > >> >on the workbook. Is there a workaround or should we just go in and manually
    > >> >fix it?
    > >> >Rima
    > >> >
    > >> >"Gord Dibben" wrote:
    > >> >
    > >> >> Rima
    > >> >>
    > >> >> The code from Debra's site would be copy/pasted to a General Module in the
    > >> >> workbook or your Personal.xls if you want to have the macro available for all
    > >> >> open workbooks.
    > >> >>
    > >> >> First...create a backup copy of your original workbook.
    > >> >>
    > >> >> To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
    > >> >>
    > >> >> Hit CRTL + R to open Project Explorer.
    > >> >>
    > >> >> Find your workbook/project by name and select it.
    > >> >>
    > >> >> Right-click and Insert>Module. Paste the code in there. Save the
    > >> >> workbook and hit ALT + Q to return to your workbook.
    > >> >>
    > >> >> Run the macro by going to Tool>Macro>Macros.
    > >> >>
    > >> >> You can also assign this macro to a button or a shortcut key combo.
    > >> >>
    > >> >> To place a button on a Toolbar...Tools>Customize>Commands>Macros.
    > >> >>
    > >> >> Drag the smiley-face button to your Toolbar.
    > >> >>
    > >> >> Right-click and "Assign Macro". Select TOGGLECENTERACROSS and OK
    > >> >>
    > >> >> Note: when you right-click you will also see "Change Button Image". There are
    > >> >> a bunch of button faces to pick from if you don't like smiley.
    > >> >>
    > >> >>
    > >> >> Gord
    > >> >>
    > >> >> On Mon, 26 Jun 2006 12:39:02 -0700, Rima <[email protected]> wrote:
    > >> >>
    > >> >> >Thanks Gord,
    > >> >> >
    > >> >> >I did try it and the tool reduced the size of the file temendously. I am
    > >> >> >just curious, In a workbook where would the code be placed? Is there a
    > >> >> >certain page which would affect all of them? or would it have to be done page
    > >> >> >for page.
    > >> >> >
    > >> >> >Rima
    > >> >> >
    > >> >> >"Gord Dibben" wrote:
    > >> >> >
    > >> >> >>
    > >> >> >> I have no knowledge of any third-party tool such as you describe.
    > >> >> >>
    > >> >> >> However, I did download the Sobolsoft Excel file reducer setup.exe and ran it.
    > >> >> >>
    > >> >> >> If you just keep clicking No to the "do you want to register etc." you can get
    > >> >> >> the file reducer to work.
    > >> >> >>
    > >> >> >> It dropped the sizes on a couple of workbooks I had set up with artificially
    > >> >> >> large used ranges.
    > >> >> >>
    > >> >> >> Sobolsoft deleted all the unused rows and columns on sheets, which Debra's code
    > >> >> >> does.
    > >> >> >>
    > >> >> >>
    > >> >> >> Gord Dibben MS Excel MVP
    > >> >> >>
    > >> >> >>
    > >> >> >> On Mon, 26 Jun 2006 09:10:02 -0700, Rima <[email protected]> wrote:
    > >> >> >>
    > >> >> >> >Thanks Gord,
    > >> >> >> >I am able to do it, yet I was wondering if there is an actual tool that does
    > >> >> >> >that. When I searched on Google, I found that Sobolsoft has a tool. Yet due
    > >> >> >> >to the fact that all of the trial versions need you to actually sign up and
    > >> >> >> >pay to use it, I am reluctant to buy it without trying it.
    > >> >> >> >I want a tool that our users are able to use so they can fix this process
    > >> >> >> >themselves.
    > >> >> >> >
    > >> >> >> >Rima
    > >> >> >> >
    > >> >> >> >"Gord Dibben" wrote:
    > >> >> >> >
    > >> >> >> >> Rima
    > >> >> >> >>
    > >> >> >> >> Debra Dalgleish has VBA code to reset the used range on each worksheet in a
    > >> >> >> >> workbook.
    > >> >> >> >>
    > >> >> >> >> http://www.contextures.on.ca/xlfaqApp.html#Unused
    > >> >> >> >>
    > >> >> >> >>
    > >> >> >> >> Gord Dibben MS Excel MVP
    > >> >> >> >>
    > >> >> >> >> On Mon, 26 Jun 2006 07:23:02 -0700, Rima <[email protected]> wrote:
    > >> >> >> >>
    > >> >> >> >> >When making excel workbooks, I found that excel puts in a lot of extra
    > >> >> >> >> >programming, therefore making the size of a workbook go up to 25MB. Which
    > >> >> >> >> >causes difficulty when e-mailing the workbook. A solution found is by
    > >> >> >> >> >deleting all extra empty cells, yet this is impractical.
    > >> >> >> >> >Is there a way to clean it up without having to manually erase all of the
    > >> >> >> >> >extra cells.
    > >> >> >> >> >
    > >> >> >> >> >Thanks
    > >> >> >> >>
    > >> >> >> >>
    > >> >> >>
    > >> >> >>
    > >> >>
    > >> >> Gord Dibben MS Excel MVP
    > >> >>
    > >>
    > >> Gord Dibben MS Excel MVP
    > >>

    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1