Excel problem

[Jonny Two Shoes]

Would like to know if anyone has experienced the same problem here, and found a fix.

When a user tries to save an excel file it says Not enough memory and then says something about cannot save references to links. Irrespective of RAM available, page file, hard drive space etc.

Microsoft support comes back saying its because their are too many references to cells in the excel file. However at this point in time it is impracticle to go and split the references and try seperate everything into different worksheets to keep the numbers down.

So if anyone here has found an alternative fix for this problem pls let me know.

User is using office 2000 on win xp, and hardware specs are good so its definately not the pc.

 

[antowan]

Past Tip of the Day
http://www.mrexcel.com/td0045.html

Steven from Australia writes: I have created a VBA macro which has to create about 50 Charts from one worksheet. The problem is that everytime I run the program when I get to the 33rd chart an error message displays "Not enough memory", then the Excel program locks up and I have to terminate the program. I have 256Meg of RAM in my PC and I'm using Excel 97 in a Windows NT operating system.

Are you creating each chart as its own chart sheet? Excel can handle 16 million cells on a worksheet, but the quiet secret is that is can not handle a lot of worksheets. The help file says that the number of worksheets is limited by "available memory".

I regularly experience the problem that you have. It is horribly frustrating, because you never know when it is about to crash. If Visual Basic would give you a trappable error, you could stop the macro, save the file, start in a new file. But they don't - you just get a crash.

I have seen the crash happen as late as 130 worksheets and as early as 40. You have to gauge where it is going to crash in your system, then put a counter in the macro. If you think you are going to crash after 32 charts, then stop the process at 30 charts, save them in a new workbook, close that workbook, and start creating them again in a new workbook.

This isn't pretty, but it is the only workaround that I have found.

Another thought - make sure that you close each module and userform in the Visual Basic Editor using the "X" in the upper right corner. I have found that by simply closing all of the components in Visual Basic before running the macro, you can free up a bit more memory and possibly squeeze a few more charts into the "available memory".

Above, I talked about doing things to conserve memory. Steven wrote back today with an excellent discovery - I found that if I set the Charts AutoScaleFont to False, I could create about 120 chart, which has solved my problem. Why this is so I have no idea, but thats Excel. Excellent tip - tuck this one away as an obscure method for conserving memory.

By Bill Jelen on 05-Dec-2001

 

[Jonny Two Shoes]

Thanks although on my side we still have just under 500meg RAM available and a pagefile of now just under 4gigs, 3.5g available. So its definately not a memory issue. Just today it complained to the user "not enough space" when she tried to save the file. She has around 40gig available, this is so freaking weird. And if she works off the server she gets the same error occasionally, even though there is ample RAM and around 135 gig free.

Re-installing excel from scratch with no left over reg keys does not help. Think its one of those live and let live problems.

The user is definately not pc literate enough to start going into the vba side and closing those windows even though I know thats easy to do.

I will look into the Charts AutoScaleFont to False thing. Thanks again anto

 

[ernstn]

Last time I had a problem like that was years ago, because the directory contained to many files. Also look at the temp directory and clean that out.