• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Large Excel File does not save (Not Responding)...

R

raviarao

New Member
I have an Excel 2003 application package (all developed by me) which is inclusive of large files with links and formulae.

I have built all sorts of small tools to assist me in my business and needless to say our entire operations run on Excel ( and quite well i must say!)!


But one large file (which is our material Inward and Outward Register-5000 rows, 25-30 columns-simple VLOOKUP and IF formulas, and a few DSUM's) has recently started to consistently hang while we try to save it.By the way all the files are shared and every day I remove the sharing and put it back on so that the file size is restored to normal.Usually even 50MB file size never creates any problem, but now it is...

Normal file size when not shared is 30MB and it grows to even 1GB at times when other people work on the file and repeatedly save from other machines, but the not responding behavior is not directly related-since it hangs even at the 30MB size!

All machines are Core2 Duo with atleast 2GB RAM.

Files centrally stored on IBM Server, with Intel Xeon processor,System x3200.

Calculations are always on 'Manual' and we use F9 when required, even 'Recalculate before Save' is unchecked so that Excel need not bother about it!

Even tried setting Excel processing priority to 'Above Normal' and 'High'-no luck.

Someone Pls throw some light on the matter...

the darkness is enveloping fast...
 
Try the following

1. Saving it to your local PC

2. Open & Repair, use the File Open command and use the little drop down to the side of the Open Button

3. With the file open, Ctrl Alt F9, save file, Close and reopen Excel, reopen file

4. Save as another file format and re-import, you may need to export macros etc first
 
4 seperate steps, not related


Close all other programs eg: IE, Firfox etc, they use heaps of memory


1. Try saving the file to your local PC to c:Test or My Documents, see if that makes a difference


2. Open & Repair, use the File Open command and use the little drop down to the side of the Open Button, select Open & Repair


3. With the file open, press Ctrl Alt F9, save the file, Close and reopen Excel, reopen the file


4. Save As an Excel Binary Workbook *.xlsb and re-open


5. Save as another file format eg: Open Document Spreadsheet, and re-import, you may need to export macros etc first
 
Hi! we are using Windows XP and I do not see Open and Repair anywhere!?

Also do not get the option in Save As for *.xlsb type file format

Another thing is that this saving activity has to be done on the live document many times in a day, so it is not a one off event, whether above procedure will keep all the file formats and validations intact?

Pls correct me if i am wrong, read somewhere that Excel is not affected by the RAM but on the processor capacity, is that correct?

Thanks in advance for your kind response, sincerely appreciate the effort.
 
For Open and Repair

Click File, Open, find your file

On the side of the Open Button there is a small down arrow

select the down arrow

and select Open and Repair


The above are all one off's and are trying to eliminate or find a solution to your problem.


Once solved you should be back to saving as normal


What version of Excel are you using ?

Excel 2002/XP both have Open and Repair, but don't have save as *.XLSB

Excel 2007/10 both have Open and Repair and have save as Excel Binary File *.XLSB


ps: How big is the saved file ?
 
Hi!Hui,

Have carried out ur instructions and repaired the file,will let you know the result tomorrow when the file is used throughout the day,

thanks a million, i sure hope this fixes it!

by the way it is brilliant how you find these tiny nuggets of precious info hidden away!! first time i ever saw the dropdown arrow next to the 'Open'- Wow!

Excel is like LIFE - I live and Learn everyday!
 
25-30Mb isn't big, so something is amiss


Do you have any contacts with Excel 2010?

It has very good repair tools built in and is worth getting them to open and repair and then save again


Please let us know how you go
 
25-30MB is the size, only when it is fresh, after some staff has worked on it all day, the file size grows to nearly 90-100MB!To bring it back to size, I remove the sharing and put it back on.

Changing over to latest versions of Excel, the problem is that our staff is not very computer savvy ( they are all self taught only to limited extent in using our inhouse package!)and the new look MENU's are very daunting and it will simply bring all our activity to a halt!hence we are stuck with Excel 2003.

the big problems of running small businesses...
 
Why do file sizes grow anyway, when the workbooks are shared?

Does anyone know?

Does Tracking Change History option have anything to do with it?

Is there a proper way of using the Advanced option in shared workbook?
 
i'll add my 2 cents in Excel 2003.


Very often I will receive files from colleagues where there are hundreds of thousands of rows with no data and thus making file size huge (you can test by doing ctrl-end to see how it jump to the end of document). I usually select those rows and columns and delete them. afterwards, i'll save the file again and the size decreases dramatically.
 
Hi!Fred,

By Rows with no data, do you mean completely blank rows or rows with formulas but no data input in them to get results...?

I normally copy paste my formula rows upto certain row nos like 999 or 3000,5000, etc.not more than 9999 anyway.But all my formulas are written for max upto 9999 so that I do not get error results when data is being input daily and crosses into a row where there are no formulas..

appreciate ur input, thanks!
 
In my case in MS Excel 2003 the rows and columns are completely blank with no data nor formulae. all I can tell is by the "size" of the scroll bar on the right hand side of the screen or the bottom right of the screen.


For instance, i will receive a spreadsheet with 3000 rows and 55 columns. but when i use the scroll bars to go to the bottom of the screen I realise that the scroll bar doesn't touch the end, which indicate that I can scroll further down. Sometimes, the scroll indicator can go all the way to line 65536, making the file size to be in hundreds of MB. So normally i'd go to line 3001 and hi-light til line 65536, right click and "delete" them all. Save it and the size will be down to a reasonable and acceptable level of hundreds of KB.


I have since switched to MS 2007 and I'm not seeing the same thing again.
 
You must log in or register to reply here.
Back
Top