Automation Error

[waldermort]

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

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

[waldermort]

I have spent the last 2 hours reading through post after post trying to find a solution to this, only to find what I am looking for within my own post.

After reading through my post, I noticed that

Set wbDest = xlApp.activeworkbook
OpenFile (Dest) 'will close any open books

should infact be

OpenFile (Dest) 'will close any open books
Set wbDest = xlApp.activeworkbook

[RobDog888]

You shouldnt use "activeworkbook" or" activesheet" as they can cause issues from actions like this. Just use the name of your workbook or sheet instead.