- Rule 1 – NEVER run apps like ‘Excel’ on the server.
- Rule 2 – Worry when any workbook hits 12-15 MB.
- 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.