Excel 2007 Compatibility mode error "Not enough storage to process

[Dean Meyer]

I have a VBA application in Excel that opens 100 workbooks. It works fine in
Excel 2003. But in Excel 2007, it fails half way through with the error
message "Not enough storage to process this command." Both machines are
running XP, and the pagefile is set to the max 4096meg. Any ideas?

 

[Ron de Bruin]

Hi Dean

curious : If you test it with this add-in to get the value of one cell
for example do you have the same problem then ?

http://www.rondebruin.nl/merge.htm

 

[Dean Meyer]

Ron, thanks for your willingness to help out, and for your very nice add-in!

The add-in ran fine with all 106 files, copying one cell from each to a new
sheet. You appear to be closing one workbook before opening the next. My
problem seems to be a limit on how many workbooks Excel 2007 can have open at
once.

Any ideas?

--Dean

 

[Ron de Bruin]

Hi Dean

Memory is the limit normal so it looks like 2007
eat more memory if you open files in compatibility mode

Can you test it with files in 2007 format ?

Why do you want to have 106 files open ?

 

[Dean Meyer]

Excel 2003 can handle up to 1gig of memory, which is plenty.

Excel 2007 is indeed using more memory per file in compatibility mode than
does 2003. And it seems to die at 700meg.

I wonder if there's some constraint other than memory built into
compatibility mode.

We're working on conversion to 2007 files, but it turns out that with many
tens of thousands of lines of VBA code, that'll take some time.

Any thoughts on what the constraint might be?

--Dean