Excel - VBA to set focus back to exel

Asked By Waxaholic on 14-Jan-08 10:05 PM
I am launching an external application from excel and need to obtain
control of excel after the external program launches. This is all done
via VBA. VBA wants to wait for the installation to finish before
giving me excel back. I need to overcome this and have immediate
control of excel so i can close the workbook. Any ideas.

thanks

Brian




RB Smissaert replied on 14-Jan-08 06:00 PM
Try this:

Option Explicit
Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As
Long) As Long

Sub XLFocus()

Dim lExcelHwnd As Long

lExcelHwnd = FindWindow("XLMAIN", Application.Caption)
SetForegroundWindow lExcelHwnd

End Sub


RBS
Dave Peterson replied on 14-Jan-08 06:37 PM
Untested...

AppActivate Application.Caption


--

Dave Peterson
Waxaholic replied on 14-Jan-08 10:05 PM
Thanks to all. I have tried both recommendations above and they do
work, however, they are real slow (about 30sec or more) in returning
focus. This is way too long and would allow the end user to screw
things up. I am trying to get an instantaneous return of focus as soon
as the external app launches.

What i have is some vba that checks for an update to the excel
workbook. It then allows the user to download the update and the
additional option of installing it. This is where i launch the
installer and want to return to excel and close the workbook before
the user can continue with the installer.
Dave Peterson replied on 14-Jan-08 08:04 PM
I do not have another suggestion.


--

Dave Peterson
RB Smissaert replied on 15-Jan-08 02:38 AM
Maybe you could post your code.

RBS
Waxaholic replied on 17-Jan-08 06:33 AM
Version 1:
Sub Launcher()
Dim lExcelHwnd As Long

lExcelHwnd = FindWindow("UMR Creator v0_32wu", Application.Caption)

Shell ThisWorkbook.Path & "\" & Sheets("MenuSheet").Range("K2") &

SetForegroundWindow lExcelHwnd

End Sub

Version 2:
----------------------------------------------------
Sub Launcher2()

Dim MyAppID, ReturnValue
Dim UMRCID As Long

MyAppID = ("UMR Creator v0_32wu")

UMRCID = Shell(ThisWorkbook.Path & "\" &
Sheets("MenuSheet").Range("K2") & ".exe", 6)
AppActivate MyAppID, 0

End Sub


Both of these work for me but the delay coming back to excel is way
too long.



On Jan 15, 1:38 am, "RB Smissaert" <bartsmissa...@blueyonder.co.uk>
RB Smissaert replied on 15-Jan-08 08:32 AM
A few things:

What is this: "UMR Creator v0_32wu"  ?
Did you try with "XLMAIN"  ?

Try a DoEvents before SetForegroundWindow lExcelHwnd


RBS
Dave Peterson replied on 15-Jan-08 09:37 AM
In launcher2, use this:
AppActivate Application.Caption
(do not change it)




--

Dave Peterson
Waxaholic replied on 17-Jan-08 06:35 AM
Thanks to both of you but i still get the same result in slowness.

The "UMR Creator v0_32wu" is the window handle for the excel workbook.
I played with this and tried using XLMAIN, as well as a few different
variations in code and no speed increase. I am thinking of another
approach like launching a helper xls file with the Launcher code in
it. This would allow me to close the existing workbook without
problem. I'll report back if it works.