VBA List all open Workbooks in Excel

VBA List all Open Workbooks in Excel. It helps to know all open workbooks name. It is easy to handle and shift between workbooks. We have an option to open multiple workbooks in MS Office Excel. Let us see in the following tutorial how to list open Workbooks in Excel VBA.

Table of Contents:

Objective
Syntax to List Workbooks in Excel VBA
VBA Code to List Workbooks in Excel VBA
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax to List Workbooks in Excel VBA

Here is the following syntax to List all Open Workbooks in Excel VBA.

For Each Workbook in Application.Workbooks
      Statements...
Next

Where Workbook: It represents Workbook object which is part of workbooks collection.

VBA Code to List all Workbooks in Excel VBA

Let us see the following example macro to List all Open Workbooks in Excel VBA

'VBA List Open Workbooks in Excel
Sub VBA_List_All_Open_Workbooks()
    
    'Variable declaration
    Dim xWorkbook As Workbook
    Dim sWorkbookName As String
    Dim iCount As Integer
    
    'Intialise value to a variable
    iCount = 2
    
    Sheets("WB_Names").Range("A1") = "Names of Available Workbooks"
    
    'Loop through all workbooks
    For Each xWorkbook In Application.Workbooks
        Sheets("WB_Names").Range("A" & iCount) = xWorkbook.Name & vbCrLf
        
        'Increase value
        iCount = iCount + 1
    Next

End Sub