Currently, I am using the following code to activate a window
contatining data to copy from -

Windows("rwservlet").Activate


Only problem is sometimes there is additional info after rwservlet
(Ex: rwservlet [2], rwservlet [Read-Only], etc). How would I put in a
wildcard so that any rwservlet..... window would work with the above
code?

There may be an easier workaround... This workbook will ALWAYS have
one sheet in it named rwservlet. Is there a way to activate just that
sheet without having to reference the workbook it is in, since that
name is subject to change?

Matthew Dyer laid this down on his screen :
Currently, I am using the following code to activate a window
contatining data to copy from -

Windows("rwservlet").Activate


Only problem is sometimes there is additional info after rwservlet
(Ex: rwservlet [2], rwservlet [Read-Only], etc). How would I put in a
wildcard so that any rwservlet..... window would work with the above
code?

Try...

Function GetWkbNameFromWindow(TextIn As String) As String
Dim wnd As Window
For Each wnd In Application.Windows
If InStr(1, wnd.Caption, TextIn, vbTextCompare) 0 Then _
GetWkbNameFromWindow = wnd.Caption: Exit Function
Next
End Function

Example usage:

Sub DoStuff()
Dim sName As String
sName = GetWkbNameFromWindow("rwservlet")
If Not sName = "" Then Workbooks(sName).Activate
'...more code
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

GS used his keyboard to write :
If Not sName = "" Then Workbooks(sName).Activate

change to...

If Not sName = "" Then Application.Windows(sName).Activate

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Matthew Dyer used his keyboard to write :
There may be an easier workaround... This workbook will ALWAYS have
one sheet in it named rwservlet. Is there a way to activate just that
sheet without having to reference the workbook it is in, since that
name is subject to change?

No! You must always use fully qualified refs.

You may be able to set an object variable to that sheet when it's the
active sheet at some point prior.

Example:
In the declarations section of a standard module:
Dim wksServlet As Worksheet

In some procedure that opens the workbook containing this sheet:
Set WksServlet = ActiveSheet

To ref it later on:
WksServlet.Activate

OR
just act directly on the sheet without activating it at all:
WksServlet.Range("A1").Value = 123

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Mar 10, 2:37*pm, GS wrote:
GS used his keyboard to write :

If Not sName = "" Then Workbooks(sName).Activate

change to...

* If Not sName = "" Then Application.Windows(sName).Activate

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

sorry garry, but it isnt working.

For Each wnd In Application.Windows
If InStr(1, wnd.Caption, TextIn, vbTextCompare) 0 Then _
GetWkbNameFromWindow = wnd.Caption: Exit Function
Next

wnd comes back as Nothing and remains nothing throughout when the code
is run. I ran it with breaks. It also appears to be running only for
the 'active' window and not trying to pull data from any of the other
windows to find the match.

Matthew Dyer was thinking very hard :
On Mar 10, 2:37*pm, GS wrote:
GS used his keyboard to write :

If Not sName = "" Then Workbooks(sName).Activate

change to...

* If Not sName = "" Then Application.Windows(sName).Activate

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

sorry garry, but it isnt working.

For Each wnd In Application.Windows
If InStr(1, wnd.Caption, TextIn, vbTextCompare) 0 Then _
GetWkbNameFromWindow = wnd.Caption: Exit Function
Next

wnd comes back as Nothing and remains nothing throughout when the code
is run. I ran it with breaks. It also appears to be running only for
the 'active' window and not trying to pull data from any of the other
windows to find the match.

Are you using the function 'as posted'? It will only work if there's at
least 1 workbook open. If more then it steps through each window to
check if the string you passed is contained in its Caption. What are
you doing different that's making it NOT work?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
Matthew Dyer was thinking very hard :
On Mar 10, 2:37 pm, GS wrote:
GS used his keyboard to write :

If Not sName = "" Then Workbooks(sName).Activate

change to...

If Not sName = "" Then Application.Windows(sName).Activate

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

