+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    10-18-2013
    Location
    Edinburgh
    MS-Off Ver
    Excel 2010
    Posts
    3

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

    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
    Last edited by CJH@FSI; 10-18-2013 at 12:45 PM. Reason: typos

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

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

    Off the top of my head, I'd say get rid of that select line - it's unnecessary and could be the problem??
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    10-18-2013
    Location
    Edinburgh
    MS-Off Ver
    Excel 2010
    Posts
    3

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

    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
    Last edited by CJH@FSI; 10-21-2013 at 06:32 AM. Reason: Added more details

  4. #4
    Registered User
    Join Date
    10-18-2013
    Location
    Edinburgh
    MS-Off Ver
    Excel 2010
    Posts
    3

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

    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.
    Last edited by CJH@FSI; 10-21-2013 at 07:09 AM. Reason: added code tags

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Code works in Windows excel 2003 and 2010 but crashes in Mac excel 2011
    By kiweed in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-19-2012, 08:46 PM
  2. [SOLVED] Macro works but crashes Excel 2010
    By Willardio in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-09-2012, 08:49 AM
  3. MS Excel 2010 crashes
    By Richard_G in forum Excel General
    Replies: 0
    Last Post: 07-11-2011, 08:57 AM
  4. MS Excel 2010 crashes
    By Richard_G in forum Excel General
    Replies: 1
    Last Post: 06-25-2011, 12:18 PM
  5. Code Crashes Excel 2010 but works in Excel 2003
    By aaronf316 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-07-2011, 03:46 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1