Referencing Workbooks within VBA

[s_andrews]

In a script, I am working with two workbooks. The problem occurs with using the Activate function. When the script was first set up, the developer simply wrote Workbooks(1).Activate. For a given session, this may have worked well but depending on what workbooks you have open at the time, the index reference for the desired workbook may not be "1". Hence, I replaced the reference with the workbook name as in: Workbook("Hold_Request")Activate.

This worked out fine for awhile but then started giving errors. Why? Even though "Hold_Request" was the proper name of the workbook, within VBA it was called "Hold_Request (15)". I never had more than one workbook open at a time by that name. So why do the numbers get appended to the project name?

The only solution I see at the moment is to do a while for all open projects to see which one has a name with it's first twelve characters equal to "Hold_Request". I could then either use the expanded name or the index for use with my Activate function. Any ideas on this matter? Thanks in advance.

Steve

[HaHoBe]

Hi, Steve,

shouldn´t that be

Please Login or Register  to view this content.

indicating that the extension should be part of the name as well (I assumed a 2003 version spreadsheet here)?

Or set objects to the workbook like

Please Login or Register  to view this content.

and refer to wbHold or ThisWorkbook which is the workbook that holds the code. You may also check if the object holds a reference for a workbook prior to working on in order to avoid run-time errors.

Ciao,
Holger