In Excel VBA, use the Dir function to find files that match a folder path and filename pattern, then repeat with Dir$() until it returns an empty string. The example below lists every matching Excel workbook; you can put your own processing steps inside the loop.
A basic VBA loop through files
This example searches one folder for files ending in .xlsx and prints each full path in the Visual Basic Editor’s Immediate window. Change the folder path to the directory you want to scan.
Sub ListExcelFiles()
Dim folderPath As String
Dim fileName As String
Dim fullName As String
folderPath = "C:\Reports"
If Right$(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
fileName = Dir$(folderPath & "*.xlsx")
Do While Len(fileName) > 0
fullName = folderPath & fileName
Debug.Print fullName
fileName = Dir$()
Loop
End Sub
For example, if the folder contains January.xlsx and February.xlsx, the loop prints their full paths one at a time. It does not open or change either workbook. The wildcard * means “any sequence of characters,” so *.xlsx matches every filename with that extension.
How Dir works
The first call supplies a search pattern: the directory, a path separator, and a filename pattern. Dir$(folderPath & "*.xlsx") returns the first matching filename, not the full path. The code joins that filename to the folder path to create fullName.
After the first call, call Dir$() without a path to retrieve the next match from the same search. When there are no more matches, it returns an empty string, which ends the Do While loop. This makes the pattern compact and suitable for ordinary, non-recursive folder scans.
A key limitation is that Dir maintains search state. Starting another Dir search before finishing the current one can replace the active search. Avoid calling a second file-enumeration routine that uses Dir from inside the loop. For more elaborate scans, consider the FileSystemObject approach described below.
Choose the file pattern you need
The pattern determines which filenames are returned. Use a narrow pattern when possible so the macro does not try to process unrelated files.
*.xlsxfinds standard Excel workbook files.*.xlsmfinds macro-enabled Excel workbooks.*.xlsfinds the older Excel workbook format.*.csvfinds comma-separated text files, which Excel can also open.Report_*.xlsxfinds only workbooks whose names begin withReport_.
If you need more than one extension, a simple option is to run a separate loop for each pattern. Be careful not to process the same file twice if your patterns overlap. A pattern such as *.* is broad and may include files that are not workbooks; check the extension or use a targeted pattern before attempting to open matches.
Open and process each workbook
To read workbook contents, open each match inside the loop, perform the required operation, and close it afterward. The following complete example opens matching workbooks as read-only, displays each workbook’s name in the Immediate window, and closes it without saving. It assumes the source workbooks are not already open in Excel.
Sub ReadExcelFiles()
Dim folderPath As String
Dim fileName As String
Dim fullName 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
fullName = folderPath & fileName
If StrComp(fullName, ThisWorkbook.FullName, vbTextCompare) <> 0 Then
Set sourceWb = Workbooks.Open(Filename:=fullName, _
ReadOnly:=True, UpdateLinks:=0)
Debug.Print sourceWb.Name
sourceWb.Close SaveChanges:=False
Set sourceWb = Nothing
End If
fileName = Dir$()
Loop
End Sub
The comparison with ThisWorkbook.FullName prevents the macro from opening itself if it is in the target folder and also matches the pattern. Replace Debug.Print sourceWb.Name with the work you need, such as reading a known worksheet or copying selected values into a summary workbook. The example suppresses link updates when opening files; it does not make untrusted files safe.
If a matching workbook might already be open, do not assume that opening and then closing it is harmless: Excel may reuse the open workbook, and the close line could close a workbook the user wanted to keep open. Either restrict the batch to closed source files or add logic to identify open workbooks and close only those opened by the macro. For a real batch job, also add error handling so one damaged or password-protected file does not stop the whole run.
Run the macro in Excel for Windows
- Save a copy of the workbook that contains your macro. VBA changes cannot be undone with Ctrl+Z.
- Open the Visual Basic Editor with Alt+F11. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon, then select Developer.
- In the editor, choose Insert > Module and paste the code into the module.
- Change
C:\Reportsto the folder you want to scan. Keep the folder path in quotation marks. - Run the procedure from the editor with F5, or return to Excel and run it through Developer > Macros.
To see the output from Debug.Print, open the Visual Basic Editor and press Ctrl+G to display the Immediate window. The macro-enabled workbook should be saved as an .xlsm file if you want to retain its VBA code. Excel for the web can open workbooks containing macros, but it cannot run VBA; use the desktop Excel application.
Scan subfolders or use another method
The Dir pattern shown above scans only the folder you specify. It does not automatically descend into child folders. If you need every file in a directory tree, write a recursive routine that visits each subfolder, or use the FileSystemObject, which exposes folders, files, and subfolders as objects. The FileSystemObject can be easier to extend for recursive work, but it adds more code and object handling than a single Dir loop.
For a one-level scan, Dir is usually the straightforward choice. For a user-selected folder, VBA’s Application.FileDialog(msoFileDialogFolderPicker) can let the user choose the directory instead of hard-coding it. Whichever method you use, keep the file-processing logic separate from the folder-selection logic where practical; this makes it easier to change the source folder without rewriting what the macro does to each file.
Common problems and fixes
- No results: Check the spelling of the folder, the path separator, and the extension. Confirm that the files are actually in that folder rather than a child folder.
- Only the first file appears: Make sure the loop calls
fileName = Dir$()on every pass. The initial call needs the search pattern; later calls continue the same search. - The macro stops on one workbook: A file may be corrupt, password-protected, locked, or otherwise unavailable. Add per-file error handling and ensure any opened workbook is closed even when processing fails.
- The wrong files are included: Narrow the wildcard pattern or test the filename before processing it. Do not use a broad search if the folder contains temporary files or unrelated documents.
- The macro cannot run: Confirm that you are using desktop Excel and that the workbook is saved in a macro-enabled format. Follow your organization’s macro-security rules; do not enable macros in files you do not trust.
Paths are platform-specific. The sample uses a Windows path and backslashes. Excel for Mac supports VBA in the desktop app, but use a Mac-appropriate folder path and check any file-system assumptions in the code. The web version does not execute VBA. On a Mac keyboard, the Visual Basic Editor shortcut is commonly Option+F11; some keyboards also require the Fn key.
Frequently Asked Questions
How do I loop through all files in a folder in Excel VBA?
Call Dir with a folder-and-wildcard pattern, process the returned filename in a loop, and call Dir$() again until the result is empty. Combine each filename with the folder path when you need a full path.
How do I loop through all Excel files, not just XLSX files?
Run a loop for each extension you want to include, such as *.xlsx, *.xlsm, and *.xls. A broad pattern can return non-Excel files, so check the extension before opening a match.
Does Dir search subfolders?
No. A standard Dir wildcard search covers the specified folder, not its descendants. Use recursion or a folder-and-file object model such as FileSystemObject when you need a directory tree.
Can I use this macro in Excel for the web?
No. Excel for the web does not run VBA macros. Run the macro in desktop Excel for Windows or Mac, with platform-appropriate paths.
How do I avoid opening the workbook that contains the macro?
Compare the candidate’s full path with ThisWorkbook.FullName before opening it. Comparing full paths is more precise than comparing names alone, since different folders can contain files with the same name.
Related
- Excel Text Number Format: How It Works and When to Use It
- Excel Text Filters: How to Find Matching Rows Easily
- Excel Text Box Wrap Text: How to Enable It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Recorder: Can It Repeat Without a Limit?Excel’s Macro Recorder does not have a built-in “repeat forever” setting: it records a set of actions once.…
- Excel Macro Protect Sheet Options: VBA, Limits, and TipsIn Excel for Microsoft 365 on Windows, a VBA macro protects a worksheet with the Worksheet.Protect method: choose…
- Excel VBA Find and Replace Exact Text in a ColumnTo replace an exact cell value in one Excel column, use VBA’s Range.Replace method with LookAt:=xlWhole. For example,…
- Excel VBA Select Case Examples: Syntax and Common UsesIn Excel VBA, Select Case chooses which block of code to run based on the value of an…
- Excel VBA Find the First Empty RowTo append data after the last entry in a known column, use Cells(Rows.Count, "A").End(xlUp).Row + 1, with a…
- VBA Code to Delete Rows in Excel: Practical ExamplesIn Excel VBA, delete a worksheet row with Rows(rowNumber).Delete; when removing rows that meet a condition, loop from…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.