Workbook.Activesheet.Paste problem

Question

I want to run reports in another application (Avaya) and paste results into Excel.  I did this several years ago with Excel 2003 in an earlier OS but can't get it to work now. 

I open four reports in Avaya, and when I click the Excel button (on the sheet I want the reports pasted to) it checks the reports are open.  If any aren't it tells me to, then to restart.

If successful checking for the necessary Avaya reports, Excel then prompts me for a date, and sends the date to the first Avaya report with Sendkeys and DoEvents, then runs the report and copies the results to the clipboard.  The next macro lines here are

AppActivate ThisApp    'goes back to Excel

Range("E4").Select

Workbook.Activesheet.Paste

Where it is supposed to paste the copied report into Excel.  The cell E4 is selected, But at this point I get the Run-time error "424' Object required, with the last command above highlighted when I click Debug.  If I manually paste in the selected cell, the last macro line is what is pasted.

I use Activesheet because there are numerous sheets in the workbook, one for every day.  I figure it should go back to the sheet where I originally clicked the macro button.

Suggestions?

Answer 1

Apparently there is another part of my code that doesn't know the new lingo

    AppActivate Calls1

    Do While FindWindow(vbNullString, Calls1) = 0

    DoEvents

    Loop

    SendKeys "%r+%r", True

    DoEvents

    SendKeys "{tab 2}" + When, True

    DoEvents

    SendKeys "%w+{tab}", True

    DoEvents

"AppActivate Calls1" does indeed activate the appropriate window of the Avaya application.  But it apparently doesn't understand the "FindWindow(vbNullString, Calls1) = 0" statement.  When I put the cursor on FindWindow and press F1 I get the message "Object library invalid or contains references to objexct definitions that could not be found."  Tools/References in the VB window shows no obvious problems.

What I am trying to do is put the focus on another app window (Calls1), then send keystrokes to it.  The problem is that the SendKeys statement is sending those commands to the VB window, not to the Calls1 application that was activated.  Any ideas?

As I said previously, I am trying to make old code that worked in Excel 2003 do similar things now.

Answer 2

Hi,

Where is the copy line in your code?

Answer 3

I substituted "ActiveWorkbook.ActiveSheet.Paste" but it still doesn't work.  Different error message now > Run-time error '1004': Paste Method of Worksheet class failed.  Debug shows the substituted verbiage is now highlighted.

Answer 4

Hi,

Does this work?

Activeworkbook.activesheet.paste