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 ou
why my shared excel file grows at an abnormal rate, and I finall
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, an
others) that are display only where users view specific data from th
Main sheet. The Scrap sheet is updated from the Main sheet every tim
the Scrap sheet tab is selected.

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

The key word here is "copy". Every time my Scrap sheet is activate
the colors and comments are transfered from the Main to Scrap sheet.
So every time my Scrap sheet is activated (even with no changes) al
the font and cell colors are transfered. In a Shared environment thi
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 fil
size, and this with no changes by the user. If I click back and fort
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 a
I have read about a lot of people with this problem but no one provide
an actual solution from experience. Usually they just say to clear you
unused range, which does nothing for this problem.

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

Ber

 

[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"