Large macro "drops out" part way through

W

whytheq

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
 
N

Nick H

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
 
J

jaf

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top