When I run the macro I get the error 'Run-Time Error '-21474417848 (80010108)': Automation Error - The Object invoked has Disconnected from its Client

[Beau B]

I am running a VB macro that works on both Windows 2010 Excel 2010 and Windows XP Service Pac 3 Excel 2007 systems, but will not run on a Windows 7 Excel 2007. I get the error 'Run-Time Error '-21474417848 (80010108)': Automation Error - The Object invoked has Disconnected from its Clients.

The macro fails at:

Selection.TextToColumns Destination:=Range("B9"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1))

Here is the entire macro:

Sub ImportSalonBizReport()
'
' ImportSalonBizReport Macro
' Macro recorded 3/2/2011 by Administrator
'
    Sheets("INTEGRITY CHECK").Select
    ActiveSheet.Unprotect
    Sheets("Staff Request Summary Report").Select
    ActiveSheet.Unprotect
    Cells.Select
    Selection.CLEARCONTENTS
    Workbooks.Open Filename:=ThisWorkbook.Path & "\Staff Request Summary.xlsx"
    Cells.Select
    Selection.Copy
    Windows("Commission Workbook v77 V2.xlsm").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Selection.RowHeight = 36
    ActiveSheet.Protect
    Windows("Staff Request Summary.xlsx").Activate
    ActiveSheet.Protect
    ActiveWorkbook.Close
    Sheets("DATA INTEGRITY PREPROCESSOR").Select
    Range("A3").Select
    Selection.Copy
    Sheets("INTEGRITY CHECK").Select
    Range("B9").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("B9"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1))
    ActiveSheet.Protect
    Sheets("REDO ADJUSTMENTS INPUT").Select
    Range("A10").Select
    ActiveWindow.SmallScroll Down:=42
    Range("A10:A60").Select
    Selection.CLEARCONTENTS
    Range("C10:C60").Select
    Selection.CLEARCONTENTS
    Range("E10:E60").Select
    Selection.CLEARCONTENTS
    Sheets("RETAIL").Select
    Range("E11:H11").Select
    Selection.CLEARCONTENTS
    Range("C13:H42").Select
    Selection.CLEARCONTENTS
    Sheets("CONTROL").Select
    Range("B3:J3").Select
End Sub

Any advice would be greatly appreciated.

Beau B.

 

[chadcollings]

Hey, just some basic clean-up to your code.

Hard to identify where the issue is without knowing what data is in the book..

Sub ImportSalonBizReport()
'
' ImportSalonBizReport Macro
' Macro recorded 3/2/2011 by Administrator
'
Sheets("INTEGRITY CHECK").Unprotect
Sheets("Staff Request Summary Report").Unprotect


Sheets("Staff Request Summary Report").Cells.ClearContents
Sheets("Staff Request Summary Report").Select


Workbooks.Open Filename:=ThisWorkbook.Path & "\Staff Request Summary.xlsx"
ActiveSheet.Cells.Copy


Windows("Commission Workbook v77 V2.xlsm").Activate
Range("A1").Paste
Selection.RowHeight = 36
ActiveSheet.Protect


Windows("Staff Request Summary.xlsx").Activate
ActiveSheet.Protect
ActiveWorkbook.Close True 'trade out True for False if you dont want to save


Sheets("DATA INTEGRITY PREPROCESSOR").Range("A3").Copy
Sheets("INTEGRITY CHECK").Select
Range("B9").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False


Selection.TextToColumns Destination:=Range("B9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1))
ActiveSheet.Protect


With Sheets("REDO ADJUSTMENTS INPUT")
    .Range("A10:A60").ClearContents
    .Range("C10:C60").ClearContents
    .Range("E10:E60").ClearContents
End With


With Sheets("RETAIL")
    .Range("E11:H11").ClearContents
    .Range("C13:H42").ClearContents
End With


Sheets("CONTROL").Select
Range("B3:J3").Select
End Sub