Transferring sheets to another workbook

[azizrasul]

I am getting an error (Object doesn't support this property or method) in the following code in the line:-

objExcelAppTo.Activate

Private Sub cmdCombineWorkbooks2_Click()
    
    Dim strPathFrom As String
    Dim strPathTo As String
    Dim strExcelFileFrom As String
    Dim strExcelFileTo As String
    Dim strConn As String
    Dim objExcelAppFrom As Object
    Dim objExcelAppTo As Object
    Dim ws As Object
    Dim blnFrom As Boolean
    Dim blnTo As Boolean
    Dim rst As ADODB.Recordset
    Dim i As Integer
    
    On Error GoTo ErrorHandler
    
    strPathFrom = "C:\Users\Aziz\Desktop\MS Excel Files\"
    strPathTo = "C:\Users\Aziz\Desktop\MS Excel Files\"
    strExcelFileFrom = "Book3.xlsx"
    strExcelFileTo = "CombineWorkbooks.xlsx"
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPathFrom & strExcelFileFrom & ";Extended Properties='Excel 12.0 Xml;HDR=Yes'"
    i = 1
    
    blnFrom = OpenExcelFile2(strPathFrom & strExcelFileFrom, True, False, "Nadeem356")
    blnTo = OpenExcelFile2(strPathTo & strExcelFileTo, True, False, "")

    Set objExcelAppFrom = GetObject(strPathFrom & strExcelFileFrom).Application
    Set objExcelAppTo = GetObject(strPathTo & strExcelFileTo).Application

    For Each ws In objExcelAppFrom.Sheets
        strSQL = "SELECT * FROM [" & ws.Name & "$]"
        Set rst = New ADODB.Recordset
        rst.Open strSQL, strConn, adOpenUnspecified, adLockUnspecified
        objExcelAppTo.Activate
'        AppActivate objExcelAppTo.Windows(1).Caption
        objExcelAppTo.Sheets(i).Range("A7").CopyFromRecordset rst
        rst.Close
        Set rst = Nothing
        i = i + 1
    Next ws

ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & ": " & Err.Description
'        Resume
    End If
    
End Sub

 

[xenou]

objExcelAppTo.Activate

this line should not be necessary at all.

 

[azizrasul]

OK I will remove that. I have created a test MS Access db to show where the problem may be. Is it possible to upload the file here or is that not possible?