错误-2147417848自动化错误调用的对象与其客户端断开连接 [英] Error -2147417848 Automation error The object invoked has disconnected from its clients

问题描述

我的代码在Excel 2007中正常工作，因为我转而使用excel 2010我开始得到这个运行时错误自动化错误调用的对象与其客户端断开连接，错误号 -2147417848 ，然后退出。

 表格（风险详细信息）范围（A3），运行此函数后出现错误2次&：BV&（count + 1））。删除Shift：= xlUp 
  

任何帮助我找出为什么会发生这种情况以及如何解决这个问题？

以下是函数的完整代码

 函数clearData（可选刷新为布尔值）作为布尔值
 Application.ScreenUpdating = False 
 
表单（单一风险注册表）。取消保护myPass 
表单（单一风险注册表）。激活
 Cells.FormatConditions.Delete 
 
 
 Sh eets（风险详细信息）。取消保护myPass 
表单（风险）。取消保护myPass 
 Dim currentrange作为范围
 
 
表单（风险）。激活
设置currentrange = Sheets（Risks）。Range（RisksTable）
 currentrange.ClearContents 
 
 
 Dim count As Integer 
 count = Sheets（Risks）。ListObjects（RisksTable）。ListRows.count 
如果count> 1然后
 Sheets（Risks）。Range（A3&C&（count + 1））删除Shift：= xlUp 
如果
 
 
 Sheets（Risk Details）。激活
设置currentrange = Sheets（Risk Details）。范围（RiskDetails）
 currentrange.ClearContents 
 count = （风险详细信息）ListObjects（RiskDetails）。ListRows.count 
如果count> 1然后
表（风险详细信息）范围（A3&：BV&（count + 1））删除Shift：= xlUp 
结束如果
表（单一风险注册表）激活
 count = CInt（Range（ActionsCount））
范围（ActionsCount）= 1 
 
 Dim tableLastRow As Integer 
 tableLastRow = getLastTableRow（）
如果（tableLastRow> = 48）然后
表单（单一风险注册表）。范围（B48&：K& tableLastRow）。删除Shift：= xlUp 
 End If 
 Range（ActionsTable [[Action Description]：[Action Commentary]]）。选择
 Application.CutCopyMode = False 
 Selection.ClearContents 
 
 Application.ScreenUpdating = False 
 Dim DataRange As Range 
 
 Dim o As Shape 
 Dim tC As Variant 
 Dim propertiesCount As Integer 
 Dim i As Integer 
 
 tC = Sheets（TableColumns）。ListObjects（TableColumns）。DataBodyRange 
 
 propertiesCount = GetTemplatePropertiesCount（templates（0）） -  1 
 ReDim属性（0到propertiesCount  -  1，0到4）
 If（ （i + 1）（propertiesCount，2）= templates（0））然后
 properties = properitesColl.Item（i + 1）
 End If 
 
对于计数= 0到propertiesCount  -  1 
如果（（properties（count，4）< C25）And（properties（count，4） - C26）And（properties（count，4） - C27）And（properties（count，4） （属性（count，4） - C38）和（属性（count，4） - C39）和（属性（count，4） - C40）和（属性（count，4） - C41））
 
然后
表单（单一风险注册表）范围（属性（count，4））= 
 End If 
 Next 
 
 Dim columnCount As Integer 
 columnCount = GetColumnCount（）
 count = 0 
 For i = 1 To columnCount 
如果tC（i，4）<> Action然后
设置currentrange = Range（tC（i，4））
 Else 
设置currentrange = Range（ActionsTable [& tC（i，1）&] ）
 End If 
如果（（tC（i，4）=C25）或（tC（i，4）=C26）或（tC（i，4）=C27 ）（tC（i，4）=C28）或（tC（i，4）=C38）或（tC（i，4）=C39）或（tC（i，4）= C40）或（tC（i，4）=C41））
 
然后
 With currentrange.Interior 
 .Pattern = xlSolid 
 .PatternColorIndex = xlAutomatic 
 .Color = 15654866 
 .TintAndShade = 0 
 .PatternTintAndShade = 0 
 End with 
 
 Else 
 With currentrange.Interior 
 .Pattern = xlSolid 
 .PatternColorIndex = xlAutomatic 
 .ThemeColor = xlThemeColorAccent1 
 .TintAndShade = 0.799981688894314 
 .PatternTintAndShade = 0 
结束
结束If 
下一个
 
 
 Shee ts（单一风险注册表）。取消保护myPass 
如果（Range（C3）。Value =）然后
 Range（ActionsTable [[Action Description]：[& addTitle& ]]）。选择
 Selection.Locked = True 
 Rows（45:47）。选择
 Selection.EntireRow.Hidden = True 
 End If $ b $值为Range（TitleCell））Value = Range（OldTitle）+（+ Range（Project_Title）+） 
 
表单（单一风险注册表）。保护myPass，AllowFiltering：= True 
表单（风险详细信息）。保护myPass，AllowFiltering：= True 
 Risks）。保护myPass，AllowFiltering：= True 
 
 doNotRun = False 
范围（C3）选择
 Application.ScreenUpdating = True 
 
退出功能
 
错误：
 
