Inexplicable Windows Error

[ExcelBob]

Can someone help me with the following? I'm absolutely stumped.

Problem: We are getting a "Excel has encountered problem and needs to close" error during processing.

We have a workbook that contains a macro which generates new workbooks. These workbooks store business metrics for our company's various projects.

It opens an XML file at the start of processing, which tells the macro which projects need to be updated. The metrics are pulled from our database by a perl script, which stores them in text files. The macro uses these text files as input.

After the project's workbook is created, a "rollup" workbook (which keeps track of metrics across projects) is opened, updated, saved, and closed.

The XML file, input text files, and the output-excel workbooks are all saved on a networked drive.

What's been happening is at a certain point every day, excel crashes with the error I mentioned at the start of the post. It isn't always crashing on the same program every day. However, it does consistently crash on the same line of code everyday:

Workbooks(RollupFile).Save

Now here's the particularly vexing fact: If I restart the macro on the program which crashed, it finishes processing without an error.

Yesterday I put a counter that incremented everytime the macro tried to execute that line of code: it executed 570 times, and crashed on the 571st time.

Any ideas as to what may be causing this problem??

 

[ExcelBob]

Update

Update: We're still running into this problem. That line of code executes anywhere from 570 - 576 times before the script dies.

The script runs for just about 2 hours before it dies.

 

[SydneyGeek]

Hi Bob,

Seems strange that you always get a certain distance in and then crash. I wonder if it's a memory issue -- each loop reclaims most, but not all, of the memory it used on that loop. After n goes, you have highly fragmented memory and the next loop can't run because there isn't enough contiguous memory space.

I have seen similar things with processing multiple files; for my purposes it works fine if I don't try to do them all at once, but do them in chunks.
Is that an option for you?

Denis

 

[ExcelBob]

Hi Denis,

You're experience seems to match mine. When I restart the script on the project it had crashed on, it runs without a hitch. We can do the processing in chunks; it isn't ideal, but if this is an issue with how VBA manages memory, we probably don't have a choice.

Thanks for your help!
- Bob

Hi Bob,

Seems strange that you always get a certain distance in and then crash. I wonder if it's a memory issue -- each loop reclaims most, but not all, of the memory it used on that loop. After n goes, you have highly fragmented memory and the next loop can't run because there isn't enough contiguous memory space.

I have seen similar things with processing multiple files; for my purposes it works fine if I don't try to do them all at once, but do them in chunks.
Is that an option for you?

Denis