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 :

Customer.xlsx
Product.xlsx
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

Explanation :

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

How ReDim works?

It alters the size of an array. WorkbookNames stores the names of workbooks from i=1 to i= Workbooks.Count

About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Let's Get Connected Email LinkedIn