Activating an Open Workbook without using the whole filename as a reference
 

Apologies if this is a really simple question but I haven't been using
VBA for all that long.

I am trying to work out if I can activate an open workbook without
referencing the whole name?

Essentially every month 100 or so files will open automatically, but
each month the name changes slightly as the file name references the
month.
Therefore when I select a file I just want to use the 'constant' terms
in the name to find the file.

I have tried the below, using the * tool but it tells me the subscript
is out of range:

i.e

Application.Workbooks("MLSS*.xls").activate

Is there something fundamental I'm missing?

Thanks for any help

Joe


--
jlejehan
------------------------------------------------------------------------
jlejehan's Profile: http://www.excelforum.com/member.php...o&userid=33950
View this thread: http://www.excelforum.com/showthread...hreadid=537253

Activating an Open Workbook without using the whole filename as a reference
 

Try something like

Dim WB As Workbook
For Each WB In Application.Workbooks
If WB.Name Like "MLSS*.xls" Then
WB.Activate
Exit For
End If
Next WB


Only one workbook can be activated at a time.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"jlejehan"
wrote in
message
...

Apologies if this is a really simple question but I haven't
been using
VBA for all that long.

I am trying to work out if I can activate an open workbook
without
referencing the whole name?

Essentially every month 100 or so files will open
automatically, but
each month the name changes slightly as the file name
references the
month.
Therefore when I select a file I just want to use the
'constant' terms
in the name to find the file.

I have tried the below, using the * tool but it tells me the
subscript
is out of range:

i.e

Application.Workbooks("MLSS*.xls").activate

Is there something fundamental I'm missing?

Thanks for any help

Joe


--
jlejehan
------------------------------------------------------------------------
jlejehan's Profile:
http://www.excelforum.com/member.php...o&userid=33950
View this thread:
http://www.excelforum.com/showthread...hreadid=537253

Activating an Open Workbook without using the whole filename as a
 

dim bk as workbook
dim bk1 as Workbook
for each bk in application.Workbooks
if bk.name like "MLSS*" then
bk.activate
set bk1 = bk
exit for
end if
Next

' now you can also reference it with bk1
' msgbox bk1.name

--
Regards,
Tom Ogilvy




"jlejehan" wrote:


Apologies if this is a really simple question but I haven't been using
VBA for all that long.

I am trying to work out if I can activate an open workbook without
referencing the whole name?

Essentially every month 100 or so files will open automatically, but
each month the name changes slightly as the file name references the
month.
Therefore when I select a file I just want to use the 'constant' terms
in the name to find the file.

I have tried the below, using the * tool but it tells me the subscript
is out of range:

i.e

Application.Workbooks("MLSS*.xls").activate

Is there something fundamental I'm missing?

Thanks for any help

Joe


--
jlejehan
------------------------------------------------------------------------
jlejehan's Profile: http://www.excelforum.com/member.php...o&userid=33950
View this thread: http://www.excelforum.com/showthread...hreadid=537253

Activating an Open Workbook without using the whole filename as a reference
 

Chip, Tom

Many thanks for your replies. Your solutions seem to work perfectly
which has saved me a great deal of time.

Thanks for all your help. Joe.


--
jlejehan
------------------------------------------------------------------------
jlejehan's Profile: http://www.excelforum.com/member.php...o&userid=33950
View this thread: http://www.excelforum.com/showthread...hreadid=537253