Inexplicable Windows Error

ExcelBob

New Member
Joined
Oct 3, 2006
Messages
3
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??
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

ExcelBob

New Member
Joined
Oct 3, 2006
Messages
3
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
Oct 3, 2006
Messages
3
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
 

Forum statistics

Threads
1,141,587
Messages
5,707,252
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top