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., )Excel VBA Code - Loop through Opened WorkbooksVisual Basic1234567891011121314151617Sub Loop_Thru_All_Open_Workbooks_Excel_Files()'Define a Workbook ObjectDim oWb As Workbook'Traverse through each workbook Opened - Using Excel Application ObjectFor Each oWb In Application.Workbooks'Check for File nameIf oWb.Name <> "FileName.xlsx" Then'Check for File ContentIf oWb.Sheets(1).Cells(1, 1) = "Text To Compare" ThenMsgBox "File Searching for Found"End IfEnd If'Read Next Opened Excel FileNext oWbEnd 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.Excel VBA Get Name & Path of All Open WorkbooksVisual Basic123456789101112131415Sub Get_Names_Of_All_Open_Workbook()'Define a Workbook ObjectDim oWb As Workbook, iSh As Worksheet, iRow As DoubleSet iSh = ThisWorkbook.Sheets(1)'Traverse through each workbook Opened - Using Excel Application ObjectiRow = 1For Each oWb In Application.Workbooks'Get File Name, Path & any other PropertyiSh.Cells(iRow, 1) = oWb.NameiSh.Cells(iRow, 2) = oWb.PathiSh.Cells(iRow, 3) = oWb.CreatoriRow = iRow + 1Next oWbEnd 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.
Excel VBA – Loop Through All Open Workbooks – Get Names