Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate workbook using partial name
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate workbook using partial name
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate workbook using partial name
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 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate workbook using partial name
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" skrev i meddelelsen ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate workbook using partial name
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 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate workbook using partial name
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" skrev i meddelelsen ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
activate workbook | Excel Programming | |||
Workbook.Activate / Window.Activate problem | Excel Programming | |||
Reference a workbook with only a partial name | Excel Worksheet Functions | |||
Workbook.activate | Excel Discussion (Misc queries) | |||
Activate Other Workbook | Excel Programming |