Excel – Out of memory

pts.
Tags:
Desktops
Management
Microsoft Office
Microsoft Windows
OS
Security
Servers
SQL Server
Hi, I have a Win2000 Server that we are using for our production QC System. I need to run 3 excel files with macros (Excel 2000) on the server for a monthly report. My problem is that each of these Excel files are already 30MB. When opening the third Excel file, the system prompts that it is out of memory. Is there any setting in the Win 2000 Server that I can make to remove this error message? Our Server has the following specs: HP ML350 G4 Proliant Server PN 356003-421 (1) Intel Xeon 3.2GHz Processor Integrated 1MB Lever 2 Cache Memory 1000 MB DIMM NC7761 PCI Gigabit NIC embedded Integrated dual channel Ultra 320 SCSI Adapter Smart Array 642 PCI 3x72GB RAID5

Answer Wiki

Thanks. We'll let you know when a new response is added.
  1. Rule 1 – NEVER run apps like ‘Excel’ on the server.
  2. Rule 2 – Worry when any workbook hits 12-15 MB.
  3. Rule 3 – The penalty of integration is unforseen gotchas.

In this case the ‘Out Of Memory’ error is not general memory, nor the too many programs at once error from ‘Stack’ limits, but ‘Heap’ memory. The way that Excel manages memory is fascinating, scary but fascinating. Your spreadsheets save structure to Heap and data to general and when you have too many structures = out of memory. And it does it pre-emptively, the attempt to load fails without crashing the system.

While you can try to finagle stack and heap values, the better solution is to get out of ‘Excel’. Probably your best bet is an ‘Access’ database using tables imported from the worksheets in ‘Excel’. Either ‘link’ or ‘embed’ dependeding on the need fro one way or two way updates.

Good Luck.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Chunkalunks
    If you can upgrade to Win2003 and Office 2003 / Excel 2003 with the latest service packs and patches that would be the best solution. Other than that update your Win 2000 server and patch your Excel 2000 with the latest Office service pack , see if it makes a difference, you can always try the information in this link as well http://support.microsoft.com/?kbid=830265 hope this helps
    0 pointsBadges:
    report
  • Poppaman2
    Howard2nd, you beat me to it (again...): The only good reason (IMHO) to run any Office application on your server is if you are running Citrix or Terminal server. That's a whole 'nother ball of wax, so to speak... When opening up a 30MB Excel spreadsheet (and I've encountered issues at 8-10MB; 15MB is huge; 30MB is gargantuan), the actual memory usage is about 40MB (application plus working file PER INSTANCE). Given the way Excel handles memory (hey, it works (kinda - it's everywhere, but nobody except Microsoft really ever claimed that it was BETTER than Lotus123....), that's enough to choke even the most robust (work)horse of a server..... Export to a database, although I disagree with Access as a viable alternative given the size of the files, especially if being used by more than one person (the database has a tendancy to corrupt, and the opportunistic locking "feature" is a real PITA in a multi user environment when that occurs....). If you use a database, try MySQL or whatever you have access to (no pun intendid).
    0 pointsBadges:
    report
  • JayCuizon
    Hi All, Thank you very much for giving your thoughs on the my posting. Basing on your feedbacks, I have decided to export the data regulary to our SQL Server and develop a program to generate the report using the SQL Server as the datasource. Jay
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following