To loop through files in a folder with Excel VBA, use the Dir function to find a matching file, process it, and call Dir again to get the next match. For example, the macro below prints the full path of every .xlsx file in a folder to the Immediate window.
Sub ListExcelFiles()
Dim folderPath As String
Dim fileName As String
folderPath = "C:\Reports\"
If Right$(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
fileName = Dir$(folderPath & "*.xlsx")
Do While Len(fileName) > 0
Debug.Print folderPath & fileName
fileName = Dir$()
Loop
End Sub
Change C:\Reports\ to the folder you want to search. In Excel for Microsoft 365 on Windows, open the VBA editor with Developer > Visual Basic, insert a standard module, and run the macro. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.
How a folder loop works
A folder loop repeats the same operation for each file that meets a condition. In the example, Dir$(folderPath & "*.xlsx") asks Windows for the first filename matching the pattern. The Do While loop runs as long as the returned filename is not empty. The argument-free Dir$() call then supplies the next match. When there are no more matches, it returns an empty string and the loop ends.
The wildcard * means any sequence of characters. Thus *.xlsx means “any filename ending in .xlsx.” You can use a different pattern, such as *.csv for CSV files or Report_*.xlsx for workbooks whose names start with “Report_.” The example only lists paths; it does not open, edit, or move the files.
Debug.Print writes results to the Immediate window in the VBA editor. Press Ctrl+G in the Windows editor to show that window. This is a useful first check: confirm that the macro finds the intended files before adding code that opens or modifies them.
List files with Dir
Here is a version that lists matching filenames in a worksheet instead. It clears column A on the active sheet, writes a heading, and adds one filename per row. It still does not open the files.
Sub ListFilesOnSheet()
Dim folderPath As String
Dim fileName As String
Dim nextRow As Long
folderPath = "C:\Reports\"
If Right$(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
ActiveSheet.Columns("A").ClearContents
ActiveSheet.Range("A1").Value = "File name"
nextRow = 2
fileName = Dir$(folderPath & "*.xlsx")
Do While Len(fileName) > 0
ActiveSheet.Cells(nextRow, 1).Value = fileName
nextRow = nextRow + 1
fileName = Dir$()
Loop
End Sub
This sample deliberately clears the contents of column A on the active sheet, so check which sheet is active before running it. To preserve existing data, remove the ClearContents line or direct the output to a new worksheet. If you need full paths in the list, write folderPath & fileName instead of just fileName.
Open and process each workbook
To inspect workbook contents, open each file inside the loop, read or process the required data, and close it before continuing. This example reads cell A1 from the first worksheet of every matching workbook and prints the filename and value to the Immediate window. The source files open as read-only, and the macro does not save changes to them.
Sub ReadFirstCellInEachWorkbook()
Dim folderPath As String
Dim fileName As String
Dim sourceWb As Workbook
folderPath = "C:\Reports\"
If Right$(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
fileName = Dir$(folderPath & "*.xlsx")
Do While Len(fileName) > 0
If StrComp(fileName, ThisWorkbook.Name, vbTextCompare) <> 0 Then
Set sourceWb = Workbooks.Open( _
Filename:=folderPath & fileName, _
ReadOnly:=True, _
UpdateLinks:=0)
Debug.Print fileName, sourceWb.Worksheets(1).Range("A1").Value
sourceWb.Close SaveChanges:=False
End If
fileName = Dir$()
Loop
End Sub
The workbook reference is explicitly declared as a Workbook, and SaveChanges:=False prevents saving edits to the opened file. Replace the cell reference with the range or procedure your task requires. If a workbook has no worksheets, uses a different layout, is password-protected, or cannot be opened, this simple example may stop with an error; production code should handle those cases and still close any workbook it successfully opened.
For macros that change files, test on copies first. A loop that saves or overwrites many workbooks can repeat a mistake quickly, and Ctrl+Z does not reverse VBA operations. Also consider excluding temporary files whose names begin with ~$ if you broaden the pattern to include several Excel extensions.
Include files in subfolders
Dir searches the specified folder, not all of its child folders. If you need a recursive search, the FileSystemObject provides folders, files, and subfolders as objects. This example uses late binding, so it does not require adding a reference in the VBA editor. It prints the full path for every file under the starting folder, including files in nested folders.
Sub ListFilesRecursively()
Dim fso As Object
Dim startFolder As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set startFolder = fso.GetFolder("C:\Reports")
PrintFolderFiles startFolder
End Sub
Private Sub PrintFolderFiles(ByVal folder As Object)
Dim file As Object
Dim subfolder As Object
For Each file In folder.Files
Debug.Print file.Path
Next file
For Each subfolder In folder.SubFolders
PrintFolderFiles subfolder
Next subfolder
End Sub
Because this recursive example prints every file type, add an extension check if you only want workbooks. For example, inside the file loop, test LCase$(fso.GetExtensionName(file.Name)) against the extension you want. A large directory tree can take time to scan. Access restrictions, disconnected network folders, or unusually deep folder structures can also cause errors, so consider adding error handling when using this on a broad or important directory.
Choose Dir or FileSystemObject
For a simple, nonrecursive job, Dir is compact and built into VBA. It is a good fit for listing files with a known extension or processing one folder at a time. Its main limitation is that it keeps search state: calling Dir with a new search pattern before finishing the current one replaces the active search. Avoid starting another Dir search from inside the loop unless you have first stored the original filenames.
FileSystemObject is more expressive when you need subfolders or file properties such as a file’s size, name, or path. It also makes a recursive folder walk straightforward. It is an additional COM object, however, and a folder that the current user cannot access may raise an error. For large jobs, choosing between the two is less important than deciding how to handle inaccessible files, unexpected file types, and failures partway through the run.
- Choose
Dirfor a quick list or a single-level folder search. - Choose FileSystemObject when you need to inspect folders recursively or use file and folder properties.
- Store filenames in an array or collection first if processing code might start another
Dirsearch.
Common problems and fixes
If the loop finds no files, verify the folder path and file extension. A missing trailing backslash is handled in the examples, but a misspelled folder name, a different extension, or a path that does not exist will still produce no matches. If the folder is on a network drive, confirm that it is available and that your Windows account can access it. A UNC path such as \\Server\Share\Reports\ can be used in place of a local path when the share is reachable.
If Excel reports a path or file error, check that the folder exists before opening files and make sure the full path is not being constructed twice. The examples append a filename to a folder path that ends in one backslash. Do not add another backslash to both the stored path and the expression that joins the path to the filename.
If a loop seems to stop early, check whether the code calls Dir again with a new pattern, either directly or in a procedure called from inside the loop. That can disturb the existing search. Another common issue is assuming that *.xlsx includes macro-enabled workbooks: it does not. Use a suitable pattern for the formats you actually intend to process, and be careful with broad patterns because they may include files you did not mean to change.
When a file is locked, corrupt, or requires a password, opening it may fail or prompt the user. A robust macro should decide whether to skip such a file, record the error, or stop, rather than silently losing track of what happened. For tasks that process many files, write a log containing each filename and its outcome so you can identify skipped items and repeat work safely.
Run the macro safely in Excel
In Excel for Microsoft 365 on Windows, save a macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the VBA code. To add a module, choose Developer > Visual Basic, then in the editor choose Insert > Module. Paste the procedure into the module. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer.
Before execution, change the example folder path and review every line that writes, deletes, moves, or saves files. Start with a test folder containing copies of a few representative files. Enable macros only for workbooks and sources you trust; do not lower macro security broadly just to run an unfamiliar file. A macro that only prints paths has a much smaller impact than one that edits and saves every workbook, but both should be reviewed before use.
Excel for the web cannot create, run, or edit VBA macros. On a Mac, Excel desktop supports VBA, but the Developer tab may need to be enabled in Excel settings, and access to files can depend on macOS privacy permissions. Windows paths use backslashes; Mac paths and file access differ, so do not assume a Windows path such as C:\Reports\ will work on a Mac. For cross-platform work, choose and test a path approach for both systems rather than copying a Windows-only path unchanged.
Frequently Asked Questions
How do I loop through all files in a folder, not just Excel files?
Use Dir$(folderPath & "*.*") as the search pattern and keep the same loop structure. That can include files of many types, so check extensions before opening or changing anything.
How do I loop through files in a folder and its subfolders?
A basic Dir loop does not recurse into subfolders. Use FileSystemObject and call a procedure for each subfolder, as in the recursive example above.
Can VBA loop through a folder without opening each file?
Yes. Use Dir to list filenames or FileSystemObject to inspect paths and file properties. Opening a workbook is only necessary when you need to read or change its workbook contents.
Why does Dir return an empty string?
It returns an empty string when there are no more matches, and it may return one immediately if the folder path or search pattern matches nothing. Check spelling, permissions, the extension, and whether the folder is accessible.
Can I run a VBA folder loop in Excel for the web?
No. Excel for the web can preserve a workbook containing VBA, but it cannot run or edit VBA macros. Use desktop Excel; on Mac, use Excel desktop and adapt the file path and permissions as needed.
Related
- Excel Split Empty Cells Into Multiple Rows?
- Excel Split Cell Text Into Separate Cells: A Practical Guide
- Excel Split Cell Value by Delimiter: Methods and Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA UserForm Tab Order: Set It CorrectlyTo set an Excel VBA UserForm’s tab order, open the form in the Visual Basic Editor, choose View…
- Excel VBA: Add a New Sheet at the End and Name It SafelyTo add a worksheet after the workbook’s last tab and name it in Excel VBA, use Worksheets.Add with…
- Excel VBA Function Not Defined? Causes and Fixes ExplainedIf Excel VBA reports Sub or Function not defined, it cannot find the procedure named in the highlighted…
- VBA Display UserForm: Methods, Examples, and FixesIn Excel VBA, display a UserForm by calling its Show method, such as frmEntry.Show. By default, the form…
- Excel UserForm: Change Font Size at Design Time or in VBATo change an Excel VBA UserForm’s font size, select the form or a control in the Visual Basic…
- Excel Macro Tiny Toggle: Make a Small On/Off ButtonIf you mean a tiny on/off control that runs a macro, Excel has no built-in feature called a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.