Automation Error - Object has diconnected from Clients.

[RogerC]

I'm running a simple macro that is invoked when the user selects the option 'Complete' from a data validated column. It is supposed to copy the active row from Sheet1 to a row in Sheet2, then delete the row that was copied from Sheet1. When I run the macro I get the error 'Run-Time Error '-21474417848 (80010108)': Automation Error - The Object invoked has Disconnected from its Clients.' The error only happens sometimes... sometimes the macro works fine. It happens maybe once every 3 times the macro is run!
I read the article at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319832
and this seems to be the problem. But I don't understand the article well enough to know how to fix the code.

Here is the macro:
Sub Move_Completed_Row()
'
Application.DisplayFullScreen = True
Selection.EntireRow.Cut
Sheets("Sheet2").Select
Rows("9:9").Select
Selection.EntireRow.Insert Shift:=xlDown
Range("A1").Select
Sheets("Sheet1").Select
Selection.EntireRow.Delete Shift:=xlUp

End Sub
Can anyone give me an idea of what to do to the code to prevent the error? Thanks!

 

[Derek]

Hi Roger
Can empathise with your difficulty in following the MKBA article, I do not pretend to understand it. I had a similar problem with a macro that used "cut". It worked reliably in NT4 but occasionally glitched when run on Windows2000 giving the message "object invoked has disconnected from its client". I traced the problem to the "cut" function and reworked the macro using "copy" and "paste" instead. Since then it hasn't given any problems.
Hope this is of some help
regards
Derek

 

[RogerC]

Thanks Derek. I think I've fixed the problem another way though... You're going to think I'm crazy, but all I did was turn off the Input Message for the Data Validation that is in one cell of the row being moved by the macro (the little yellow note that is next to the drop-down arrow that pops up when you click in the Data Validated cell). The selection made in this cell is what actaully triggers the macro, so the Message is active when the macro runs. After I turned off the Input Message option, the macro has not failed once!
This message was edited by RogerC on 2002-09-20 10:51

 

[jtdrexel]

I ran into this same error today. But i resolved the problem by going around it. All codes in my module worked fine for years until today! After investigating this problem i discovered that copying and pasting between two seperate instances of excel while the copying range have cell(s) within them that contains formula will yield this error. Therefore i took the formula containing range and pasted it into another column with just the values and then used the regular copy paste method. Problem solved. I'm not exactly sure why the error occured in the first place especially if it has been working for a long time! Here are my codes...

OLD CODE:
Public Sub loadlogs_Click()
Dim XLobject As Excel.Application, wbXLS As Excel.Workbook

rootpath = constant_information("rootpath")
Str_Dt = "D1": Stp_Dt = "D2"
D1 = Sheets("STL").Range(Str_Dt).Value: D2 = Sheets("STL").Range(Stp_Dt).Value
D_diff = DateDiff("m", D1, D2): If D_diff < 0 Then Exit Sub

screenfreeze (0) ': ThisWorkbook.Application.ScreenUpdating = False
firstrow = 4: lastcol = "X": RowIDCol = "AA"
lastrow1 = ThisWorkbook.Sheets("STL").Rows.Count
ThisWorkbook.Sheets("STL").Rows(firstrow & ":" & lastrow1).Delete Shift:=xlUp 'Selection.Delete Shift:=xlUp
Set XLobject = New Excel.Application: XLobject.Visible = False

For i = 0 To D_diff
targetdate = DateAdd("m", i, D1)
targetpath = rootpath & Year(targetdate) & " LogBooks\STL_" & Format(targetdate, "mmmyyyy") & ".xls"
'Debug.Print targetpath

'Dim RWstatus As Boolean
'RWstatus = Application.ThisWorkbook.ReadOnly
'If RWstatus = False Then screenfreeze (1): Exit Sub
'ThisWorkbookPath = ThisWorkbook.path & "\" & ThisWorkbook.Name

fs = chkfile(targetpath) '0=nofile;1=readwrite;2=readonly

If fs <> 0 Then 'And RWstatus = True Then
thiswrkbk_nxtrow = ThisWorkbook.Sheets("STL").Cells(Rows.Count, 1).End(xlUp).Row + 1
Set wbXLS = XLobject.Workbooks.Open(targetpath, Password:="letmein", ReadOnly:=True) ', Writerespassword:="tracker")
lastrow2 = wbXLS.Sheets("STL").Cells(Rows.Count, 1).End(xlUp).Row
'The next two lines work just fine! Copy range contains no formula.
wbXLS.Sheets("STL").Range("A" & firstrow & ":" & lastcol & lastrow2).Copy
ThisWorkbook.Sheets("STL").Cells(thiswrkbk_nxtrow, 1).Select: ActiveSheet.Paste
'The ActiveSheet.Paste below yields an errors because the range that is being copied have cells which contain formulas.
wbXLS.Sheets("STL").Range(RowIDCol & firstrow & ":" & RowIDCol & lastrow2).Copy
ThisWorkbook.Sheets("STL").Cells(thiswrkbk_nxtrow, col(RowIDCol)).Select: ActiveSheet.Paste
wbXLS.Application.CutCopyMode = False
wbXLS.Close Savechanges:=False
ElseIf fs = 0 Then
h = MsgBox("Fatal Error: Logbook '" & "STL_" & Format(targetdate, "mmmyyyy") & "' could not be located! Loading process for this book will be skipped. Please notify administrator!", vbExclamation)
End If
Next
XLobject.Quit
screenfreeze (1)
End Sub

NEW CODE:
Public Sub loadlogs_Click()
Dim XLobject As Excel.Application, wbXLS As Excel.Workbook

rootpath = constant_information("rootpath")
Str_Dt = "D1": Stp_Dt = "D2"
D1 = Sheets("STL").Range(Str_Dt).Value: D2 = Sheets("STL").Range(Stp_Dt).Value
D_diff = DateDiff("m", D1, D2): If D_diff < 0 Then Exit Sub

screenfreeze (0) ': ThisWorkbook.Application.ScreenUpdating = False
firstrow = 4: lastcol = "X": RowIDCol = "AA": CopyRowIDCol = "AB"
lastrow1 = ThisWorkbook.Sheets("STL").Rows.Count
ThisWorkbook.Sheets("STL").Rows(firstrow & ":" & lastrow1).Delete Shift:=xlUp 'Selection.Delete Shift:=xlUp
Set XLobject = New Excel.Application: XLobject.Visible = False

For i = 0 To D_diff
targetdate = DateAdd("m", i, D1)
targetpath = rootpath & Year(targetdate) & " LogBooks\STL_" & Format(targetdate, "mmmyyyy") & ".xls"
'Debug.Print targetpath

'Dim RWstatus As Boolean
'RWstatus = Application.ThisWorkbook.ReadOnly
'If RWstatus = False Then screenfreeze (1): Exit Sub
'ThisWorkbookPath = ThisWorkbook.path & "\" & ThisWorkbook.Name

fs = chkfile(targetpath) '0=nofile;1=readwrite;2=readonly

If fs <> 0 Then 'And RWstatus = True Then
thiswrkbk_nxtrow = ThisWorkbook.Sheets("STL").Cells(Rows.Count, 1).End(xlUp).Row + 1
Set wbXLS = XLobject.Workbooks.Open(targetpath, Password:="letmein", ReadOnly:=True) ', Writerespassword:="tracker")
lastrow2 = wbXLS.Sheets("STL").Cells(Rows.Count, 1).End(xlUp).Row
wbXLS.Sheets("STL").Range("A" & firstrow & ":" & lastcol & lastrow2).Copy
ThisWorkbook.Sheets("STL").Cells(thiswrkbk_nxtrow, 1).Select: ActiveSheet.Paste

'The reason why i wrote the weird code in the 4 lines below is because a regular copy and paste _
like in the lines just above yields an error: runtime error '-2147427848 (80010108)'. after _
investigating this problem i discovered that copying and pasting between two seperate instances _
of excel while the copying range have cell(s) within them that contains formula will yield this _
error. Therefore in the following line of codes, i take the formula containing range and paste _
it into another column with just the values and then use the regular copy paste method.
wbXLS.Sheets("STL").Range(RowIDCol & firstrow & ":" & RowIDCol & lastrow2).Copy
wbXLS.Sheets("STL").Range(CopyRowIDCol & firstrow).PasteSpecial Paste:=xlPasteValues
wbXLS.Sheets("STL").Range(CopyRowIDCol & firstrow & ":" & CopyRowIDCol & lastrow2).Copy
ThisWorkbook.Sheets("STL").Cells(thiswrkbk_nxtrow, col(RowIDCol)).Select: ActiveSheet.Paste

wbXLS.Application.CutCopyMode = False
wbXLS.Close Savechanges:=False
ElseIf fs = 0 Then
h = MsgBox("Fatal Error: Logbook '" & "STL_" & Format(targetdate, "mmmyyyy") & "' could not be located! Loading process for this book will be skipped. Please notify administrator!", vbExclamation)
End If
Next
XLobject.Quit
screenfreeze (1)
End Sub

 

[shadow12345]

I just got this error myself.. all I did is turn on the copy... then did it again later in the macro instead.


Columns("H:H").Select

'Selection.Copy
Columns("F:F").Select

'break is here so turn the copy off

Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Application.CutCopyMode = False

 

[bissettbd]

I found in my instance this error was caused by Excel recalculating all the formulas in my Workbook while the Macro was running, locking the program and causing a GPF. It can be cured by disabling automatic macro calculation with:

'Turn Off Automatic Calculation During Macro Execution (At least one Worksheet Must be Shown)
Application.Calculation = xlCalculationManual

and re-enabling Macro Calculation at the end of the macro with:

Application.Calculation = xlCalculationAutomatic

 

[cgrady2020]

I found in my instance this error was caused by Excel recalculating all the formulas in my Workbook while the Macro was running, locking the program and causing a GPF. It can be cured by disabling automatic macro calculation with:

'Turn Off Automatic Calculation During Macro Execution (At least one Worksheet Must be Shown)
Application.Calculation = xlCalculationManual

and re-enabling Macro Calculation at the end of the macro with:

Application.Calculation = xlCalculationAutomatic

Thank You. This tip was a massive time saver for me.

 

[mgoss]

Thanks bissettbd, this one worked for me as well. Problem solved.