12

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
Community
  • 1
  • 1
William
  • 405
  • 6
  • 11
  • 26
  • Try disabling calculation at the top of your macro. Application.Calculation = xlCalculationManual (then enable it again at the end) ((I have had a similar issue and this fixed it)) – Sam Jun 25 '13 at 17:00
  • I tried. It still broke in the same spot. – William Jun 25 '13 at 18:05

6 Answers6

7

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
jwheron
  • 2,553
  • 2
  • 30
  • 40
red
  • 86
  • 2
4

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.

brasofilo
  • 25,496
  • 15
  • 91
  • 179
eagle
  • 41
  • 1
  • 1
    I was getting the same error while, coincidentally, doing the exact same thing (invoking ".Insert") and your solution is spot on! Thanks for the clear break down! You might want to put the following in bold in your response :) Bottom Line: "...a Cell has to be edited before inserting a new row" – elektrykalAJ Dec 23 '15 at 16:58
3

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.

tjvg1991
  • 382
  • 1
  • 5
  • 10
  • This actually helps. My problem is that I'm trying to call a Sub in PERSONAL.XLSB. But this workbook will be opened automatically when Excel starts.. When I try to open again, Excel will prompt a window to ask if you want to open by "read only"... this cause the disconnect. – Jing He Jan 25 '18 at 10:00
1

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.

StymiedforAwhile
  • 11
  • 1
  • Weird, when I added this statement to my macro it worked, but it had some side effects, so I took it away again and then it still worked. So thanks for the tip! – Peter Horsbøll Møller Dec 23 '13 at 13:09
0

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.

doubleDown
  • 8,048
  • 1
  • 32
  • 48
Jim Cone
  • 146
  • 1
  • 4
  • I made an attempt with the suggested changes, and it broke in the same location, same error, on the second round through the code. – William Jun 26 '13 at 15:08
  • Potential problem this time is that it looks like it cycled around through the code, so that Book2 was opened, before Book1 was saved and closed. – William Jun 26 '13 at 15:10
0

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

meneldal
  • 1,717
  • 1
  • 21
  • 30
Durga Mahesh
  • 1