Activate workbook using partial name

[aileen]

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.

 

[OssieMac]

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.

 

[Chip Pearson]

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)

 

[Per Jessen]

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

 

[aileen]

Worked perfectly. Thanks so much.

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)

 

[aileen]

This also worked perfectly. Thanks for the help.