Large macro "drops out" part way through
 

Hello All,

I've got a difficult problem to analyse.
My program opens many workbooks (~40) one at a time then moves data
into each; does a refresh; and then saves the workbook. This all
happens in a second instance of Excel.

Problem is that it has started to just stop half way through with no
error message - I'll activate the instance of excel and one of the
mentioned workbooks will be open in design mode....with no clue as to
why!!

It takes about 25mins all in so stopping after 15mins of success is a
pain.

(Sometimes I can re-run and all will go through ok)

Anybody got any ideas, or experienced anything like this before?

Any help much appreciated
Jason

Large macro "drops out" part way through
 

Difficult to diagnose without seeing it but here are some things to
try...

1) set Application.EnableEvents = False before allowing your app to
open any other workbooks.
2) If you're copying sheets into a workbook you may be hitting a
limit. Excel doesn't specify a number for this limit but it is
dependant on available resources and quantity of data in the sheets
being copied (I think). Excel will give a run-time error '1004':
Method of object '_Worksheet' failed.
The work-around for this is to have the macro Save and Close the
workbook after a certain number of sheets copied in. Then have it re-
open the workbook and carry on where it left off.
3) You say your NOT getting an error so make sure any error handling
within your routines - such as On Error Resume Next - is turned off so
that you're not disguising the problem.

Br, Nick H

Large macro "drops out" part way through
 

Hi Jason,
Are the files located on another machine? A server maybe?
Adding a "DOEVENTS" between files should take care of any network timing issues.

John


wrote in message ...
Hello All,

I've got a difficult problem to analyse.
My program opens many workbooks (~40) one at a time then moves data
into each; does a refresh; and then saves the workbook. This all
happens in a second instance of Excel.

Problem is that it has started to just stop half way through with no
error message - I'll activate the instance of excel and one of the
mentioned workbooks will be open in design mode....with no clue as to
why!!

It takes about 25mins all in so stopping after 15mins of success is a
pain.

(Sometimes I can re-run and all will go through ok)

Anybody got any ideas, or experienced anything like this before?

Any help much appreciated
Jason