Excel VBA 自动化错误：调用的对象已与其客户端断开连接

Excel VBA Automation Error: The object invoked has disconnected from its clients

提问by William

I know I've seen references to this issue before, but I have tried several of the suggestions and I am still getting the error. I have a workbook that assembles data from another book and generates a report. I then want to make a new workbook, copy the report information into the new book, save the new book and close it, and then move on to the next report. It should do this around 10 times. In the part of my code where I am copying and pasting the sheets, I am getting an error

我知道我以前看过这个问题的参考资料，但我已经尝试了几个建议，但仍然出现错误。我有一本工作簿，可以从另一本书中收集数据并生成报告。然后我想制作一个新的工作簿，将报表信息复制到新簿中，保存新簿并关闭它，然后继续进行下一个报表。它应该这样做大约 10 次。在我复制和粘贴工作表的代码部分中，出现错误

Error -2147417848 Automation error The object invoked has disconnected from its clients

错误 -2147417848 自动化错误调用的对象已与其客户端断开连接

I have checked other postings about this error, and tried the suggested solutions without any results. the interesting thing is that sometimes it will make it through 5 cycles of code before breaking, sometimes only 2. The only consistency is that it always breaks in the same place

我检查了有关此错误的其他帖子，并尝试了建议的解决方案，但没有任何结果。有趣的是，有时它会通过 5 个代码循环才断掉，有时只有 2 个。唯一的一致性是它总是在同一个地方断掉

fromBook.Sheets("Report").Copy Before:=newBook.Sheets("Sheet1")

fromBook.Sheets("Report").Copy Before:=newBook.Sheets("Sheet1")

I have option Explicit at the top of the module, and I have checked to make sure that there are not any globals inside of the sub it is breaking in. That being said, It's entirely possible I have overlooked something. I also put a "timer" in at one point to make sure that the excel sheets were not walking over each other.

我在模块的顶部有选项 Explicit，并且我已经检查以确保它正在闯入的 sub 内部没有任何全局变量。话虽如此，我完全有可能忽略了一些东西。我还在某一时刻放了一个“计时器”，以确保 Excel 表不会相互重叠。

I could really use the help!

我真的可以使用帮助！

Here is my sub's code:

这是我的子代码：

Sub CreateAndSave(ByRef Reg As Integer, ByVal j As Integer)

        Dim fromBook As Workbook
        Dim fromSheet As Worksheet
        Dim newBook As Workbook
        Dim fileExists As Boolean
        Dim i As Integer
        Dim Holder As Integer


        Application.ScreenUpdating = False
        Application.DisplayAlerts = False

            Set fromBook = Application.Workbooks("Region_Audit_Report")
            Set newBook = Workbooks.Add

           With newBook
            .SaveAs Filename:="G:\DataTeam\ExcelDev\Audit Report\Region Workbooks\Region" & Reg & " " & Month(Date) & "-" & Day(Date) & "-" & Year(Date) & ".xlsx" _
            , FileFormat:=xlOpenXMLWorkbook
           End With

        Set newBook = Application.Workbooks("Region" & Reg & " " & Month(Date) & "-" & Day(Date) & "-" & Year(Date) & ".xlsx")

        fromBook.Sheets("Report").Copy Before:=newBook.Sheets("Sheet1")
        fromBook.Sheets("MonthData").Copy After:=newBook.Sheets("Report")

        newBook.Sheets("MonthData").Range("A1") = "Month"
        newBook.Sheets("MonthData").Range("B1") = "Store#"
        newBook.Sheets("MonthData").Range("C1") = "District"
        newBook.Sheets("MonthData").Range("D1") = "Region"
        newBook.Sheets("MonthData").Range("E1") = "Due Date"
        newBook.Sheets("MonthData").Range("F1") = "Comp Date"
        newBook.Sheets("MonthData").Range("G1") = "# of Errors"
        newBook.Sheets("MonthData").Range("H1") = "Late?"
        newBook.Sheets("MonthData").Range("I1") = "Complete?"

        newBook.Sheets("MonthData").Range("A1:I1").Interior.ColorIndex = 43


            newBook.Save

            newBook.Close


            Application.DisplayAlerts = True

    End Sub

采纳答案by red

I have had this problem on multiple projects converting Excel 2000 to 2010. Here is what I found which seems to be working. I made two changes, but not sure which caused the success:

我在将 Excel 2000 转换为 2010 的多个项目中遇到了这个问题。这是我发现的似乎有效的内容。我做了两个更改，但不确定哪个导致成功：

