I know there are numerous threads on the forums about this error, but so far none have held the solutions.
I am getting this error:
Automation error
The object invoked has disconnected from its client
when trying to copy a worksheet to another book. Here is my code
VB Code:
Public xlApp As Object 'Excel.Application Dim xlBook As Object 'Excel.Workbook Dim xlSheet As Object 'Excel.Worksheet 'Constructor Public Sub Class_Initialize() Set xlApp = CreateObject("Excel.Application") xlApp.displayalerts = False Exit Sub End Sub Public Function OpenFile(FileName As String) As String If xlApp.activeworkbook Is Nothing Then Set xlBook = xlApp.workbooks.open(FileName) Else If Not xlApp.activeworkbook.fullname = FileName Then xlApp.activeworkbook.Close savechanges:=False Set xlBook = xlApp.workbooks.open(FileName) End If End If xlApp.worksheets(1).Activate Set xlSheet = xlApp.activesheet OpenFile = xlSheet.Name End Function Public Sub MergeBooks(Dest As String, Source As String) Dim wbDest As Object Dim wbSource As Object Set wbDest = xlApp.activeworkbook OpenFile (Dest) 'will close any open books Set wbSource = xlApp.workbooks.open(Source) For Each xlSheet In wbSource.worksheets If xlSheet.Name <> "Info" Then 'The Automation error occurs here xlSheet.Copy after:=wbDest.worksheets(wbDest.worksheets.Count) End If Next xlSheet '(TODO) sheet 1("info") in both books contain a list, they need adding End Sub
Please help![]()




Reply With Quote