AppActivate from Excel with VBA

AppActivate will do what you want but you have to have the exact window title or task ID as returned from of the Shell function in order for it to be effective.

How are you getting the window title or task ID now?

Kevin

I believe I've tried both the window title and the task id, but i could just be getting the syntax wrong.

What would the syntax be if the application was microsoft word?

The syntax is the same regardless of the application. The parameter has to be a valid window title or task ID as returned from the Shell function.

AppActivate "My Word Document"

Kevin

I've tried this...

Dim myTaskID As Long
myTaskID = Shell("C:\Program Files\Microsoft Office\OFFICE11\winword.exe")
AppActivate myTaskID

but it didn't work

We can handle this more easily ;-)

Shell "C:\Program Files\Microsoft Office 2003\OFFICE11\winword.exe", vbNormalFocus

Kevin

hmm, methinks that will LAUNCH Word, not simply bring an already existing instance of Word into focus

Correct.

You can't bring an existing instance into focus without knowing either a window title or the task ID. And neither of those are obtainable without some magical API calls.

Kevin

alright, lay it on me then... i do have to do this but i didn't want to use the the win api. c'est la vie

Ummm...API = Win API. Ain't no way around it.

For what it's worth, in the gazillion years I've been hacking Excel I have never heard anyone say that API calls increase a workbooks size. It just isn't so.

So, just what are you trying to do?

Kevin

i need the api call that brings a running app into focus when excel has the focus... i've seen it (even on EE i believe) but i'd rather you make sure it was picture perfect for me =)

I need more information. What running application? What document title? Did you execute the application? How?

Kevin

bfreescott,

My solution as follows:

Sub test_focus()
Dim myTaskID As Long
z_path = "C:\test.doc"
myTaskID = Shell("winword.exe " & z_path, 1)
AppActivate myTaskID
End Sub

Select allOpen in new window

Hi Kevin,

There is no document title and i'm actually trying to bring a subform to the foreground (kind of like if you were to go to Tools, Options in Excel and you would get a dialog with it's own window title.

Is there a way to simply loop through all the open windows with an api and debug.print the window titles so i can find the correct one?

That's what I was attempting here:

Option Explicit
Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" ( _
ByVal lpClassName As Any, ByVal lpWindowName As Any) As Long

Private Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long

Sub l()
   Dim hWnd As Long
   Do While hWnd > -1
   hWnd = FindWindow(vbNullString, Application.Caption)
   Debug.Print hWnd
   Loop
   'SetForegroundWindow hWnd
End Sub

Here is some code that may be what you need.

Private Const HWND_TOPMOST = -1
Private Const SWP_SHOWWINDOW = &H40
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1
Private Const SW_SHOW = 5
Private Const SW_RESTORE = 9

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
      ByVal lpClassName As String, _
      ByVal lpWindowName As String _
   ) As Long
   
Private Declare Function GetWindowThreadProcessId Lib "user32" ( _
      ByVal hWnd As Long, _
      lpdwProcessId As Long _
   ) As Long
   
Private Declare Function AttachThreadInput Lib "user32" ( _
      ByVal idAttach As Long, _
      ByVal idAttachTo As Long, _
      ByVal fAttach As Long _
   ) As Long
   
Private Declare Function GetForegroundWindow Lib "user32" () As Long

Private Declare Function SetForegroundWindow Lib "user32" ( _
      ByVal hWnd As Long _
   ) As Long
   
Private Declare Function IsIconic Lib "user32" ( _
      ByVal hWnd As Long _
   ) As Long
   
Private Declare Function ShowWindow Lib "user32" ( _
      ByVal hWnd As Long, _
      ByVal nCmdShow As Long _
   ) As Long
   
Declare Function SetWindowPos Lib "user32" ( _
      ByVal hWnd As Long, _
      ByVal hWndInsertAfter As Long, _
      ByVal X As Long, _
      ByVal Y As Long, _
      ByVal cx As Long, _
      ByVal cy As Long, _
      ByVal uFlags As Long _
   ) As Long

Sub ActivateApplication( _
      ByVal ApplicationWindowName As String _
   )

   Dim hWnd As Long
   Dim Result As Long

   hWnd = FindWindow(ApplicationWindowName, vbNullString)
   ForceForegroundWindow hWnd

End Sub

Public Function ForceForegroundWindow( _
      ByVal hWnd As Long _
   ) As Boolean

   Dim ThreadID1 As Long
   Dim ThreadID2 As Long
   Dim Result As Long
   
   ' Don't do anything is already in forground
   If hWnd = GetForegroundWindow() Then
      ForceForegroundWindow = True
   Else
      ' Get thread IDs of current foreground window and target window
      ThreadID1 = GetWindowThreadProcessId(GetForegroundWindow, ByVal 0&)
      ThreadID2 = GetWindowThreadProcessId(hWnd, ByVal 0&)
      ' By sharing input state, threads share their concept of the active window
      If ThreadID1 <> ThreadID2 Then
         AttachThreadInput ThreadID1, ThreadID2, True
         Result = SetForegroundWindow(hWnd)
         AttachThreadInput ThreadID1, ThreadID2, False
      Else
         Result = SetForegroundWindow(hWnd)
      End If
      ' Restore and repaint
      If IsIconic(hWnd) Then
         ShowWindow hWnd, SW_RESTORE
      Else
         ShowWindow hWnd, SW_SHOW
      End If
      ' SetForegroundWindow return accurately reflects success
      ForceForegroundWindow = CBool(Result)
   End If
   
End Function

Kevin

i think i see why all the vars in apis are typed as L O N G...

so, this line looks familiar: hWnd = FindWindow(ApplicationWindowName, vbNullString)

what does it do in your code?

Hey Kevin

I ran this and nothing happened (kinda expected).  I think you really need to specify the application you are trying to manipulate.  That's they whole problem here.  I don't know how to find things like Window Titles or Task IDs.  That's why I was attempting to loop through all running applications to determine some of those values.

bfreescott,

Have you changed your question?

I submitted a working solution above and you did not confirm if it worked for you.

You don't find windows using application names, you find them using the window titles. Kind of stupid but that's the way it works. And, given that fact, it makes it really difficult to do this when you don't know the window title!

Let's start from the beginning. What the heck are you doing? Why do you want to bring a Word document to the foreground? How to do know it's even there? What if there are 10 Word documents open? None? Did you launch it with your code? Or are you just looking for random Word documents?

Kevin

experts1,

We already did the Shell->Task ID route above. Didn't work. Still not clear what the Asker is doing.

Kevin

Let's start from the beginning. What the heck are you doing?
I don't know anymore.  I didn't realize this question would be so challenging.  Bringing another process to the foreground is like simulating alt-tab.  Doesn't seem like it would require and 20-page long api call, but maybe it does.
Why do you want to bring a Word document to the foreground?
I have a word document with a list of all the prices of tea in China and I want to copy them to a running (modeless) userform.
How to do know it's even there?
Because I opened it by double-clicking it's icon on my windows desktop.
What if there are 10 Word documents open?
 Is there a way to even open 10 concurrent instances of the Options dialog in Word?
 
Expert1, your solution was great! For someone else's question.

>Expert1, your solution was great! For someone else's question.

Oh, that's mean! Don't make me put my admin hat on!

>Because I opened it by double-clicking it's icon on my windows desktop.
>I have a word document with a list of all the prices of tea in China and I want to copy them to a running (modeless) userform.

OK, you have two manual events here. What's wrong with a third?

>Is there a way to even open 10 concurrent instances of the Options dialog in Word?

WTF! What does the Options dialog have to do with copying text from the Word document?

Does the Word document always have the same file name?

Kevin

I'm not trying to be mean or disrespectful to anyone. I just don't like it when people don't read my question and then insist that I accept their "solution".

OK, you have two manual events here. What's wrong with a third?
Believe me, I'm leaning toward that with each passing moment.
WTF! What does the Options dialog have to do with ...
If you had read my 12:45 post, you would have seen that I was using the Options dialog as an example.
Honestly, I was hoping that you could post an example proc that I could learn from and apply to my own requirements - that's typically what I get from you.  =)  Something along the lines of bringing Internet Explorer to the foreground of a modeless userform, so you could grab this wonderful thread and put it in your textbox.
I don't really have time to discuss the virtues of hide versus unload; where do you get the time to even have those debates? lol
Thanks again Kevin!

Oh dear, you didn't answer the one special question:

Does the Word document always have the same file name?

And one more:

If so, what is it?

If not, you're screwed.

Kevin

Well, let me see if I can answer that in a way that doesn't end up in me being screwed. =)
I believe when you open Word, you don't necessarily have to have an open document.
In fact, if you open Word, then close the "New" document, you are simply left with the application, whose caption reads: "Microsoft Word".

I believe the caption always reads that way, but the document title is added when you have a document open.

What I wonder is if I can use a wild card in place of whatever may be added to the application caption to capture the window title.  If I can just capture the window title, I think I'm home free.

Hey buddy - ok, looks like we found a solution at the same time.  Below is my working proc for bring Internet Explorer's Internet Options child window into focus from within Excel (I understand that it will arbitrarily grab one if there is more than one Options child window open, but how often is that going to be the case???  And in my environment, I'm not sure it's even possible):

Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal ClassName As String, ByVal WindowName As String) As Long
Private Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long

Sub test()
   Dim hWnd As Long
   hWnd = FindWindow(vbNullString, "Internet Options")
   Debug.Print hWnd
   SetForegroundWindow hWnd
End Sub

I appreciate your help on this and I'll give you the points with one more answer:
Of course it's my code that launches the child window, why would I have manual steps? lol
I read somewhere that if you do launch the window programmatically, you have to allow time for it to open before attempting to access it (like with the above api).  Do you have that snippet handy?

Have fun on your date!  I'll be watching the BCS Nat'l Champ.

I might have a better solution.  Use Word automation to open a copy of the file in a word object variable and transfer the contents of the document into your VBA variables.  As long as the document isn't opened exclusively, you don't have to worry about finding another application.  Besides, it sounds like you might be thinking about using the clipboard as the transfer mechanism, which isn't as reliable as the word automation approach.

Thanks aikimark

That's actually the first approach I took to do this.  The application is actually Lotus Notes and I'm opening the user's address book (a child window) for them to select from.  It was far too difficult with the different views in Notes to get the values they would need via the automation classes, so I just went with the UI route and it was far simpler.  The only issue seems to be that because I am opening that child window programmatically, I cannot then access it programmatically.  I've tried using the Sleep API function and a host of other API approaches, but nothing works.  Everything works to perfection except the part where I have to alt-tab to the window I just opened.  sigh...

Their address book is in a Word document?!?
What are they selecting from the Word document?
How is their selection supposed to get into your Notes VB code?

Word was just an example

Notes has an automation object allowing you to access its contents.

>I read somewhere that if you do launch the window programmatically, you have to allow time for it to open before attempting to access it (like with the above api).  Do you have that snippet handy?

The best way to pause code execution is to provide a mechanism that gives the parent application such as Excel or Word opportunities to handle events as well as other operating system tasks. The routine below provides both and allows a pause of as little as a hundredth of a second.

Note that the declaration of the Sleep API function has to be placed above all other routines in the module.

[Begin Code Segment]

Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)

Public Sub Pause( _
      ByVal Seconds As Single, _
      Optional ByVal PreventVBEvents As Boolean _
   )

' Pauses for the number of seconds specified. Seconds can be specified down to
' 1/100 of a second. The Windows Sleep routine is called during each cycle to
' give other applications time because, while DoEvents does the same, it does
' not wait and hence the VB loop code consumes more CPU cycles.

   Const MaxSystemSleepInterval = 25 ' milliseconds
   Const MinSystemSleepInterval = 1 ' milliseconds
   
   Dim ResumeTime As Double
   Dim Factor As Long
   Dim SleepDuration As Double
   
   Factor = CLng(24) * 60 * 60
   
   ResumeTime = Int(Now) + (Timer + Seconds) / Factor
   
   Do
      SleepDuration = (ResumeTime - (Int(Now) + Timer / Factor)) * Factor * 1000
      If SleepDuration > MaxSystemSleepInterval Then SleepDuration = MaxSystemSleepInterval
      If SleepDuration < MinSystemSleepInterval Then SleepDuration = MinSystemSleepInterval
      Sleep SleepDuration
      If Not PreventVBEvents Then DoEvents
   Loop Until Int(Now) + Timer / Factor >= ResumeTime
   
End Sub

[End Code Segment]

Note that the expression

   Int(Now) + Timer / Factor

is used to create a time that both handles midnight crossovers and is accurate to within 1/100 of a second. Just the Timer function alone is accurate to within 1/100 of a second but does not handle midnight crossovers. The Now function is only accurate to within about 1/4 of a second.

The DoEvents call is used to give the managed environment such as Excel or Word opportunities to handle events and do other work. But DoEvents only works within the managed environment and can still consume a considerable amount of resources without some other throttling mechanism. By also using the Windows Sleep API call the Windows operating system is given an opportunity to let other processes run. And, since the code is doing nothing but waiting, it is the appropriate thing to do.

Often the task involves waiting for an asynchronous task to complete such as a web query. To use the above routine while waiting for such a task to compete, two time durations are needed: the total amount of time to wait until it can be assumed that a failure has occurred in the other task, and the amount of time to wait between checks that the other task has completed. Determining how long to wait until giving up requires consideration of the longest possible time that the task could reasonably take and how long the user is willing to wait for that task to complete - wait too long and the user gets frustrated, don't wait long enough and the risk increases of falsely assuming an error occurred when it didn't. This duration is the more difficult to determine of the two. The second time, the duration between checks for completion, is easier to determine. This duration should be long enough to not consume unnecessary CPU cycles doing the check, but short enough to respond quickly when the status of the asynchronous task changes. A duration of between a quarter of a second and one second is usually reasonable. The sample code below illustrates how to wait for an asynchronous task to complete that usually finishes in less than 10 seconds.

   Dim TimeoutTime As Date
   TimeoutTime = Now() + TimeSerial(0, 0, 30) ' Allow 30 seconds for the asynchronous task to complete before assuming it failed
   Do While Now() < TimeoutTime And Not IsTaskComplete
      Pause 0.5 ' Pause half a second to allow the ashyncronous task (and the rest of the environment) to do work
   Loop

The above example uses a function named IsTaskComplete to determine if the asynchronous task completed. The function can do anything such as checking if a cell changed, checking if a control's property is set, or checking if a file exists.


Other techniques for pausing code execution and the problems with each are listed below. These should all be avoided in any well-designed application.

Wait Method (VBA only):

   Application.Wait Now() + TimeSerial(0, 0, 10)

The Wait method suspends all application activity and may prevent other operations from getting processing time while Wait is in effect. However, background processes such as printing and recalculation continue. The net effect of pausing using the Wait method is to shut down the application (e.g. Excel) event handling and slow or stop other applications. This method does not allow any fractional seconds to be used.

Windows Sleep:

   Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
   Sleep 10000

Using the Sleep Windows API call is system friendly by allowing all other processes to get processing time but it effectively shuts down the parent application. This is generally not a good idea as an application should always be responsive to user requests, even if the application is waiting for an asynchronous task to complete. Extended sleep periods can also cause problems when Windows is sending out system events such as system shut down notifications.

DoEvents Loop

   TimeoutTime = Now() + TimeSerial(0, 0, 10)
   Do
      DoEvents
   Loop Until Now > TimeoutTime

Performing a DoEvents loop to pause gives the parent application a chance to handle events but, because there is no pause between DoEvents calls, virtually all available processing time is dedicated to the loop and nothing else which means this is not a good way to pause code execution. This method does not allow any fractional seconds to be used.

Basic Loop without DoEvents

   TimeoutTime = Now() + TimeSerial(0, 0, 10)
   Do
   Loop Until Now > TimeoutTime

A tight loop without a DoEvents call effectively brings the workstation to a halt until the loop exits. This is the worst technique to pause code execution. This method does not allow any fractional seconds to be used.

There are other, more sophisticated, techniques that monitor event queues and other system resources but the net result is the same as a simple loop with both a DoEvents and a Sleep. As long as some other throttling mechanism is used such as the Windows Sleep function, DoEvents will consume very little resources as all it does is look for any pending events and then either processes those events or returns immediately to the caller.

Kevin

Thanks Kevin - after studying this problem with me, are you convinced it's a wait/sleep issue??  I used Sleep with an argument of 10000 which is complete overkill, but it still did not help.  I can tab over to the child window I opened pretty much immediately after the commandbutton that opens it is pressed.

I wonder if the real issue isn't something else entirely.

I think I'll create a seperate dummy/softball question worth 500 points so you can get what you really deserve for helping me on this one.

Using the Sleep command is deadly. It locks Excel up for the duration you specify.

You never told me exactly what you are doing. You have talked about modal Word dialogs, Word documents, and other assorted applications. Just what, exactly, are you doing? And this is to save, what, a couple of keystrokes how often?

Kevin

