Is it possible to activate a workbook using only part of the worbook name?
e.g. windows("Done.*").Activate. I have two workbooks open, my macro creates
one of the wb's and the other is already open, but the end of its name will
change everyday. For example, today the wb name is DONE3209.xlsx but
tomorrow the wb name may be DONE3509.xlsx. The first 4 letters in the wb
name will always be the same so I would like to use that part of the wb name
only to activate the wb. Thanks in advance for any help.

Hi Aileen,

You say that your macro creates one of the workbooks so I am assuming that
it creates a variable for the name of the workbook. If the workbook with the
macro does not get closed then you could use the following method.

Declare a Public variable in the Declarations area of the VB Editor.
(Declarations area is at the top of the page before any subs.)

Example:-
Public wbName As String

In your code ensure that you assign the new name of the workbook to that
variable and it will be available until the workbook with the macro is
closed. You activate the workbook with the following code.

Windows(wbName).Activate

However, if the workbook with the macro gets closed and you still want it to
remember the last workbook created when you next open it then save the
workbook name to a cell in an out of the way cell area of the workbook and
use a workbook open event to assign the cell value to the Public variable.

Hope this helps.
--
Regards,

OssieMac


"aileen" wrote:

> Is it possible to activate a workbook using only part of the worbook name?
> e.g. windows("Done.*").Activate. I have two workbooks open, my macro creates
> one of the wb's and the other is already open, but the end of its name will
> change everyday. For example, today the wb name is DONE3209.xlsx but
> tomorrow the wb name may be DONE3509.xlsx. The first 4 letters in the wb
> name will always be the same so I would like to use that part of the wb name
> only to activate the wb. Thanks in advance for any help.
>
>

Use code like

Sub ActivateWB(WBName As String)
Dim WB As Workbook
For Each WB In Workbooks
If WB.Name Like WBName Then
WB.Activate
Exit Sub
End If
Next WB
End Sub

Then you can call this with code like

ActivateWB "DONE*"

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 8 Apr 2009 13:13:02 -0700, aileen
<(E-Mail Removed)> wrote:

>Is it possible to activate a workbook using only part of the worbook name?
>e.g. windows("Done.*").Activate. I have two workbooks open, my macro creates
>one of the wb's and the other is already open, but the end of its name will
>change everyday. For example, today the wb name is DONE3209.xlsx but
>tomorrow the wb name may be DONE3509.xlsx. The first 4 letters in the wb
>name will always be the same so I would like to use that part of the wb name
>only to activate the wb. Thanks in advance for any help.
>

Hi

If the workbook DONE* is the active workbook when the macro is started you
can use this before the macro create the new workbook:

Dim wbA as Workbook
Dim wbB as Workbook
Set wbA = ActiveWorkbook

Set wbB=Workbooks.Add
'Your code

'More code

wbA.Activte

Hopes this helps

---
Per

"aileen" <(E-Mail Removed)> skrev i meddelelsen
news:95C5F83B-D848-49BA-9544-(E-Mail Removed)...
> Is it possible to activate a workbook using only part of the worbook name?
> e.g. windows("Done.*").Activate. I have two workbooks open, my macro
> creates
> one of the wb's and the other is already open, but the end of its name
> will
> change everyday. For example, today the wb name is DONE3209.xlsx but
> tomorrow the wb name may be DONE3509.xlsx. The first 4 letters in the wb
> name will always be the same so I would like to use that part of the wb
> name
> only to activate the wb. Thanks in advance for any help.
>
>

Worked perfectly. Thanks so much.

"Chip Pearson" wrote:

> Use code like
>
> Sub ActivateWB(WBName As String)
> Dim WB As Workbook
> For Each WB In Workbooks
> If WB.Name Like WBName Then
> WB.Activate
> Exit Sub
> End If
> Next WB
> End Sub
>
> Then you can call this with code like
>
> ActivateWB "DONE*"
>
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional
> Excel Product Group, 1998 - 2009
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email on web site)
>
>
> On Wed, 8 Apr 2009 13:13:02 -0700, aileen
> <(E-Mail Removed)> wrote:
>
> >Is it possible to activate a workbook using only part of the worbook name?
> >e.g. windows("Done.*").Activate. I have two workbooks open, my macro creates
> >one of the wb's and the other is already open, but the end of its name will
> >change everyday. For example, today the wb name is DONE3209.xlsx but
> >tomorrow the wb name may be DONE3509.xlsx. The first 4 letters in the wb
> >name will always be the same so I would like to use that part of the wb name
> >only to activate the wb. Thanks in advance for any help.
> >
>

This also worked perfectly. Thanks for the help.

"Per Jessen" wrote:

> Hi
>
> If the workbook DONE* is the active workbook when the macro is started you
> can use this before the macro create the new workbook:
>
> Dim wbA as Workbook
> Dim wbB as Workbook
> Set wbA = ActiveWorkbook
>
> Set wbB=Workbooks.Add
> 'Your code
>
> 'More code
>
> wbA.Activte
>
> Hopes this helps
>
> ---
> Per
>
> "aileen" <(E-Mail Removed)> skrev i meddelelsen
> news:95C5F83B-D848-49BA-9544-(E-Mail Removed)...
> > Is it possible to activate a workbook using only part of the worbook name?
> > e.g. windows("Done.*").Activate. I have two workbooks open, my macro
> > creates
> > one of the wb's and the other is already open, but the end of its name
> > will
> > change everyday. For example, today the wb name is DONE3209.xlsx but
> > tomorrow the wb name may be DONE3509.xlsx. The first 4 letters in the wb
> > name will always be the same so I would like to use that part of the wb
> > name
> > only to activate the wb. Thanks in advance for any help.
> >
> >
>
>