Automation Error - Object has diconnected from Clients.

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
536
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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top