Crashes macro application in Microsoft Excel 97 under Windows XP

[arun0580]

I am running a VBA macro application in Microsoft Excel 97 under Windows XP -Service Pack 1 to,
1.Open an Excel work book .
2.Update a value in a cell
3.Save and close the file.
I need to repeat the steps 1,2,3 for 1,00,000 times. But after executing the process around 32,000 times the Excel application crashes.The whole OS hangs. And a message box appears As
" Run time error -2147467259 (80004005) ", method 'open' of object workbook's failed.

I am facing this problem since last two weeks, If any body have the solution please comment it .

Thanks
Arun

[kadghar]

I am running a VBA macro application in Microsoft Excel 97 under Windows XP -Service Pack 1 to,
1.Open an Excel work book .
2.Update a value in a cell
3.Save and close the file.
I need to repeat the steps 1,2,3 for 1,00,000 times. But after executing the process around 32,000 times the Excel application crashes.The whole OS hangs. And a message box appears As
" Run time error -2147467259 (80004005) ", method 'open' of object workbook's failed.

I am facing this problem since last two weeks, If any body have the solution please comment it .

Thanks
Arun

For the way you're describing it, i'd say there's an integer in the code (presumably in the file's name). Define it as a long.

Or you are creating a new object each time you open a workbook, this will create a new instance, and if you're not closing the ones before, and by a coincidence, your pc crashes when you reach 32K instances. But i'd say this is not the reason, since error (80004005) is a HDD or LAN error (correct me if not, please).

[Killer42]

Seems simple enough to verify. Check exactly which number it bombs on. If it's file number 32768 or 32769, then the odds are very good that the problem is related to data size (number of bits).

However, the data-size problem could strike in an unexpected area. For example, what if the OS (or something along the chain, anyway) can't handle more than 32K files in a folder?

While you have a think about this, can we see the actual code?

Oh! Something else that comes to mind. Are you sure you're closing the files after opening them? Perhaps Excel is simply complaining that you're trying to open too many files at once.

Kadghar makes a good point, too. An integer value might "wrap around" after 32K, so you might think you're asking for file 32768, but actually be requesting a negative number.


P.S. Sorry, I just realised I may be making an invalid assumption here. I thought that you were using the number as part of the file name, and thus opening 32000 different files. But in fact I don't think you said that. Are you in fact working with the same file each time, or what?