shared workbook

[Jarryd]

Hi,

I have an Excel workbook that is shared among three users. The file has
bloated to a file size of 15MB. When I unshare it the file size decreases
to 800KB. If I reshare it the file size remains at 800KB. Why does Excel
not clear the bloat automatically? Is it not a good idea to unshare and
then reshare the workbook to shed off the excess, i.e. are you getting rid
of important backgound data and in doing so causing problems later on?

TIA,

Jarryd

[justanormalguy]

Jarryd

I've been pulling my hair out for the past 2 weeks trying to figure out why my shared excel file grows at an abnormal rate, and I finally figured it out. I hope your problem is similar so you can solve it.

I have a Main job schedule worksheet where users enter data, colors, and comments. I have other worksheets (called Scrap, Texture, and others) that are display only where users view specific data from the Main sheet. The Scrap sheet is updated from the Main sheet every time the Scrap sheet tab is selected.

I have a lot of array formulas on the Scrap sheet. These worked fine and did not cause file growth. Two weeks ago my employer asked me to transfer cell colors, font colors, and any comments as well as data from the Main to Scrap sheet for viewing. This can't be done in formulas (unless maybe a VBA custom formula), so I added VBA code to "copy" the colors and comments over for viewing.

The key word here is "copy". Every time my Scrap sheet is activated the colors and comments are transfered from the Main to Scrap sheet. So every time my Scrap sheet is activated (even with no changes) all the font and cell colors are transfered. In a Shared environment this formatting is remembered. The user is not changing a cell but I am. Every time my Scrap sheet is activated it adds about 20-30K to the file size, and this with no changes by the user. If I click back and forth between Main and Scrap 100 times I add about 2.5 Megs to my file size, which would normally be 1.2M.

So, check to see you are programmatically changing colors, formats, borders, or anything else. Post back here if it solves the problem as I have read about a lot of people with this problem but no one provided an actual solution from experience. Usually they just say to clear your unused range, which does nothing for this problem.

Taking it out of Share mode will not harm any data in your file, it just gets rid of any history and tracking that took place.

Bert

[Jarryd]

Hi Bert,

The workbook is used to keep track of deliveries. There are two copies of
the workbook, and both are shared. The one holds current data (last 5
weeks) and the other holds everything else, i.e. 6 years minus the last 5
weeks. The first is called admincur.xls and the other is adminhis.xls. The
users periodically cut data from admincur.xls and paste it to adminhis.xls.
After I posted this question I read about how Excel shares data, i.e.
keeping a history. What I still didn't know is if I could purge it, so I
thank you for your reply letting me know that it is safe to do so.

I imagine that the problem is caused by the periodical cutting and pasting,
which did include all formatting. Although I didn't use VBA I believe it is
the same thing to copy/cut with mouse/keyboard as it is to do so with VBA.
Unfortunately, this can't/won't be helped as it is a system that has been in
place for ages and a change is expected in about three months. We are
getting a new ERP system that will take care of things better and so there
will no longer be a need to maintain disparate data systems, otherwise I
would create a DB in Access for them. I say unfotunately because they will
have to plod along with it for another quarter but at least we know we can
simply unshare/reshare the DB without any ill-effects and clear the bloat.

Thanks again.

Best regards,

Jarryd

"justanormalguy"
<justanormalguy.2a6q7z_1151620201.7289@excelforum-nospam.com> wrote in
message news:justanormalguy.2a6q7z_1151620201.7289@excelforum-nospam.com...
>
> Jarryd
>
> I've been pulling my hair out for the past 2 weeks trying to figure out
> why my shared excel file grows at an abnormal rate, and I finally
> figured it out. I hope your problem is similar so you can solve it.
>
> I have a Main job schedule worksheet where users enter data, colors,
> and comments. I have other worksheets (called Scrap, Texture, and
> others) that are display only where users view specific data from the
> Main sheet. The Scrap sheet is updated from the Main sheet every time
> the Scrap sheet tab is selected.
>
> I have a lot of array formulas on the Scrap sheet. These worked fine
> and did not cause file growth. Two weeks ago my employer asked me to
> transfer cell colors, font colors, and any comments as well as data
> from the Main to Scrap sheet for viewing. This can't be done in
> formulas (unless maybe a VBA custom formula), so I added VBA code to
> "copy" the colors and comments over for viewing.
>
> The key word here is "copy". Every time my Scrap sheet is activated
> the colors and comments are transfered from the Main to Scrap sheet.
> So every time my Scrap sheet is activated (even with no changes) all
> the font and cell colors are transfered. In a Shared environment this
> formatting is remembered. The user is not changing a cell but I am.
> Every time my Scrap sheet is activated it adds about 20-30K to the file
> size, and this with no changes by the user. If I click back and forth
> between Main and Scrap 100 times I add about 2.5 Megs to my file size,
> which would normally be 1.2M.
>
> So, check to see you are programmatically changing colors, formats,
> borders, or anything else. Post back here if it solves the problem as
> I have read about a lot of people with this problem but no one provided
> an actual solution from experience. Usually they just say to clear your
> unused range, which does nothing for this problem.
>
> Taking it out of Share mode will not harm any data in your file, it
> just gets rid of any history and tracking that took place.
>
> Bert
>
>
> --
> justanormalguy
> ------------------------------------------------------------------------
> justanormalguy's Profile:
> http://www.excelforum.com/member.php...o&userid=35919
> View this thread: http://www.excelforum.com/showthread...hreadid=556823
>