Solved

Excel / VBA out of resources

Posted on 2011-09-30
2
381 Views
Last Modified: 2012-05-12
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



0
Comment
Question by:atak2983
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 1

Accepted Solution

by:
atak2983 earned 0 total points
ID: 36896189
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
0
 
LVL 1

Author Closing Comment

by:atak2983
ID: 36896191
self solved as per description in message
0

Featured Post

Office 365 Training for Admins

Learn how to provision tenants, synchronize on-premise Active Directory, and implement Single Sign-On with these master level course.  Only from Platform Scholar

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
move line without macro or copy/paste 6 44
Microsoft office 2016 PDF prints out A4 10 29
Vlookup Help 3 27
Unique List in UserForm 3 22
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Outlook Free & Paid Tools
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question