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

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"
<[email protected]> wrote in
message
news:[email protected]...
>
> 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
>

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

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.