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!

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!

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!

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!

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!

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!

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!