Selecting a Workbook by Index or Name in Excel VBA

Selecting a Workbook by Index or Name

The following macro demonstrates two different techniques for selecting (activating) already opened workbooks:

Sub SelectWorkbookFromList()
    Workbooks(1).Activate
    MsgBox ActiveWorkbook.Name
    Workbooks("Workbook2.xlsm").Activate
    MsgBox ActiveWorkbook.Name
End Sub

Explanation:

This VBA subroutine shows how to activate workbooks using:

1. Selection by Index:

Workbooks(1).Activate

• The Workbooks collection contains all the currently open workbooks in the Excel session.
• Each workbook in this collection has a position index, starting at 1 for the first opened workbook, up to Workbooks.Count (the total number of open workbooks).
• The line above activates the workbook at position 1.
• MsgBox ActiveWorkbook.Name then displays the name of the currently active workbook, confirming which one was selected.

⚠️ Be cautious:
If you try to use an index that doesn’t exist (for example, Workbooks(3) when only two workbooks are open), Excel will throw a runtime error.
Techniques for managing such errors will be covered in Chapter 4: Error Handling.

2. Selection by Name:

Workbooks(« Workbook2.xlsm »).Activate

• Instead of using an index, you can specify the name of the workbook file as a string in quotation marks.
• This is often more reliable than using the index because the order of open workbooks can change (especially if the user opens or closes files dynamically).
• The name used must be exactly the file name as shown in Excel’s title bar (but without the full file path).
• After activation, the MsgBox line again displays the name of the now active workbook.

✅ Important:
In Excel, you cannot open two workbooks with the same name at the same time, even if they are located in different folders. This guarantees that the name is a unique identifier among open workbooks.

Recommendation:

Although selecting a workbook by index may work in some cases, using the workbook name is more precise and less error-prone. It’s easier to understand and maintain in your code, especially when multiple workbooks are open.