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
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...
Comment out the second "Set NewBook" line of code...
You already have an object reference to the workbook.
Do your SaveAs after copying the sheets.
Source: Stackoverflow.com