sorry garry, but it isnt working.

For Each wnd In Application.Windows
If InStr(1, wnd.Caption, TextIn, vbTextCompare) 0 Then _
GetWkbNameFromWindow = wnd.Caption: Exit Function
Next

wnd comes back as Nothing and remains nothing throughout when the
code
is run. I ran it with breaks. It also appears to be running only for
the 'active' window and not trying to pull data from any of the other
windows to find the match.

Are you using the function 'as posted'? It will only work if there's
at least 1 workbook open. If more then it steps through each window to
check if the string you passed is contained in its Caption. What are
you doing different that's making it NOT work?



Different instances of Excel, maybe?

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Mar 10, 3:14*pm, GS wrote:
Matthew Dyer was thinking very hard :





On Mar 10, 2:37 pm, GS wrote:
GS used his keyboard to write :

If Not sName = "" Then Workbooks(sName).Activate

change to...

If Not sName = "" Then Application.Windows(sName).Activate

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

sorry garry, but it isnt working.

For Each wnd In Application.Windows
* * If InStr(1, wnd.Caption, TextIn, vbTextCompare) 0 Then _
* * GetWkbNameFromWindow = wnd.Caption: Exit Function
* Next

wnd comes back as Nothing and remains nothing throughout when the code
is run. I ran it with breaks. It also appears to be running only for
the 'active' window and not trying to pull data from any of the other
windows to find the match.

Are you using the function 'as posted'? It will only work if there's at
least 1 workbook open. If more then it steps through each window to
check if the string you passed is contained in its Caption. What are
you doing different that's making it NOT work?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

copy/paste of the function with no modifications. I think Clif may be
on to something about it being a different instance of excel...
reccomendations?

Matthew Dyer laid this down on his screen :
On Mar 10, 3:14*pm, GS wrote:
Matthew Dyer was thinking very hard :





On Mar 10, 2:37 pm, GS wrote:
GS used his keyboard to write :

If Not sName = "" Then Workbooks(sName).Activate
change to...

If Not sName = "" Then Application.Windows(sName).Activate
--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
sorry garry, but it isnt working.

For Each wnd In Application.Windows
* * If InStr(1, wnd.Caption, TextIn, vbTextCompare) 0 Then _
* * GetWkbNameFromWindow = wnd.Caption: Exit Function
* Next

wnd comes back as Nothing and remains nothing throughout when the code
is run. I ran it with breaks. It also appears to be running only for
the 'active' window and not trying to pull data from any of the other
windows to find the match.

Are you using the function 'as posted'? It will only work if there's at
least 1 workbook open. If more then it steps through each window to
check if the string you passed is contained in its Caption. What are
you doing different that's making it NOT work?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

copy/paste of the function with no modifications. I think Clif may be
on to something about it being a different instance of excel...
reccomendations?

The ref to 'Application' in the function is the current instance of
Excel in which the code is running. The function won't work on other
instances running at the same time. Not sure why this would be an issue
since most users use the same instance of Excel for all open workbooks.
In order for me to have multiple instances open at the same time it
would be a deliberate action on my part to do so. (Not withstanding
automated instances)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
Matthew Dyer laid this down on his screen :
On Mar 10, 3:14 pm, GS wrote:
Matthew Dyer was thinking very hard :





On Mar 10, 2:37 pm, GS wrote:
GS used his keyboard to write :

If Not sName = "" Then Workbooks(sName).Activate
change to...

If Not sName = "" Then Application.Windows(sName).Activate
--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
sorry garry, but it isnt working.

For Each wnd In Application.Windows
If InStr(1, wnd.Caption, TextIn, vbTextCompare) 0 Then _
GetWkbNameFromWindow = wnd.Caption: Exit Function
Next

wnd comes back as Nothing and remains nothing throughout when the
code
is run. I ran it with breaks. It also appears to be running only
for
the 'active' window and not trying to pull data from any of the
other
windows to find the match.

