How can I obtain a list of all open workbooks in VBA?

In order to obtain a list of all open workbooks in VBA, one can utilize the Workbooks collection object. This object contains a collection of all the currently open workbooks in the VBA project. By looping through this collection and accessing each workbook’s properties, one can retrieve a list of all open workbooks and their associated information, such as name, path, and number of worksheets. This allows for efficient management and manipulation of multiple workbooks within a VBA project.

VBA: Get a List of All Open Workbooks


You can use a For Each loop in VBA to get a list of all open Excel workbooks.

Here is one common way to do so in practice:

Sub ListAllOpenWorkbooks()

Dim wbName As StringDim wb As Workbook

'add each open workbook to message box
For Each wb In Application.Workbooks
    wbName = wbName & wb.Name & vbCrLf
Next'display message box with all open workbooks
MsgBox wbName

End Sub

This particular macro will generate a message box that contains a list of all open Excel workbooks.

The following example shows how to use this macro in practice.

Example: Get a List of All Open Workbooks Using VBA

Suppose we currently have the following three Excel workbooks open:

  • baseball_data.xlsx
  • football_data.xlsx
  • hockey_data.xlsx

Suppose we would like to use VBA to list the names of all of these open workbooks.

We can create the following macro to do so:

Sub ListAllOpenWorkbooks()

Dim wbName As StringDim wb As Workbook

'add each open workbook to message box
For Each wb In Application.Workbooks
    wbName = wbName & wb.Name & vbCrLf
Next'display message box with all open workbooks
MsgBox wbName

End Sub

When we run this macro, we receive the following output:

The message box displays the names of each the open workbooks, with each unique workbook listed on its own line.

Note that we used the vbCrLf constant within the For Each loop to insert a carriage return, which moves the cursor down to the next line.

This enabled us to list each open workbook on its own line in the message box.

Additional Resources

x