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

 

[Micron]

Never have done this but I'm sitting here wondering if there's a question because the message is kind of obvious and happens to me occasionally (for different reasons of course) - there is no Activate property or method for the object? There is ActivateMicrosoftApp though. If you have an inkling of what you need/are doing, this is my go to source for all things in the Office object model. If you don't have a clue about what you're looking for, it's not easy to find it. That link will take you to the Excel application object, but everything else you'd want to know about any Office app is there - and then some.

 

[azizrasul]

Tried ActivateMicrosoftApp but no luck. I can't get the

objExcelAppTo.Sheets(i).Range("A7").CopyFromRecordset rst

line to work or the destination workbook (objExcelAppTo) is not being activated prior to this line.

I'm at a complete loss and spent many days on this without success.

I also need to know how to adjust strConn to include a password protected Excel file. Any help would be appreciated.

 

[welshgasman]

I would just be opening both workbooks and Select/Activate each by name.?

 

[Micron]

If no luck, then why are you reporting that a different line is now raising an error, as if you're now getting farther in the code? I'm with Gasman; automation should be easier and more efficient than ADO connections and recordsets. You seem to be copying an entire sheet, so I would have taken the approach of using automation and Excel vba to just copy the sheets to a workbook. That is an inherent function in Excel, with the option to copy a sheet to the same or a different workbook.

 

[azizrasul]

Micron, can you give me an example of the automation code? I did try

ws.Copy After:=objExcelAppTo.Sheets(objExcelAppTo.Sheets.Count)

but I get the error 1004: Copy method of Worksheet class failed

 

[Micron]

Micron, can you give me an example of the automation code?

Not really as I'm out of practice with Excel vba. However, I can point you to one source that I'd go to for a refresher on automation of Excel. The main thing to keep in mind is to create objects on the Access side and then use Excel methods/properties to control them. You need a reference to Excel library also, IIRC.

 

[azizrasul]

Many thanks Micron, I will look into that. Will repost if I still have a problem.

 

[welshgasman]

Micron, can you give me an example of the automation code? I did try

ws.Copy After:=objExcelAppTo.Sheets(objExcelAppTo.Sheets.Count)

but I get the error 1004: Copy method of Worksheet class failed

I tend to use the Macro Recorder to get the basics and then tweak that to suit.?
Plus I also Google a lot.

copy worksheets between workbooks vba - Google Search

 

[azizrasul]

Thanks, I will look at that.