Are you using the function 'as posted'? It will only work if there's
at
least 1 workbook open. If more then it steps through each window to
check if the string you passed is contained in its Caption. What are
you doing different that's making it NOT work?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted
text -

- Show quoted text -

copy/paste of the function with no modifications. I think Clif may
be
on to something about it being a different instance of excel...
reccomendations?

The ref to 'Application' in the function is the current instance of
Excel in which the code is running. The function won't work on other
instances running at the same time. Not sure why this would be an
issue since most users use the same instance of Excel for all open
workbooks. In order for me to have multiple instances open at the same
time it would be a deliberate action on my part to do so. (Not
withstanding automated instances)



It's easy enough to discover if there are multiple instances running by
using Task Manager. On XP, my normal way to launch Task Manager is
Ctrl+Alt+Del which brings up a dialog and one of the choices is Task
Manager.

When Task Manager opens, select the Processes (NOT Applications) tab and
look for multiple entries for EXCEL.EXE.

If there's only one, there is only one instance running on that
computer.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin presented the following explanation :
It's easy enough to discover if there are multiple instances running by using
Task Manager. On XP, my normal way to launch Task Manager is Ctrl+Alt+Del
which brings up a dialog and one of the choices is Task Manager.

When Task Manager opens, select the Processes (NOT Applications) tab and look
for multiple entries for EXCEL.EXE.

If there's only one, there is only one instance running on that computer.

Regardless of how many instances are running, the ref to Application
only applies the the instance in which the code is executing. Each
instance is the parent of its open files, and so open files in some
other instance aren't affected by any other instance running. Even if 2
instances have the same file open, the instance running the code only
works on objects of that instance.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
Clif McIrvin presented the following explanation :
It's easy enough to discover if there are multiple instances running
by using Task Manager. On XP, my normal way to launch Task Manager is
Ctrl+Alt+Del which brings up a dialog and one of the choices is Task
Manager.

When Task Manager opens, select the Processes (NOT Applications) tab
and look for multiple entries for EXCEL.EXE.

If there's only one, there is only one instance running on that
computer.

Regardless of how many instances are running, the ref to Application
only applies the the instance in which the code is executing. Each
instance is the parent of its open files, and so open files in some
other instance aren't affected by any other instance running. Even if
2 instances have the same file open, the instance running the code
only works on objects of that instance.



True.

My thinking is simply that if Matthew *does* in fact have multiple
instances running then he will need to either adjust his operating
environment or change his strategy for dealing with it.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin formulated on Thursday :
"GS" wrote in message
...
Clif McIrvin presented the following explanation :
It's easy enough to discover if there are multiple instances running by
using Task Manager. On XP, my normal way to launch Task Manager is
Ctrl+Alt+Del which brings up a dialog and one of the choices is Task
Manager.

When Task Manager opens, select the Processes (NOT Applications) tab and
look for multiple entries for EXCEL.EXE.

If there's only one, there is only one instance running on that computer.

Regardless of how many instances are running, the ref to Application only
applies the the instance in which the code is executing. Each instance is
the parent of its open files, and so open files in some other instance
aren't affected by any other instance running. Even if 2 instances have the
same file open, the instance running the code only works on objects of that
instance.



True.

My thinking is simply that if Matthew *does* in fact have multiple instances
running then he will need to either adjust his operating environment or
change his strategy for dealing with it.

Well maybe, but that has nothing to do with why the code doesn't work
in whatever instance he's running it in. Just so it's clear what I'm
saying, I often test run my apps in 4 versions of Excel SUMULTANEOUSLY
without issue (exception: the expected issue with PERSONAL.XLS). Also,
I can run in a production instance (automated) AND my default instance
of the same version, again without issue. What makes this possible is
that each instance runs in its own thread, and space in memory. -I
don't believe there's any 'cross-talk' going on between the instances!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
Clif McIrvin formulated on Thursday :
"GS" wrote in message
...
Clif McIrvin presented the following explanation :
It's easy enough to discover if there are multiple instances
running by using Task Manager. On XP, my normal way to launch Task
Manager is Ctrl+Alt+Del which brings up a dialog and one of the
choices is Task Manager.

