How can I get a list of all the currently open workbooks using VBA?

---

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 String
Dim 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 String
Dim 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.