1) I changed how I closed and saved the file (from close & save = true to save as the same file name and close the file:

1）我更改了关闭和保存文件的方式（从 close & save = true 保存为相同的文件名并关闭文件：

...
    Dim oFile           As Object       ' File being processed
...
[Where the error happens - where aArray(i) is just the name of an Excel.xlsb file]
   Set oFile = GetObject(aArray(i))
...
'oFile.Close SaveChanges:=True    - OLD CODE WHICH ERROR'D
'New Code
oFile.SaveAs Filename:=oFile.Name
oFile.Close SaveChanges:=False

2) I went back and looked for all of the .range in the code and made sure it was the full construct..

2）我回去寻找代码中的所有 .range 并确保它是完整的构造..

Application.Workbooks("workbook name").Worksheets("worksheet name").Range("G19").Value

or (not 100% sure if this is correct syntax, but this is the 'effort' i made)

或（不是 100% 确定这是否是正确的语法，但这是我所做的“努力”）

ActiveSheet.Range("A1").Select

回答by eagle

I have just met this problem today: I migrated my Excel project from Office 2007 to 2010. At a certain point, when my macro tried to Insert a new line (e.g. Range("5:5").Insert), the same error message came. It happens only when previously another sheet has been edited (my macro switches to another sheet).

我今天刚遇到这个问题：我将我的 Excel 项目从 Office 2007 迁移到 2010。在某个时刻，当我的宏尝试插入新行（例如Range("5:5").Insert）时，出现了相同的错误消息。只有在之前编辑过另一张工作表时才会发生这种情况（我的宏切换到另一张工作表）。

Thanks to Google, and your discussion, I found the following solution (based on the answer given by "red" at answered Jul 30 '13 at 0:27): after switching to the sheet a Cell has to be edited before inserting a new row. I have added the following code:

感谢 Google 和您的讨论，我找到了以下解决方案（基于“red”在 13 年 7 月 30 日 0:27 回答时给出的答案）：切换到工作表后，必须在插入新单元格之前编辑单元格排。我添加了以下代码：

'=== Excel bugfix workaround - 2014.08.17
Range("B1").Activate
vCellValue = Range("B1").Value
Range("B1").ClearContents
Range("B1").Value = vCellValue

"B1" can be replaced by any cell on the sheet.

“B1”可以被工作表上的任何单元格替换。

回答by tjvg1991

You must have used the object, released it ("disconnect"), and used it again. Release object only after you're finished with it, or when calling Form_Closing.

您必须使用过该对象，将其释放（“断开连接”），然后再次使用它。仅在完成对象后或调用Form_Closing.

回答by StymiedforAwhile

I had this same problem in a large Excel 2000 spreadsheet with hundreds of lines of code. My solution was to make the Worksheet active at the beginning of the Class. I.E. ThisWorkbook.Worksheets("WorkSheetName").Activate This was finally discovered when I noticed that if "WorkSheetName" was active when starting the operation (the code) the error didn't occur. Drove me crazy for quite awhile.

我在一个包含数百行代码的大型 Excel 2000 电子表格中遇到了同样的问题。我的解决方案是在课程开始时使工作表处于活动状态。IE ThisWorkbook.Worksheets("WorkSheetName").Activate 当我注意到如果在开始操作（代码）时“WorkSheetName”处于活动状态时，错误没有发生，我终于发现了这一点。让我疯狂了好一阵子。

回答by Jim Cone

Couple of things to try...

有几件事要尝试......

1. Comment out the second "Set NewBook" line of code...

2. You already have an object reference to the workbook.

3. Do your SaveAs after copying the sheets.

1. 注释掉第二行“Set NewBook”代码...

2. 您已经拥有对工作簿的对象引用。

3. 复制工作表后执行另存为。

回答by Durga Mahesh

The error in the below line of code (as mentioned by the requestor-William) is due to the following reason:

以下代码行中的错误（如请求者 William 所述）是由于以下原因：

fromBook.Sheets("Report").Copy Before:=newBook.Sheets("Sheet1")

The destination sheet you are trying to copy to is closed. (Here newbook.Sheets("Sheet1")). Add the below statement just before copying to destination.

您尝试复制到的目标工作表已关闭。（这里newbook.Sheets("Sheet1")）。在复制到目的地之前添加以下语句。

Application.Workbooks.Open ("YOUR SHEET NAME")

This will solve the problem!!

这样就能解决问题！！