Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run-time Error 2147417848 (80010108) (Excel 2000)

    I use a macro to insert sheets, copy contents, update charts .... however, whenever it reaches 40 sheets, my Excel crashes with the following code:

    Run-time Error 2147417848 (80010108)
    Automation error
    The object involved has disconnected from its clients

    It's very frustrating. Any help will be appreciated !

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run-time Error 2147417848 (80010108) (Excel 2000)

    Do you set horizontal page breaks in your macro? If so, see XL2000: Error Message: "Automation Error: The Object Invoked Has Disconnected from Its Clients".

    Do you cut and paste cells in the macro? If so, see XL2000: Cut and Paste Operation in Visual Basic Procedure Hangs Excel.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time Error 2147417848 (80010108) (Excel 2000)

    Hens,

    I did not use horizontal page break or "cut" methods. Following are my code:


    Sub CopySum3()
    Dim i#
    Dim strLine$, stemp$, strSheetName$
    i = 2
    stemp = Sheets("SUM").Range("AG" & i).Value
    Do While Len(stemp) > 1
    Sheets("SUM").Range("F2").Value = stemp
    strSheetName = CStr(Sheets("SUM").Range("AH" & i).Value)
    Sheets.Add
    ActiveSheet.Name = strSheetName
    Sheets("SUM").Cells.Copy Destination:=Sheets(strSheetName).Cells
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    i = i + 1
    stemp = Sheets("SUM").Range("AG" & i).Value
    Loop
    End Sub

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time Error 2147417848 (80010108) (Excel 2000)

    Hans,

    Could this also be file size related ? If I delete a lot of contents on the main sheet, I seem to be able to run this for many more pages.

    TQ

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run-time Error 2147417848 (80010108) (Excel 2000)

    By copying *all* 256 x 65,536 = 16,777,216 cells of the SUM worksheet to tens of new worksheets, you're probably using up too many resources, even if Excel tries to do it intelligently. Can't you limit the copied area?

    You might also try splitting the copy operation into two instructions:

    Sheets("SUM").Cells.Copy
    Sheets(strSheetName).Paste

    preferably with a specific range instead of Cells.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Run-time Error 2147417848 (80010108) (Excel 2000)

    Sheets("SUM").range(range("a1"), range("a1").specialCells(xllastcell)).Copy
    sheets(2).range("a1").pastespecial

    Should work and only copy cells with something in it, so it should limit the range copied. xllastcell is not always accurate, but it should be less than all the cells, and it should contain all the cells "being used".

    The other option is to just copy the sheet, it takes less resources, though there is a limit on the cell contents by this method.

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time Error 2147417848 (80010108) (Excel 2000)

    Hans,
    I copied range instead of the whole sheets and it worked. Many thanks.

    TQ

Posting Permissions

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