AZSteve
09-27-2013, 10:28 PM
[This was previously posted to Microsoft Community, I got a couple of questions/suggestions but no resolution.]
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?
----------------------
Per suggestion, 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 (still in the last macro line) is now highlighted.
----------------------
Additional post by me >
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 code that worked with a previous OS and Excel 2003 do similar things now. I have the feeling that the new OS (Windows 7 Enterprise) and the new Excel (2010) don't talk to each other like the old OS and the old Excel. Suggestions?
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?
----------------------
Per suggestion, 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 (still in the last macro line) is now highlighted.
----------------------
Additional post by me >
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 code that worked with a previous OS and Excel 2003 do similar things now. I have the feeling that the new OS (Windows 7 Enterprise) and the new Excel (2010) don't talk to each other like the old OS and the old Excel. Suggestions?