| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting focus back to Excel using Sendkeys statements
Hi,
When I run the following program alone from Excel, it works fine: Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select However, when I add this to another program which, again works fine as it is, the program listed above doesnt run properly it will activate the Window named in AppActivate, 'Staff Management' , but the Call subroutines dont run - it just 'freezes' I add in the following to the end of the previous routine (which works fine alone) to get the focus back to Excel before running it with: Appactivate Vantage Interactive Advanced.xls With ThisWorkbook.Sheets (NEWTSform) Appactivate Staff Management Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select End with End Sub It errors here, usually with End sub highlighted with an Expected End with without with or End Select without Select or something similar!. Where am I going run it must be possible to do, if they run alone independently, they should run end-on-end. Ive tried various combinations of code to reclaim the focus back to Excel - but it still errors! Many thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting focus back to Excel using Sendkeys statements
HI,
Remove first end select from Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select to: Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder Case 3 Call inputSMIEtonewtsorder End Select -- Regards, Halim "Zakynthos" wrote: Hi, When I run the following program alone from Excel, it works fine: Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select However, when I add this to another program which, again works fine as it is, the program listed above doesnt run properly it will activate the Window named in AppActivate, 'Staff Management' , but the Call subroutines dont run - it just 'freezes' I add in the following to the end of the previous routine (which works fine alone) to get the focus back to Excel before running it with: Appactivate Vantage Interactive Advanced.xls With ThisWorkbook.Sheets (NEWTSform) Appactivate Staff Management Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select End with End Sub It errors here, usually with End sub highlighted with an Expected End with without with or End Select without Select or something similar!. Where am I going run it must be possible to do, if they run alone independently, they should run end-on-end. Ive tried various combinations of code to reclaim the focus back to Excel - but it still errors! Many thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting focus back to Excel using Sendkeys statements
Halim,
Many thanks for your suggestion - which I tried, but am still getting the same error - End sub highlighted and an error comment of "Expected 'End with'" I guess it's something to do with the AppActive code switching focus - I'm showing below (with my commented notes) the end of the previous (working OK) subroutine and this one: 'CLOSE WINDOW DoEvents SendKeys "%fc" 'activate SMI window and input as appropriate D or E info: AppActivate "Microsoft Excel - Vantage Interactive Advanced tm" With ThisWorkbook.Sheets("NEWTSform") AppActivate "Staff Management" Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder Case 3 Call inputSMIEtonewtsorder End Select End With End Sub Many thanks! "Zakynthos" wrote: Hi, When I run the following program alone from Excel, it works fine: Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select However, when I add this to another program which, again works fine as it is, the program listed above doesnt run properly it will activate the Window named in AppActivate, 'Staff Management' , but the Call subroutines dont run - it just 'freezes' I add in the following to the end of the previous routine (which works fine alone) to get the focus back to Excel before running it with: Appactivate Vantage Interactive Advanced.xls With ThisWorkbook.Sheets (NEWTSform) Appactivate Staff Management Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select End with End Sub It errors here, usually with End sub highlighted with an Expected End with without with or End Select without Select or something similar!. Where am I going run it must be possible to do, if they run alone independently, they should run end-on-end. Ive tried various combinations of code to reclaim the focus back to Excel - but it still errors! Many thanks! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting focus back to Excel using Sendkeys statements
Hi Zakynthos
Might not be totally relavent but the syntax is AppActivate not Appactivate "Zakynthos" wrote: Hi, When I run the following program alone from Excel, it works fine: Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select However, when I add this to another program which, again works fine as it is, the program listed above doesnt run properly it will activate the Window named in AppActivate, 'Staff Management' , but the Call subroutines dont run - it just 'freezes' I add in the following to the end of the previous routine (which works fine alone) to get the focus back to Excel before running it with: Vantage Interactive Advanced.xls With ThisWorkbook.Sheets (NEWTSform) Appactivate Staff Management Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select End with End Sub It errors here, usually with End sub highlighted with an Expected End with without with or End Select without Select or something similar!. Where am I going run it must be possible to do, if they run alone independently, they should run end-on-end. Ive tried various combinations of code to reclaim the focus back to Excel - but it still errors! Many thanks! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting 'focus' back to Excel using Sendkeys statements
Do you really want AppActivate ?
You seem to be activating workbooks not other applications. Check the Help for AppActivate. What is this supposed to do ? AppActivate "Staff Management" And where are the keys being sent with SendKeys ? It is a very unreliable method. NickHK "Zakynthos" wrote in message ... Halim, Many thanks for your suggestion - which I tried, but am still getting the same error - End sub highlighted and an error comment of "Expected 'End with'" I guess it's something to do with the AppActive code switching focus - I'm showing below (with my commented notes) the end of the previous (working OK) subroutine and this one: 'CLOSE WINDOW DoEvents SendKeys "%fc" 'activate SMI window and input as appropriate D or E info: AppActivate "Microsoft Excel - Vantage Interactive Advanced tm" With ThisWorkbook.Sheets("NEWTSform") AppActivate "Staff Management" Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder Case 3 Call inputSMIEtonewtsorder End Select End With End Sub Many thanks! "Zakynthos" wrote: Hi, When I run the following program alone from Excel, it works fine: Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select However, when I add this to another program which, again works fine as it is, the program listed above doesn't run properly - it will activate the Window named in AppActivate, 'Staff Management' , but the 'Call' subroutines don't run - it just 'freezes' I add in the following to the end of the previous routine (which works fine alone) to get the 'focus' back to Excel before running it with: Appactivate "Vantage Interactive Advanced.xls" With ThisWorkbook.Sheets ("NEWTSform") Appactivate "Staff Management" Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select End with End Sub It errors here, usually with End sub highlighted with an "Expected 'End with without with' or 'End Select' without 'Select' or something similar!. Where am I going run - it must be possible to do, if they run alone independently, they should run end-on-end. I've tried various combinations of code to reclaim the 'focus' back to Excel - but it still errors! Many thanks! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting 'focus' back to Excel using Sendkeys statements
Nick,
Your questions helped me clarify what I was doing - AppActivate was fine, I did activate the windows I needed and the code ran Ok as an 'application.run ' Once again, may thanks - I don't think I'd have got the solution without you. Best wishes, Tony "NickHK" wrote: Do you really want AppActivate ? You seem to be activating workbooks not other applications. Check the Help for AppActivate. What is this supposed to do ? AppActivate "Staff Management" And where are the keys being sent with SendKeys ? It is a very unreliable method. NickHK "Zakynthos" wrote in message ... Halim, Many thanks for your suggestion - which I tried, but am still getting the same error - End sub highlighted and an error comment of "Expected 'End with'" I guess it's something to do with the AppActive code switching focus - I'm showing below (with my commented notes) the end of the previous (working OK) subroutine and this one: 'CLOSE WINDOW DoEvents SendKeys "%fc" 'activate SMI window and input as appropriate D or E info: AppActivate "Microsoft Excel - Vantage Interactive Advanced tm" With ThisWorkbook.Sheets("NEWTSform") AppActivate "Staff Management" Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder Case 3 Call inputSMIEtonewtsorder End Select End With End Sub Many thanks! "Zakynthos" wrote: Hi, When I run the following program alone from Excel, it works fine: Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select However, when I add this to another program which, again works fine as it is, the program listed above doesn't run properly - it will activate the Window named in AppActivate, 'Staff Management' , but the 'Call' subroutines don't run - it just 'freezes' I add in the following to the end of the previous routine (which works fine alone) to get the 'focus' back to Excel before running it with: Appactivate "Vantage Interactive Advanced.xls" With ThisWorkbook.Sheets ("NEWTSform") Appactivate "Staff Management" Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select End with End Sub It errors here, usually with End sub highlighted with an "Expected 'End with without with' or 'End Select' without 'Select' or something similar!. Where am I going run - it must be possible to do, if they run alone independently, they should run end-on-end. I've tried various combinations of code to reclaim the 'focus' back to Excel - but it still errors! Many thanks! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting focus back to Excel using Sendkeys statements
Steve,
Yes, of course, a typo, but nevertheless I'm grateful to you for pointing it out - yours and the other helpful comments helped me focus on my errors - now all works perfectly! Once again, thanks for your help. Best wishes, Tony "steve_doc" wrote: Hi Zakynthos Might not be totally relavent but the syntax is AppActivate not Appactivate "Zakynthos" wrote: Hi, When I run the following program alone from Excel, it works fine: Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select However, when I add this to another program which, again works fine as it is, the program listed above doesnt run properly it will activate the Window named in AppActivate, 'Staff Management' , but the Call subroutines dont run - it just 'freezes' I add in the following to the end of the previous routine (which works fine alone) to get the focus back to Excel before running it with: Vantage Interactive Advanced.xls With ThisWorkbook.Sheets (NEWTSform) Appactivate Staff Management Sheets("NEWTSform").Select Select Case Range("H19").Value Case 4 Call inputSMIDtonewtsorder End Select Select Case Range("H19").Value Case 3 Call inputSMIEtonewtsorder End Select End with End Sub It errors here, usually with End sub highlighted with an Expected End with without with or End Select without Select or something similar!. Where am I going run it must be possible to do, if they run alone independently, they should run end-on-end. Ive tried various combinations of code to reclaim the focus back to Excel - but it still errors! Many thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel charts do not update when using SendKeys | Charts and Charting in Excel | |||
| How do I sendkeys to Interner Explorer from Excel? | Excel Programming | |||
| sendkeys examples in excel | Excel Programming | |||
| How Can I Use SendKeys in MAC EXCEL? | Excel Programming | |||
Linear Mode
