Excel VBA Automation Error The object invoked has disconnected from its clients

Question

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   amp  Reg  amp       amp  Month Date   amp   -   amp  Day Date   amp   -   amp  Year Date   amp    xlsx                  FileFormat  xlOpenXMLWorkbook            End With          Set newBook   Application Workbooks  Region   amp  Reg  amp       amp  Month Date   amp   -   amp  Day Date   amp   -   amp  Year Date   amp    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to automation

Examples related to runtime-error

Examples related to vba