Excel VBA: Loop Through All Files in a Folder

VBA & macros · Updated November 19, 2026

To loop through files in a folder with Excel VBA, use the Dir function to get the first matching file, then call Dir again inside a Do While loop to get each next file. The macro below lists ordinary files in a folder in the Immediate window; change the folder path to suit your task.

Sub ListFilesInFolder()
    Dim folderPath As String
    Dim fileName As String
    Dim separator As String

    ' Set this to the folder you want to scan.
    folderPath = "C:\Data"
    separator = Application.PathSeparator
    If Right$(folderPath, 1) <> separator Then folderPath = folderPath & separator

    ' The first Dir call starts the search; later calls get the next match.
    fileName = Dir$(folderPath & "*")
    Do While Len(fileName) > 0
        If Left$(fileName, 2) <> "~$" Then Debug.Print folderPath & fileName
        fileName = Dir$()
    Loop
End Sub

In the Visual Basic Editor, press Alt+F11 on Windows, choose Insert > Module, and paste the code into the module. Open the Immediate window with Ctrl+G to see the results. Save a copy of the workbook before running any macro that changes data: VBA actions generally cannot be reversed with Ctrl+Z.

How the Dir loop works

Dir searches for names matching a path and optional pattern. Its first call supplies the search path, such as a folder plus a wildcard. Each later call with no argument returns the next match. When there are no more matches, it returns an empty string, so the loop ends. The example uses * to match files regardless of extension.

The value returned by Dir is a name, not a full path. Joining that name to folderPath gives a usable path for opening, copying, or inspecting the file. The Debug.Print statement sends that full path to the Immediate window; replace it with the action your workflow needs.

The check for names beginning with ~$ skips temporary owner files that Office may create while a workbook is open. These are not usually files you want to process as workbooks. If you intentionally need to include files with that prefix, remove the check.

Loop through only Excel workbooks

To restrict the search to one extension, put a pattern such as *.xlsx in the first Dir call. This version visits Excel Open XML workbooks with the .xlsx extension and prints their full paths:

Sub ListXlsxFiles()
    Dim folderPath As String
    Dim fileName As String
    Dim separator As String

    folderPath = "C:\Data"
    separator = Application.PathSeparator
    If Right$(folderPath, 1) <> separator Then folderPath = folderPath & separator

    fileName = Dir$(folderPath & "*.xlsx")
    Do While Len(fileName) > 0
        If Left$(fileName, 2) <> "~$" Then Debug.Print folderPath & fileName
        fileName = Dir$()
    Loop
End Sub

Excel files can also use .xlsm for macro-enabled workbooks, .xlsb for binary workbooks, or .xls for the older Excel format. A pattern such as *.xls* can find those common extensions, but it is broader than a precise extension check and may match other names that begin with .xls. For a controlled folder, use separate searches for the extensions you expect, or check each returned name before processing it.

Open and process each matching workbook

Once the loop finds a workbook, pass its full path to Workbooks.Open. This example opens each .xlsx file as read-only and closes it without saving. Add your own work between opening and closing the workbook—for instance, reading a known cell or copying data into a summary workbook.

Sub OpenEachXlsxReadOnly()
    Dim folderPath As String
    Dim fileName As String
    Dim separator As String
    Dim sourceBook As Workbook

    folderPath = "C:\Data"
    separator = Application.PathSeparator
    If Right$(folderPath, 1) <> separator Then folderPath = folderPath & separator

    fileName = Dir$(folderPath & "*.xlsx")
    Do While Len(fileName) > 0
        If Left$(fileName, 2) <> "~$" Then
            Set sourceBook = Workbooks.Open( _
                Filename:=folderPath & fileName, ReadOnly:=True, UpdateLinks:=0)

            ' Add the required reading or processing steps here.
            Debug.Print sourceBook.Name

            sourceBook.Close SaveChanges:=False
        End If
        fileName = Dir$()
    Loop
End Sub

Opening workbooks can trigger prompts, consume memory, or fail if a file is damaged, unavailable, or protected. Read-only mode helps avoid accidental edits, and UpdateLinks:=0 tells Excel not to update external links when opening. For a production process, consider error handling so one problematic file does not stop the whole run. Also make sure the macro does not accidentally process the workbook containing the macro if it is stored in the target folder and matches the pattern.

Include subfolders when needed

A Dir loop searches the specified folder; it does not automatically descend into its subfolders. If you only need the immediate folder, that behavior is useful because it keeps the search scope predictable. If you need every file in a folder tree, use a recursive procedure or a file-system object and explicitly visit each subfolder.

On Windows, the FileSystemObject provides a convenient way to enumerate a folder’s files and child folders. This late-bound example does not require adding a reference in the VBA editor:

