Excel VBA – Loop Through All Open Workbooks – Get Names

VBA Code To Access All Open Excel Files

The code below will loop through all workbooks that are open in current system.

If you want to scan through all the opened Excel files, assign it to a Workbook object and read or write to them, then this code will be much useful.

Collection Used: Application.workbooks

Excel Macro to Search All Opened Workbooks

Copy paste this code to your VBA project, to search or verify any opened files with Excel Application (CSV, XLSM, XLSX etc., )

Sub Loop_Thru_All_Open_Workbooks_Excel_Files()
    'Define a Workbook Object
    Dim oWb As Workbook
    
    'Traverse through each workbook Opened - Using Excel Application Object
    For Each oWb In Application.Workbooks
        'Check for File name
        If oWb.Name <> "FileName.xlsx" Then
            'Check for File Content
            If oWb.Sheets(1).Cells(1, 1) = "Text To Compare" Then
                MsgBox "File Searching for Found"
            End If
        End If
        
        'Read Next Opened Excel File
    Next oWb
End Sub

Once the file that you are searching for is found, then using the Workbook object, any file operations like read/write can be performed.

VBA Get Name & Path Of All Open Workbooks

Here is another very useful implementation of this code.

This will create an index of all open Excel files into current active sheet along with its folder path.

Sub Get_Names_Of_All_Open_Workbook()
    'Define a Workbook Object
    Dim oWb As Workbook, iSh As Worksheet, iRow As Double
    Set iSh = ThisWorkbook.Sheets(1)
    
    'Traverse through each workbook Opened - Using Excel Application Object
    iRow = 1
    For Each oWb In Application.Workbooks
        'Get File Name, Path & any other Property
        iSh.Cells(iRow, 1) = oWb.Name
        iSh.Cells(iRow, 2) = oWb.Path
        iSh.Cells(iRow, 3) = oWb.Creator
        iRow = iRow + 1
    Next oWb
End Sub

If you have opened a large number of Excel files & get a list of all its names, then this code will generate the list.