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

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

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

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")

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.

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

This question is related to excel automation runtime-error vba

The answer is


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) 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) 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

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

ActiveSheet.Range("A1").Select

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).

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:

'=== 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.


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.


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

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.

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

This will solve the problem!!


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.


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.


Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to automation

element not interactable exception in selenium web automation Upload file to SFTP using PowerShell Check if element is clickable in Selenium Java Schedule automatic daily upload with FileZilla How can I start InternetExplorerDriver using Selenium WebDriver How to use Selenium with Python? Excel VBA Automation Error: The object invoked has disconnected from its clients How to type in textbox using Selenium WebDriver (Selenium 2) with Java? Sending email from Command-line via outlook without having to click send R command for setting working directory to source file location in Rstudio

Examples related to runtime-error

ExpressionChangedAfterItHasBeenCheckedError: Expression has changed after it was checked. Previous value: 'undefined' what does Error "Thread 1:EXC_BAD_INSTRUCTION (code=EXC_I386_INVOP, subcode=0x0)" mean? javac: invalid target release: 1.8 C++ error : terminate called after throwing an instance of 'std::bad_alloc' Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException Error No Main class found in NetBeans Runtime error: Could not load file or assembly 'System.Web.WebPages.Razor, Version=3.0.0.0 TypeError: Cannot read property "0" from undefined Array Index Out of Bounds Exception (Java) Excel VBA Automation Error: The object invoked has disconnected from its clients

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?