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）。复制之前：= newBook.Sheets（Sheet1）

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

我在模块的顶部有Explicit选项，我已经检查过，确保它的子目录内没有全局变量，也就是说我完全可以忽略某些东西。我也在一点点放了一个计时器，以确保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:\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

推荐答案

我已经在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）我改变了我如何关闭并保存文件（从关闭& 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屋！