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 · Accepted 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 & 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

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

The Solution to Excel VBA Automation Error: The object invoked has disconnected from its clients is

Most Viewed Questions: