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

On 9/30/2011 4:13 PM, AlexT wrote:
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

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"
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

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