PDA

View Full Version : Slow Save, and the File Size Kid.



ukdane
03-02-2009, 05:26 AM
I've sent the same workbook, to a number of locations, who save the file on a local network.

The workbook is a shared workbook, with each locations workbook being used by anywhere between 1 and 12 people at any one time.

I've recently noticed that the size of the file has grown quite a bit at some locations, whilst it remains smaller at other locations. This seems to be irrospective of the amount of data that has been input into the workbook.

I've also noticed, that the time it takes for each location to save their workbook has slowed considerably. File sizes vary between 5Mb and 35Mb.
Saving times between 13 seconds, and 1 minute 23 seconds.

The workbook also contains a number of Hyperlinks, to file locations. I've tried removing the links, and it doesn't appear to have had an effect on file size, or sharing time.

Generally, saving the workbook to C drive is a few seconds (but not much) quicker than saving to the network drive.

1) How can I find out why the file sizes are varying so much?

2)Is there someway Excel can evaluate which parts of the worksheet are requiring more memory?

3) How can I reduce the time it takes to save the workbook? (I assume the answer here is reduce the file size)

I can't post the workbook, as it contains sensitive material.
I can tell you that each instance of the workbook contains 16 worksheets. So far only 7 of these worksheets contain data,
and that the "largest" of these worksheets only contains data in the fields from A1:BN400 (or there abouts). There is on average one hyperlink on each line that contains data.

Any information about the what, where, how, why's and so forth about these two fugitives is greatly appreciated.
I need to aprehend them as soon as possible :-)

Thanks

Jan Karel Pieterse
03-02-2009, 06:27 AM
Where does control+end take you on each sheet? If way beyond the actual data, delete rows and columns.

A warning.
I strongly advise against using the shared workbook feature. Excel is no multi-user application.
I have seen nothing but problem reports on this feature. Problems include file corruption, data being lost, changes not saved, ...
If you need more than one user modifying data, don't use Excel.

ukdane
03-02-2009, 06:39 AM
I just took a dummy of a workbook, and CTL+END all the pages, they did exceed their needed use a little, and I deleted the unneeded rows. However this didn't make a difference.

In fact the opposite happened. The file size increased from 8Mb to 12 Mb.

However I then removed the share workbook function. saved the file, then reapplied the function, and reshared it.

The file size then dropped from 12 Mb to just over 1Mb.

I also noticed that I had set the Share Workbook function to keep a 30day change history. I think removing the change history entirely could be dangerous. So far I've reduced it to 7 days, but maybe it should be reduced to 48 hours.

Is the track change history kept "internally", and therefore responsible for the slow saves?

Jan Karel Pieterse
03-02-2009, 08:42 AM
It is kept in a separate worksheet in the workbook if I recall correctly, but since I avoid use of shared workbooks like the bubonic plague I haven't got much experience with them.

ukdane
03-04-2009, 03:05 AM
Oddly, my solution above seems only to have been a temporary solution.
I think I'm going to have to look at the entire structure of the Workbook, and VBA code from start to finish, and look at ways to reduce the overall file size.

So if anyone knows a way that I can check how much memory I'm using by running the various functions and bits of code, I'd be more than happy to know.

Also, if anyone can give me some pointers about how to streamline programs, then please let me know.

Cheers