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

问题描述

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

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

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

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

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

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

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

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

我在模块的顶部有选项显式，我已经检查以确保它闯入的子程序内部没有任何全局变量.话虽如此，我完全有可能忽略了一些东西.我还在某一点放置了一个计时器"，以确保 Excel 表格不会相互重叠.

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.

我真的可以使用帮助！

这是我的子代码:

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:DataTeamExcelDevAudit ReportRegion WorkbooksRegion" & 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

推荐答案

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

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:

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

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:

...
    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) 我回去寻找代码中的所有 .range 并确保它是完整的构造..

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

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

或(不是 100% 确定这是否是正确的语法，但这是我所做的努力")

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

ActiveSheet.Range("A1").Select

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