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.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
How to Rename a File Using VBA
How to List Files in Folder Using VBA
How to Open All Files in Folder Using VBA
How to Check if File Exists Using VBA
