Excel 2010 VBA sheets(array(sheet1, )).copy command crashes excel - but not all the time.

[CJH@FSI]

I have inherited a complex system consisting of multiple excel worbooks. My task is to upgrade the system from Excel 2003 to Excel 2010. It all works OK in excel 2003.

Background:
There is a single "processor" workbook that runs throughout the working day - its sole purpose is to open and run other "template" workbooks as and when requested.

These "template" workbooks are not true excel templates. They are .xlsm files, often with multiple worksheets each sheet with hundreds of cells referencing a shed load of UDFs (all held in a massive VBA add in), excel functions and other cells / sheets within the workbook. Once all the workbook cells have been recalculated some, but not all of worksheets are copied to a new workbook using the following command:

Please Login or Register  to view this content.

The new workbook is tidied up, formatted, saved and closed then the "processor" opens the next "template" and starts again.

The processor workbook has to run the same template multiple times (albeit with different input parameters) it works OK the first time through but crashes Excel the second time.

The problem:
It manages to create the new book but errors on the sheets.copy command:
Err Nmbr: -2147417848
Err.Desc: Automation error - The object invoked has disconnected from its clients.

Shortly after this, Excels displays the "Microsoft Excel has experienced a problem and needs to close" message. Sometimes it completely freezes, sometimes it just shuts down.

Can anyone explain why this might be?

I have 100 or so templates to upgrade, all with they same basic processing, so need a solution.

Many thanks

[xladept]

Off the top of my head, I'd say get rid of that select line - it's unnecessary and could be the problem??

[CJH@FSI]

No that's not it I'm afraid. I tried it again to make sure but there was no change. First time through fine, second time through catastrophe.

Realised I hadn't checked the error message so gave it another go. This time with error handling switched off:

First Error Message:

Run-time error '-2147417848 (80010108)':
Method 'Copy' of object 'Sheets' failed

Selected debug and continued to step through code - got a 2nd Error Message on the same line:

Run-time error '9':
Subscript out of range

Tried to step through a couple more times got the

Run-time error '9':
Subscript out of range

error again then processing appeared to continue. However the new workbook was not saved & closed neither was the "template" work book closed as it should. The "processor" reopened the "template" workbook then froze. I killed it off.

Gave it another go. Got the

Run-time error '9':
Subscript out of range

error. It had created the new workbook but only the first four sheets in the list had been copied.

Is there anything I need to reset in anyway between runs?

Thanks again

C

[CJH@FSI]

I changed my approach and tried the following:

Please Login or Register  to view this content.

(wbResults is defined as a Workbook object)

It copies the first three sheets then fails with:

-2147417848
Automation error - The object invoked has disconnected from its clients.

[xladept]

Hi CJH,

All I can think of is that you should "repair" your Office 2010. When I installed my 2010, I had all kinds of weird problems - sorry, that's all I can think of