When Task Manager opens, select the Processes (NOT Applications)
tab and look for multiple entries for EXCEL.EXE.

If there's only one, there is only one instance running on that
computer.

Regardless of how many instances are running, the ref to Application
only applies the the instance in which the code is executing. Each
instance is the parent of its open files, and so open files in some
other instance aren't affected by any other instance running. Even
if 2 instances have the same file open, the instance running the
code only works on objects of that instance.



True.

My thinking is simply that if Matthew *does* in fact have multiple
instances running then he will need to either adjust his operating
environment or change his strategy for dealing with it.

Well maybe, but that has nothing to do with why the code doesn't work
in whatever instance he's running it in. Just so it's clear what I'm
saying, I often test run my apps in 4 versions of Excel SUMULTANEOUSLY
without issue (exception: the expected issue with PERSONAL.XLS). Also,
I can run in a production instance (automated) AND my default instance
of the same version, again without issue. What makes this possible is
that each instance runs in its own thread, and space in memory. -I
don't believe there's any 'cross-talk' going on between the instances!



None that I've ever read about. If he is using multiple instances, his
solution will be much, much easier to find if he can change the
operating model to run all the windows out of a single instance.

Generally I try to approach these posts from the mind-set of a
non-programmer, non-developer. It's not been so long ago that would
have described me -- although I do have a fair amount of programming
experience from pre-PC days -- and coding / "application development" is
still something that only happens on the outer fringes of my workload.
I have seen times when that perspective helped bridge a communication
gap between a poster and an experienced volunteer on the ng's.

Just my .02 worth <grin.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin wrote :
None that I've ever read about. If he is using multiple instances, his
solution will be much, much easier to find if he can change the operating
model to run all the windows out of a single instance.

Maybe, then, I don't understand what Matthew's talking about! I'm
assuming he's looking for the Application.Window holding the workbook
he's looking for, NOT the OS's open windows of Excel instances. If the
latter is the case then that requires using APIs to query the open
windows. That I can do (returns window handle) but I don't read his
post as this being the case. Perhaps we need more explicit info so we
don't go wondering off on tangents due to assumptions.

The OP clearly states he's looking to "activate a window containing
data to copy from". If the instance of Excel running the code I posted
has multiple files open AND one of those files contains the search text
he's looking for then it will return that window's Caption. I could
have made the function return its Index, but either can be used to
identify the window and so I went with the Caption since that's what
contains the search string.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
Clif McIrvin wrote :
None that I've ever read about. If he is using multiple instances,
his solution will be much, much easier to find if he can change the
operating model to run all the windows out of a single instance.

Maybe, then, I don't understand what Matthew's talking about! I'm
assuming he's looking for the Application.Window holding the workbook
he's looking for, NOT the OS's open windows of Excel instances. If the
latter is the case then that requires using APIs to query the open
windows. That I can do (returns window handle) but I don't read his
post as this being the case. Perhaps we need more explicit info so we
don't go wondering off on tangents due to assumptions.

The OP clearly states he's looking to "activate a window containing
data to copy from". If the instance of Excel running the code I posted
has multiple files open AND one of those files contains the search
text he's looking for then it will return that window's Caption. I
could have made the function return its Index, but either can be used
to identify the window and so I went with the Caption since that's
what contains the search string.



Absolutely. That's why, when he said your code wasn't working for him,
I questioned whether or not he had multiple instances. The casual
observer would have no idea whether they were running multiple instances
or not.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

on 3/11/2011, Clif McIrvin supposed :
Absolutely. That's why, when he said your code wasn't working for him, I
questioned whether or not he had multiple instances.

So then, you're suggesting he thought the file was open but when he ran
the code in the instance he ran from, the file was not open in that
instance.

The casual observer would have no idea whether they were running
multiple instances or not.

