Excel / VBA out of resources
Posted on 2011-09-30
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