Out of resources error message - when not...

A

AlexT

Folks

I’m having a rather annoying issue with some Excel / VBA code of mine.

This is Excel 2007 / Windows 2003.

My code is a fairly typical “batch processing” procedure where Excel
in launched, retrieves some information from a database, creates a
spreadsheet and closes. This is repeated a few hundred times from
within a command shell loop.

It used ot be working for the past 2 years – no brainer.

All the sudden for past week or so the processing breaks at some
random point (typically around 2-300 iterations, but not on a specific
data set) where Excel throws an error: “Excel cannot complete this
task with the available resources. Choose less data or close other
applications.”.

I have some logging / error tracking in my code and I can definitely
say that

The error does not occur at a specific place in the code – it’s
relatively random but is generally triggered during file i/o operation
(saving) or when adding a new worksheet to an existing workbook.

It is not linked to a specific data being processed but occurs after a
few hundred iteration of my batch routine.

* There is no left over Excel process (ie. Excel is closed after each
iteration)

* There are ample system resources free on the machine (few Gb of
unallocated free RAM).

* The “stuck” Excel instance has a typical RAM usage of 200k, 10-15
User objects, 20-30 GDI objects

* The machine is otherwise working fine, the code was not changed and
Excel was not updated recently.

I am in the process of migrating the whole thing to another unrelated
machine but it’s a rather involved proposition given the
infrastructure this is being deployed. In the meantime I muss confess
that I am pretty much stuck as of why this is happening I would
welcome any suggestion !

Thanks & regards
 
M

Mike S

Folks

I’m having a rather annoying issue with some Excel / VBA code of mine.
This is Excel 2007 / Windows 2003.
My code is a fairly typical “batch processing” procedure where Excel
in launched, retrieves some information from a database, creates a
spreadsheet and closes. This is repeated a few hundred times from
within a command shell loop.
It used ot be working for the past 2 years – no brainer.
All the sudden for past week or so the processing breaks at some
random point (typically around 2-300 iterations, but not on a specific
data set) where Excel throws an error: “Excel cannot complete this
task with the available resources. Choose less data or close other
applications.”.

I have some logging / error tracking in my code and I can definitely
say that

The error does not occur at a specific place in the code – it’s
relatively random but is generally triggered during file i/o operation
(saving) or when adding a new worksheet to an existing workbook.

It is not linked to a specific data being processed but occurs after a
few hundred iteration of my batch routine.

* There is no left over Excel process (ie. Excel is closed after each
iteration)

* There are ample system resources free on the machine (few Gb of
unallocated free RAM).

* The “stuck” Excel instance has a typical RAM usage of 200k, 10-15
User objects, 20-30 GDI objects

* The machine is otherwise working fine, the code was not changed and
Excel was not updated recently.

I am in the process of migrating the whole thing to another unrelated
machine but it’s a rather involved proposition given the
infrastructure this is being deployed. In the meantime I muss confess
that I am pretty much stuck as of why this is happening I would
welcome any suggestion !

Thanks& regards

Would you mind posting the command shell loop?
Did you try monitoring system resources while the command shell loop is
running?

Mike
 
J

Jim Cone

Are there any charts involved?
How many styles are being created?
Are you opening and closing the Excel application a "few hundred times"?

--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)






"AlexT" <[email protected]>
wrote in message
Folks

I’m having a rather annoying issue with some Excel / VBA code of mine.

This is Excel 2007 / Windows 2003.

My code is a fairly typical “batch processing” procedure where Excel
in launched, retrieves some information from a database, creates a
spreadsheet and closes. This is repeated a few hundred times from
within a command shell loop.

It used ot be working for the past 2 years – no brainer.

All the sudden for past week or so the processing breaks at some
random point (typically around 2-300 iterations, but not on a specific
data set) where Excel throws an error: “Excel cannot complete this
task with the available resources. Choose less data or close other
applications.”.

I have some logging / error tracking in my code and I can definitely
say that

The error does not occur at a specific place in the code – it’s
relatively random but is generally triggered during file i/o operation
(saving) or when adding a new worksheet to an existing workbook.

It is not linked to a specific data being processed but occurs after a
few hundred iteration of my batch routine.

* There is no left over Excel process (ie. Excel is closed after each
iteration)

* There are ample system resources free on the machine (few Gb of
unallocated free RAM).

* The “stuck” Excel instance has a typical RAM usage of 200k, 10-15
User objects, 20-30 GDI objects

* The machine is otherwise working fine, the code was not changed and
Excel was not updated recently.

I am in the process of migrating the whole thing to another unrelated
machine but it’s a rather involved proposition given the
infrastructure this is being deployed. In the meantime I muss confess
that I am pretty much stuck as of why this is happening I would
welcome any suggestion !

Thanks & regards
 
A

AlexT

Hi

thanks for all the suggestions.

Turns out I had about 80'000 (!) "recent documents" links that stacked
up in the user profile... probably all files generated during the past
2 years !

Cleaning up those and temp files solved my issue.

Regards
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top