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.