VBA : How to List All Open Workbooks.

This tutorial explains how to get all the open Excel workbooks using VBA code.

Let’s take an example to understand the procedure.

There are three open workbooks which are as follows :

1. Customer.xlsx
2. Product.xlsx
3. Transaction.xlsx

Task : To list the names corresponding to each workbook in a single column.

Sub listWorkbooks()
    Dim i As Long
    Dim WorkbookNames() As String
    
    
    ReDim WorkbookNames(1 To Workbooks.Count)
    
    
    For i = 1 To Workbooks.Count
        WorkbookNames(i) = Workbooks(i).Name
        ActiveSheet.Cells(i, 1).Value = WorkbookNames(i)
    Next i
End Sub

Press Run or F5 to run the above code.

The result looks like this

1. .Name : It returns the name of an object.
2. .Value : It returns the value stored in the object.
3. .Count : It returns the total count of the objects.

Read MoreListenData