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?

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

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?

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 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