AppActivate - workaround if app's title bar not known 100%
 

Trying to write an Excel macro that activates a currently running
application when that app's title-bar text isn't completely known. I've
been able to do this in Word, because Word VBA exposes the "Tasks"
collection, so I can loop through all running Tasks comparing their
..Name properties and activate the one I need, without necessarily
specifying its entire title bar text -- rather, by using InStr() to
check for a match. (See last para below if this is unclear.)

But it looks like Excel VBA doesn't know about the Tasks collection. Is
it called something else in Excel? Is there another way to get at it?

For Excel, the "other app" I need to switch to is almost always Word.
If our installation of Word were "normal" (ehh?), I could use
AppActivate. Problem is, AppActivate requires a match to the *first*
characters of the title-bar text, and in our office the title bar's text
is the active document's name *followed* by Microsoft Word, e.g.,
"MyFile.doc - Microsoft Word." (When running the macro, I want the
focus to switch to *whatever* file is open in Word, and that file
varies, so I can’t hard-code its name. That's why I need to use InStr()
and match only a portion of what's on the new app's title bar.)

I know there's a way to disable that filename-leads-the-titlebar
feature, but I’d rather keep it. Can I have my cake and eat it too?

If my first paragraph is unclear, the Word macro that accomplishes this is:

Dim TA As Task
For Each TA In Tasks
If InStr(TA.Name, "Smurf Delta") 0 Then
TA.Activate
Exit Sub
End If
Next TA

Many thanks for any clues.

-----------------------
Mark Tangard

AppActivate - workaround if app's title bar not known 100%
 

Mark,
It sounds like you need to check out Automation, then use GetObject on a
running instance of Word, or CreateObject (or New Word.Application) to start
your own.
Once you have that reference to the Word App, you can use Word's object
model as you wish.

e.g.
Dim MyWord As Word.Application
Dim TA as Word.Task

Set MyWord=New Word.Application

With MyWord
.Open MyFile.....
For Each TA In .Tasks
......etc

P.S. I do not the Word Object model well, but something like the above I
guess.

NickHK


"Mark Tangard" wrote in message
...
Trying to write an Excel macro that activates a currently running
application when that app's title-bar text isn't completely known. I've
been able to do this in Word, because Word VBA exposes the "Tasks"
collection, so I can loop through all running Tasks comparing their
.Name properties and activate the one I need, without necessarily
specifying its entire title bar text -- rather, by using InStr() to
check for a match. (See last para below if this is unclear.)

But it looks like Excel VBA doesn't know about the Tasks collection. Is
it called something else in Excel? Is there another way to get at it?

For Excel, the "other app" I need to switch to is almost always Word.
If our installation of Word were "normal" (ehh?), I could use
AppActivate. Problem is, AppActivate requires a match to the *first*
characters of the title-bar text, and in our office the title bar's text
is the active document's name *followed* by Microsoft Word, e.g.,
"MyFile.doc - Microsoft Word." (When running the macro, I want the
focus to switch to *whatever* file is open in Word, and that file
varies, so I can’t hard-code its name. That's why I need to use InStr()
and match only a portion of what's on the new app's title bar.)

I know there's a way to disable that filename-leads-the-titlebar
feature, but I’d rather keep it. Can I have my cake and eat it too?

If my first paragraph is unclear, the Word macro that accomplishes this
is:

Dim TA As Task
For Each TA In Tasks
If InStr(TA.Name, "Smurf Delta") 0 Then
TA.Activate
Exit Sub
End If
Next TA

Many thanks for any clues.

-----------------------
Mark Tangard

AppActivate - workaround if app's title bar not known 100%
 

Omigod, that's so simple! Thanks, Nick, worked like a charm. For
anyone looking for complete skeleton code, this works:

Sub SwitchToWord()
Dim MW As Object
' See if Word is running. GetObject called with no first argument
' returns a ref to a Word instance, or an error if Word isn't running
On Error Resume Next
Set MW = GetObject(, "Word.Application")
If Err.Number < 0 Then
MsgBox "Word is not running."
Err.Clear
Exit Sub
End If
MW.Application.Visible = True
MW.Parent.Windows(1).Visible = True
MW.Activate
Set MW = Nothing
End Sub


Mark


NickHK wrote:

Mark,
It sounds like you need to check out Automation, then use GetObject on a
running instance of Word, or CreateObject (or New Word.Application) to start
your own.
Once you have that reference to the Word App, you can use Word's object
model as you wish.

e.g.
Dim MyWord As Word.Application
Dim TA as Word.Task

Set MyWord=New Word.Application

With MyWord
.Open MyFile.....
For Each TA In .Tasks
.....etc

P.S. I do not the Word Object model well, but something like the above I
guess.

NickHK


"Mark Tangard" wrote in message
...

Trying to write an Excel macro that activates a currently running
application when that app's title-bar text isn't completely known. I've
been able to do this in Word, because Word VBA exposes the "Tasks"
collection, so I can loop through all running Tasks comparing their
.Name properties and activate the one I need, without necessarily
specifying its entire title bar text -- rather, by using InStr() to
check for a match. (See last para below if this is unclear.)

But it looks like Excel VBA doesn't know about the Tasks collection. Is
it called something else in Excel? Is there another way to get at it?

For Excel, the "other app" I need to switch to is almost always Word.
If our installation of Word were "normal" (ehh?), I could use
AppActivate. Problem is, AppActivate requires a match to the *first*
characters of the title-bar text, and in our office the title bar's text
is the active document's name *followed* by Microsoft Word, e.g.,
"MyFile.doc - Microsoft Word." (When running the macro, I want the
focus to switch to *whatever* file is open in Word, and that file
varies, so I can’t hard-code its name. That's why I need to use InStr()
and match only a portion of what's on the new app's title bar.)

I know there's a way to disable that filename-leads-the-titlebar
feature, but I’d rather keep it. Can I have my cake and eat it too?

If my first paragraph is unclear, the Word macro that accomplishes this

is:

Dim TA As Task
For Each TA In Tasks
If InStr(TA.Name, "Smurf Delta") 0 Then
TA.Activate
Exit Sub
End If
Next TA

Many thanks for any clues.

-----------------------
Mark Tangard