表格（风险详细信息）。保护myPass 
表单（风险）。保护myPass 
 MsgBox err.Description，vbCritical，Error
  

解决方案

Microsoft提供高级答案：

如果代码总是出现在第一次运行的时候， d如果错误或意外行为仅在后续调用相同的代码时发生，则不合格的方法调用就是原因。

I am working on clearing some tables in 3 excel sheets.

My code was working fine with Excel 2007, as i switched to excel 2010 i started getting this runtime error Automation error The object invoked has disconnected from its clients, error number -2147417848 and then excel freezes.

the error is showing after running this function 2 times on the line

    Sheets("Risk Details").Range("A3" & ":BV" & (count + 1)).Delete Shift:=xlUp

can anyone help me in finding out why this is happening and how to work around it?

below are the full code for the function

Function clearData(Optional refresh As Boolean) As Boolean
Application.ScreenUpdating = False

Sheets("Single Risk Register").Unprotect myPass
Sheets("Single Risk Register").Activate
Cells.FormatConditions.Delete


Sheets("Risk Details").Unprotect myPass
Sheets("Risks").Unprotect myPass
Dim currentrange As Range


Sheets("Risks").Activate
Set currentrange = Sheets("Risks").Range("RisksTable")
currentrange.ClearContents


Dim count As Integer
count = Sheets("Risks").ListObjects("RisksTable").ListRows.count
If count > 1 Then
    Sheets("Risks").Range("A3" & ":C" & (count + 1)).Delete Shift:=xlUp
End If


Sheets("Risk Details").Activate
Set currentrange = Sheets("Risk Details").Range("RiskDetails")
currentrange.ClearContents
count = Sheets("Risk Details").ListObjects("RiskDetails").ListRows.count
If count > 1 Then
    Sheets("Risk Details").Range("A3" & ":BV" & (count + 1)).Delete Shift:=xlUp
End If
Sheets("Single Risk Register").Activate
count = CInt(Range("ActionsCount"))
Range("ActionsCount") = 1

Dim tableLastRow As Integer
tableLastRow = getLastTableRow()
If (tableLastRow >= 48) Then
    Sheets("Single Risk Register").Range("B48" & ":K" & tableLastRow).Delete Shift:=xlUp
End If
Range("ActionsTable[[Action Description]:[Action Commentary]]").Select
Application.CutCopyMode = False
Selection.ClearContents

Application.ScreenUpdating = False
Dim DataRange As Range

Dim o As Shape
Dim tC As Variant
Dim propertiesCount As Integer
Dim i As Integer

tC = Sheets("TableColumns").ListObjects("TableColumns").DataBodyRange

propertiesCount = GetTemplatePropertiesCount(templates(0)) - 1
ReDim properties(0 To propertiesCount - 1, 0 To 4)
If (properitesColl.Item(i + 1)(propertiesCount, 2) = templates(0)) Then
    properties = properitesColl.Item(i + 1)
End If

For count = 0 To propertiesCount - 1
    If ((properties(count, 4) <> "C25") And (properties(count, 4) <> "C26") And (properties(count, 4) <> "C27") And (properties(count, 4) <> "C28") And (properties(count, 4) <> "C38") And (properties(count, 4) <> "C39") And (properties(count, 4) <> "C40") And (properties(count, 4) <> "C41"))

    Then
        Sheets("Single Risk Register").Range(properties(count, 4)) = ""
    End If
Next

Dim columnCount As Integer
columnCount = GetColumnCount()
count = 0
For i = 1 To columnCount
    If tC(i, 4) <> "Action" Then
        Set currentrange = Range(tC(i, 4))
    Else
        Set currentrange = Range("ActionsTable[" & tC(i, 1) & "]")
    End If
    If ((tC(i, 4) = "C25") Or (tC(i, 4) = "C26") Or (tC(i, 4) = "C27") Or (tC(i, 4) = "C28") Or (tC(i, 4) = "C38") Or (tC(i, 4) = "C39") Or (tC(i, 4) = "C40") Or (tC(i, 4) = "C41")) 

     Then
        With currentrange.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 15654866
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

    Else
        With currentrange.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
    End If
Next


Sheets("Single Risk Register").Unprotect myPass
If (Range("C3").Value = "") Then
    Range("ActionsTable[[Action Description]:[" & addTitle & "]]").Select
    Selection.Locked = True
    Rows("45:47").Select
    Selection.EntireRow.Hidden = True
End If


Sheets("Single Risk Register").Range(Range("TitleCell")).Value = Range("OldTitle") + " (" + Range("Project_Title") + ")"

Sheets("Single Risk Register").Protect myPass, AllowFiltering:=True
Sheets("Risk Details").Protect myPass, AllowFiltering:=True
Sheets("Risks").Protect myPass, AllowFiltering:=True

doNotRun = False
Range("C3").Select
Application.ScreenUpdating = True

Exit Function

err:  

Sheets("Risk Details").Protect myPass
Sheets("Risks").Protect myPass
MsgBox err.Description, vbCritical, "Error"

解决方案

Microsoft provide a high-level answer:

If the code always appears to work the first time that it is run, and if the errors or the unexpected behaviors occur only during subsequent calls to the same code, an unqualified method call is the cause.

这篇关于错误-2147417848自动化错误调用的对象与其客户端断开连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！