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

Workbooks("Hold_Request.xls")Activate

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

Dim wbHold As Workbook

Set wbHold = Workbooks("Hold_Request.xls")

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