Hey Kevin,

Ok, so 2 things:

1) The explanation of what I'm doing: I have an Excel userform (launched modeless for other purposes than what we've discussed) and one of the commandbuttons launches a child window in another running application (it launches the local address book of lotus notes).  Lotus Notes is running (and if it isn't the proc starts it and opens the address book).  All of that works perfectly.

Once I have the address book open, I can select names from it to populate a listbox on my userform.
That too works perfectly.

The part that doesn't work is the one annoying piece in between there: I must Activate (not focus) the address book to bring it to the front of my Excel userform.

Now for the second thing (and the exciting / confusing part):
2) As I have been working with other parts of the code tonight, I ran the procedure that opens the address book and when it ran, the address book opened, activated, and took the focus!!!  I tried it again, but it did not do it again.  It has only done that once in probably over 100 executions.  Bear in mind that I do not currently have any code that attempts to do that included (e.g. appactivate, forcefocus, etc.) in my proc.

That does lead me to believe that some sort of timing issue is playing a role here.

If there is a child form inside a parent form, is the child form maximized?
Was the child form maximized when the AppActivate worked?

the child form has never been maximized

>Timing

I agree completely with your assessment.

Try different pause durations starting with a long one. If the long one doesn't work then you may have just experienced an anomaly.

The trick to the focus setting is knowing the process ID or the window title. In this case you know neither and I believe you are flying blind and experiencing a little luck now and then. One of the cool things about the code I posted above is it works with partial strings like "*Word* which will activate the first window it finds with "Word" anywhere in the window title. I suggest revisiting that code and evaluating just what you need to use to search for the Notes window that you want to activate.

For grins, just what is the window title? Does it change with each instance?

Kevin

> Try different pause durations

I've used Sleep 10000.  I thought that was a pretty long pause.  I've also used different variations of app.wait.  I don't think a pause is the answer.  I can immediately flip over (alt-tab) to the window after executing the code that launches it, so I don't think it's that.

It could be some sort of threading-issue, in which case I'm screwed since you cannot launch multiple threads with vba.

I actually do know the window title "Select Names" and if I launch the window from within lotus notes, I can run a one-line appactivate statement which will bring it to the front of Excel.

Curious. I see what you mean about timing. You can launch the application. You can bring the window to the front. You just can't do both at the same time.

Try using my Pause routine. That's about all I can suggest at this time. I'm stumped.

Kevin

Post your code. Maybe I might see something.

Kevin

How would you open the window NOT within Lotus Notes?

Hi Kevin,

Here is the code that opens the address book and populates the listbox with the user's selections:

Private Sub CommandButton3_Click()
'
    On Error GoTo ErrorHandler
    Dim Addresses() As String
    Dim i As Integer
    Set notesClient = CreateObject("Notes.NotesUiWorkspace")

    Addresses = notesClient.PICKLISTSTRINGS(0, True, "")

    If Addresses(i) = "" Then
        MsgBox "No Names Selected"
    Else
        For i = 0 To UBound(Addresses)
            UserForm5.ListBox1.AddItem (Addresses(i))
        Next i
    End If

Exit Sub
ErrorHandler:
    MsgBox "Error: " & Err.Number & " , " & Err.Description & Chr(13) & _
    "Procedure is: Form5, AddressBookA" & Chr(13) & ""
'
End Sub

Why do you want to activate the application when you are already able to automate pulling the information you want? Just start Notes, get the data, and close Notes.

Kevin

get what data?

Hi Kevin,

The line: Addresses = notesClient.PICKLISTSTRINGS(0, True, "")
opens the address book.

It is the "front door" / Notes UI approach to giving the user the ability to select from their address book.
The back door approach Notes Automation classes was way too complex and did not offer an interface for the user.

This approach is just about as simple as it gets (if I can just show the address book after I open it) LOL

>if I can just show the address book after I open it

This is what doesn't make sense. Your objective is to pull the data into Excel. Why are you still trying to show the Notes window? Just grab the data and be done with it.

Kevin

What happens if you DeActivate Excel?

I hadn't thought of that.  Since the address book is opened with the picklist statement, I imagine it will be the top window if i deactivated Excel.  I'll try it.

Thanks Kevin!  I was able to work with this and get it to work for me.  Can't get the focus back on my userform, but I'll figure that one out on my own.  You're a lifesaver!!!