Switch Back to Excel

[Dave Martin]

Using Excel 2000, SR-1.
I have an Excel VBA Macro that copies charts from Excel to other
applications such as Word and Powerpoint. I can easily switch into Word or
powerpoint like this:

Set wdApp = Word.Application
wdApp.Activate

But I can't for the life of me figure out how to switch the focus back to
excel once I've activated Word. The only way I've seen to do it is to close
Word or Powerpoint.

Can someone advise how to switch the focus back to excel after switching to
another MS application?

xlApp.Activate

Does not work.

[Jan Karel Pieterse]

Hi,

Like this:

Option Explicit

Declare Function FindWindow32 Lib "User32"
Alias "FindWindowA" (ByVal lpClassName As String, ByVal
lpWindowName As String) As Long
Declare Function GetForegroundWindow Lib "user32.dll" ()
As Long
Declare Function SetActiveWindow Lib "user32.dll" (ByVal
hwnd As Long) As Long
Declare Function SetForegroundWindow Lib "user32.dll"
(ByVal hwnd As Long) As Long

Sub test()
Dim RetVal
Dim hWndP1 As Long
'Find the hWnd of the main Excel window
hWndP1 = FindWindow32("xlmain", Application.Caption)
RetVal = Shell("C:\WINDOWS\CALC.EXE", 1) ' Run
Calculator
SetActiveWindow hWndP1
SetForegroundWindow hWndP1
End Sub

Regards,

Jan Karel Pieterse
Excel TA/MVP

>.
>

[Jim Rech]

Windows doesn't approve of inactive applications switching the focus back to
themselves. You should know that, Dave<g>.

Try this:

Public Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As
Long, lpdwProcessId As Long) As Long
Public Declare Function AttachThreadInput Lib "user32" (ByVal idAttach As
Long, ByVal idAttachTo As Long, ByVal fAttach As Long) As Long
Public Declare Function GetForegroundWindow Lib "user32" () As Long
Public Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As
Long) As Long
Public Declare Function IsIconic Lib "user32" (ByVal hWnd As Long) As Long
Public Declare Function ShowWindow Lib "user32" (ByVal hWnd As Long, ByVal
nCmdShow As Long) As Long
Declare Function FindWindowA Lib "user32" (ByVal lpClassName As Long, ByVal

lpWindowName As String) As Long

Const SW_SHOW = 5
Const SW_RESTORE = 9

''Run this an then switch to another application.
Sub Test()
Application.OnTime Now + TimeValue("00:00:05"), "BringTotop"
End Sub

Sub BringToTop()
ForceForegroundWindow FindWindowA(0, Application.Caption)
End Sub

''All this is necessary in Windows 98/NT5 because MS disabled
SetForegroundWindow unless
''the caller is itself the foreground window.
''From Visual Basic Programmers Journal Feb 1999 pg 94
Function ForceForegroundWindow(ByVal hWnd As Long) As Boolean
Dim ThreadID1 As Long
Dim ThreadID2 As Long
Dim nRet As Long

If hWnd = GetForegroundWindow Then
ForceForegroundWindow = True
Else
ThreadID1 = GetWindowThreadProcessId(GetForegroundWindow, ByVal 0&)
ThreadID2 = GetWindowThreadProcessId(hWnd, ByVal 0&)
If ThreadID1 <> ThreadID2 Then
AttachThreadInput ThreadID1, ThreadID2, True
nRet = SetForegroundWindow(hWnd)
AttachThreadInput ThreadID1, ThreadID2, False
Else
nRet = SetForegroundWindow(hWnd)
End If
If IsIconic(hWnd) Then
ShowWindow hWnd, SW_RESTORE
Else
ShowWindow hWnd, SW_SHOW
End If
ForceForegroundWindow = CBool(nRet)
End If
End Function

--
Jim Rech
Excel MVP

[Tom Ogilvy]

ThisWorkbook.Application.Activate

possibly.

Regards,
Tom Ogilvy

"Dave Martin" <da...@spcorch.com> wrote in message
news:O3gHMP4vBHA.2112@tkmsftngp02...

[Jan Karel Pieterse]

Hi Jim,

I tried the code I posted (win98, xl2k) and get this:

- If XL is foreground & active, Excel main window stays
that way
- if VBE is open and active, it stays that way
- if all windows are minimized, the XL taskbar button
gets "pressed". Adding application.windowstate=xlmaximized
gets it (XL Main window) back up.

Regards,

Jan Karel Pieterse
Excel TA/MVP

>.
>

[Jim Rech]

Hi Jan Karel-

Your post didn't appear (to me) until after my post.

Your code runs fine but there is a difference between it and what I posted.
If I run this version of your code:

Sub a()
Application.OnTime Now + TimeValue("00:00:05"), "test"
End Sub

Sub test()
Dim RetVal
Dim hWndP1 As Long

hWndP1 = FindWindow32("xlmain", Application.Caption)

SetActiveWindow hWndP1
SetForegroundWindow hWndP1
End Sub

Excel just flashes in the taskbar after 5 seconds. With my code Excel is
forced to the top w/o flashing. If your code (or better, Tom's) does what
Dave wants then great. But I assume he would have tried these things since
I know him as a long time Excel developer. Hopefully we'll hear but either
way the technique in my post is not well known and is very powerful and
could be useful to others at some point if not Dave.

Jim

[Tom Ogilvy]

Jim,
You have posted this information in the past and I recall verifying it in
win98 SE. It has been a while, however, and I did not recall the key
discriminator here about the application excercising the command being or
not being the top level window. (This behavior extended into the
AppActivate command you also pointed out at the time as I recall. )

There have been several posts related to this lately and reported success in
those clouded the issue - guess I should have gone back and dug out your old
post. Thanks for "coming to the rescue" with a complete explanation and my
apologies to Dave Martin (who I don't know).

Regards,
Tom Ogilvy

"Jim Rech" <jar...@kpmg.com> wrote in message
news:#lTF116vBHA.2168@tkmsftngp04...

[Jan Karel Pieterse]

Hi Jim,

<<Excel just flashes in the taskbar after 5 seconds>>

Which is why I added application.windowstate=xlmaximized as the final
line of code.

But I do believe there will be situations where my code fails to do
exactly what is needed. I'll stow away yours for future reference...

regards,

Jan Karel Pieterse
Excel MVP