Sub ListFilesRecursively()
    Dim fso As Object
    Dim rootFolder As Object

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set rootFolder = fso.GetFolder("C:\Data")
    WalkFolder rootFolder
End Sub

Private Sub WalkFolder(ByVal folder As Object)
    Dim file As Object
    Dim childFolder As Object

    For Each file In folder.Files
        Debug.Print file.Path
    Next file

    For Each childFolder In folder.SubFolders
        WalkFolder childFolder
    Next childFolder
End Sub

This visits files in the starting folder and then calls itself for each child folder. Large folder trees can contain many files, and some locations may deny access; a robust version should handle permission errors and decide whether to continue past inaccessible folders. The FileSystemObject example is intended for Windows. For portability, especially where the same macro must run on Mac, check the platform-specific file-system support before relying on this object.

Choose between Dir and FileSystemObject

For a single folder and a straightforward filename pattern, Dir is compact and is often the simplest choice. It avoids opening each workbook merely to find its name. FileSystemObject is more expressive when you need file properties such as size or date, or need to inspect a folder’s files and subfolders as collections. Neither method is automatically the right choice for every task.

  • Use Dir for a short list or a simple extension filter in one folder.
  • Use FileSystemObject when you need folder objects, file metadata, or recursive traversal on Windows.
  • Use a file picker when the person running the macro should choose a folder rather than edit a hard-coded path.

One important limitation of Dir is that its search state is shared. Do not start another Dir search inside the loop before the current search is finished: the new search can replace the active one, and the original loop may no longer return its expected next filename. If processing each file requires a helper procedure that also uses Dir, collect the names first or use a different enumeration method.

Paths, Mac, and choosing a folder

The examples target Excel for Microsoft 365 on Windows and use C:\Data as an illustrative path. Replace it with a real folder that exists and that your account can access. Application.PathSeparator supplies Excel’s path separator, and the code adds it only when the path does not already end with one. This helps avoid malformed paths caused by a missing separator or a doubled one.

Excel for Mac supports VBA, but file-system paths and available automation components can differ from Windows. A Mac path may not look like a Windows drive path, and Windows’ Scripting.FileSystemObject should not be assumed to work there. If a macro must run on both systems, use a folder-selection approach or platform-aware path handling, and test the actual file operations in both environments. Excel for the web does not run VBA macros; use the desktop Excel application to run these procedures.

To let a user select the folder, VBA can use Excel’s Application.FileDialog folder picker in desktop Excel. That avoids embedding a personal path in the code, but the dialog and returned path still need to be handled appropriately for the operating system. For a one-off task, changing the folder path directly may be simpler; for a shared workbook, asking the user to select a folder can make the macro easier to reuse.

Common problems and safe handling

  • No output: Confirm the folder exists, the path is spelled correctly, and the wildcard matches the file extensions. Check the Immediate window with Ctrl+G.
  • Files are skipped: A pattern like *.xlsx excludes .xlsm, .xlsb, and .xls files. Use a broader search or add searches for the formats needed.
  • The loop stops early: Look for another Dir call in code that runs during the loop. A second search can reset the active search state.
  • A workbook opens unexpectedly: Make sure the macro uses the intended folder and file pattern, and consider excluding its own workbook by comparing full paths.
  • Access or security prompts appear: Use folders you are authorized to read, and do not bypass Office security warnings for files you do not trust. Opening files from a folder can run workbook events or expose linked content depending on Excel settings.

A macro that only prints names is a good first check before adding actions that alter files or workbooks. When the macro will rename, delete, overwrite, or save files, test it on a copy of the folder and verify the exact file list before enabling those operations. Macros cannot be undone with Ctrl+Z, so a backup is especially important when the code changes source files.

Frequently Asked Questions

How do I loop through all files in a folder in VBA?
Set a filename variable to Dir$(folderPath & "*"), run a Do While Len(fileName) > 0 loop, and call Dir$() at the end of each pass to get the next match.

How do I loop through only Excel files?
Use an extension pattern such as *.xlsx in the first Dir call. Use a broader pattern or additional searches if you also need formats such as .xlsm, .xlsb, or .xls.

Does Dir search subfolders automatically?
No. A standard Dir loop searches the folder named in its path; it does not recurse into child folders. Use a recursive procedure or enumerate subfolders separately when you need a folder tree.

How can I see the filenames found by the macro?
The examples use Debug.Print, which writes to the Immediate window in the Visual Basic Editor. On Windows, open it with Ctrl+G. You can instead write names to worksheet cells if you need a saved list.

Can I run this VBA macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the desktop Excel application, such as Excel for Microsoft 365 on Windows or Mac, to run VBA.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.