Huh.., you might be right! I find it hard to believe, though, since one
must deliberately start another instance while an existing instance is
running.

Another possibility is that an automated instance is running with its
'IgnoreRemoteRequests' property set 'True' so when a user double-clicks
a file in explorer it starts the default instance of Excel. If the
author of the automated instance didn't change the Caption/Icon for the
running app then there'd be 2 Excel icons on the taskbar.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
on 3/11/2011, Clif McIrvin supposed :
Absolutely. That's why, when he said your code wasn't working for
him, I questioned whether or not he had multiple instances.

So then, you're suggesting he thought the file was open but when he
ran the code in the instance he ran from, the file was not open in
that instance.

Correct.


The casual observer would have no idea whether they were running
multiple instances or not.

Huh.., you might be right! I find it hard to believe, though, since
one must deliberately start another instance while an existing
instance is running.

It's not that difficult to construct a shortcut that launches a new
instance of Excel, and if the user was using provided shortcuts they
would never know.


Another possibility is that an automated instance is running with its
'IgnoreRemoteRequests' property set 'True' so when a user
double-clicks a file in explorer it starts the default instance of
Excel. If the author of the automated instance didn't change the
Caption/Icon for the running app then there'd be 2 Excel icons on the
taskbar.

I started getting acquainted with Office and Excel w/ 2003 (Win XP
Professional). As I recall, I have *always* had a distinct Excel icon on
the taskbar for every open workbook -- whether I had one or three (or
whatever) instances of Excel running, so the taskbar icons are useless
as an indicator whether there are multiple instances of Excel running
(unless both instances have no open workbook.) Now that my company
switched to Office 2010 I still get a taskbar icon for each open
workbook .... I don't recall, I may have changed a setting because I was
used to seeing it that way.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin wrote on 3/11/2011 :
"GS" wrote in message
...
on 3/11/2011, Clif McIrvin supposed :
Absolutely. That's why, when he said your code wasn't working for him, I
questioned whether or not he had multiple instances.

So then, you're suggesting he thought the file was open but when he ran the
code in the instance he ran from, the file was not open in that instance.

Correct.


The casual observer would have no idea whether they were running multiple
instances or not.

Huh.., you might be right! I find it hard to believe, though, since one
must deliberately start another instance while an existing instance is
running.

It's not that difficult to construct a shortcut that launches a new instance
of Excel, and if the user was using provided shortcuts they would never know.


Another possibility is that an automated instance is running with its
'IgnoreRemoteRequests' property set 'True' so when a user double-clicks a
file in explorer it starts the default instance of Excel. If the author of
the automated instance didn't change the Caption/Icon for the running app
then there'd be 2 Excel icons on the taskbar.

I started getting acquainted with Office and Excel w/ 2003 (Win XP
Professional). As I recall, I have *always* had a distinct Excel icon on the
taskbar for every open workbook -- whether I had one or three (or whatever)
instances of Excel running, so the taskbar icons are useless as an indicator
whether there are multiple instances of Excel running (unless both instances
have no open workbook.) Now that my company switched to Office 2010 I still
get a taskbar icon for each open workbook .... I don't recall, I may have
changed a setting because I was used to seeing it that way.

This is controlled by the 'WindowsInTaskbar' property setting. I don't
see this in any UI dialogs for v2007. I can't speak to v2010 because I
haven't installed it yet. (Most my clients use 2003/7) [1]

In XP I get a separate icon on the taskbar for each instance. I get an
entirely different icon for an automated instance running my app[s].
This is how I tell them apart: -my app's instance has a different
Icon/Caption.

[1] I'll probably get around to it when a client starts using the
64bit version. I haven't decided yet whether to set up my Win7 x64
machine with VB6 because I haven't found out if MSO32 and MSO64 can
coexist on the same machine. I suppose I can just continue to do x32
stuff on my XP machine, but it would be more convenient to do it on the
Win7 x64 machine.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc