Excel VBA: Open Multiple Files in a Folder with a Loop

To open every Excel workbook in a folder, use VBA’s Dir function to find each matching filename, then call Workbooks.Open inside a loop. The example below lets you choose a folder, opens matching Excel files read-only, and leaves them open; save a copy of your workbook before running it, because macros cannot be undone with Ctrl+Z.

How the folder loop works

Dir searches for files matching a pattern such as *.xlsx and returns one filename at a time. After handling that file, call Dir again with no arguments to get the next match. When there are no more matches, it returns an empty string and the loop ends. The code combines each returned filename with the selected folder path and passes the full path to Workbooks.Open.

This is different from opening several files by selecting them in Excel’s Open dialog: the VBA loop can repeat the same operation on each workbook and can be extended to inspect, copy, or summarize data. The sample deliberately opens files without editing them. If your goal is to process data, add that work between opening a workbook and moving on to the next filename.

VBA example: choose a folder and open its workbooks

In desktop Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the macro into the module. Press F5 while the cursor is inside the procedure, or return to Excel and run it from Developer > Macros. The macro matches files whose names begin with any Excel-style extension starting with “.xls,” including common .xlsx, .xlsm, .xlsb, and .xls formats.

Sub OpenExcelFilesInFolder()
    Dim folderPath As String
    Dim fileName As String
    Dim fullPath As String
    Dim wb As Workbook

    'Ask the user to select a folder.
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select the folder containing Excel files"
        If .Show <> -1 Then Exit Sub
        folderPath = .SelectedItems(1)
    End With

    'Add a path separator if the selected path lacks one.
    If Right$(folderPath, 1) <> Application.PathSeparator Then
        folderPath = folderPath & Application.PathSeparator
    End If

    'Find files with Excel-style extensions.
    fileName = Dir$(folderPath & "*.xls*")
    Do While Len(fileName) > 0
        'Ignore Excel's temporary lock files and this macro workbook.
        If Left$(fileName, 2) <> "~$" Then
            fullPath = folderPath & fileName
            If StrComp(fullPath, ThisWorkbook.FullName, vbTextCompare) <> 0 Then
                'Open without updating links, and do not allow edits.
                Set wb = Workbooks.Open(Filename:=fullPath, _
                    UpdateLinks:=0, ReadOnly:=True, AddToMru:=False)
                'Add any read-only processing here; the workbook stays open.
            End If
        End If

        'Get the next matching filename.
        fileName = Dir$()
    Loop
End Sub

The code skips files beginning with ~$, which Excel commonly creates as temporary lock files while a workbook is open. It also skips the workbook containing the macro, preventing that workbook from being opened again if it is stored in the selected folder. The UpdateLinks:=0 argument tells Excel not to update external links while opening. ReadOnly:=True reduces the chance of accidentally changing source files; it does not make an untrusted file safe to open.

Choose the right file pattern and folder

Use the narrowest filename pattern that fits the job. If you only need modern .xlsx workbooks, change the search pattern to *.xlsx. The broader *.xls* pattern can include legacy workbooks and macro-enabled or binary workbooks, which may not be appropriate for every process. It does not search subfolders: Dir with this pattern checks only the selected folder. To include subfolders, use a recursive folder routine or another file-system approach.

For a fixed, trusted folder, you can replace the folder-picker section with a folder path assigned in the code. Keep the trailing-separator check, or make sure the path you assign ends with the separator returned by Application.PathSeparator. Avoid relying on a path that exists only on your computer if the workbook will be shared; mapped drives and local folders may have different names or permissions for other users.

Process each workbook or close it afterward

As written, the macro leaves every workbook open, which is useful when you want to inspect the results manually. If you have many files, leaving them all open can consume substantial memory and make Excel harder to manage. For a batch task, process one workbook at a time and close it before the loop advances. For example, after your processing statements, use wb.Close SaveChanges:=False if you intentionally want to discard changes. If the macro changes data that must be retained, save the intended output deliberately before closing; do not assume that opening a workbook read-only will permit saving edits to that source.

A typical processing sequence is to open a source workbook, refer to its sheets through the wb variable, transfer or calculate the required information in the macro workbook, and then close the source. Use explicit workbook references such as wb.Worksheets("Data") rather than relying on whichever workbook happens to be active. This avoids a common error: unqualified references like Range("A1") can point to the active sheet, which may change as files open.

What to consider before running a batch

Make a backup. Save a separate copy of the macro workbook and any source files that might be changed. Ctrl+Z cannot reverse changes made by VBA.
Test on a small folder. Start with two or three copies of representative files. Confirm which files match the pattern and whether the macro should leave them open or close them.
Check trust and permissions. Only open files from sources you trust. Excel’s macro security settings and file-protection prompts still matter; a VBA macro is not a security check for the files it opens.
Plan for volume. Opening hundreds of workbooks at once can use a lot of memory. A one-at-a-time workflow is usually better when the files only need to be read and summarized.
Decide what errors should do. A corrupt, password-protected, unavailable, or incompatible file can cause Workbooks.Open to fail. The example does not include per-file error recovery, so an opening error can stop the macro. For unattended batches, add an error handler that records the failing filename and continues only when that behavior is appropriate.

Common problems and practical fixes

No files open: Check that the selected folder is correct and that the filename pattern matches the file extensions. The code does not search below the chosen folder. Files stored online, in a disconnected network location, or in a folder without access permission may not open normally.

The macro stops partway through: A workbook may be damaged, password-protected, or unavailable, or Excel may display a prompt that requires a response. Test the problem file by opening it manually. If the batch should continue, add controlled error handling and write the filename and error description to a log instead of silently ignoring failures.

Some files appear to be skipped: The macro intentionally skips temporary lock files and the workbook that contains the macro. It also skips files that do not match *.xls*. A file with an unusual extension, such as a CSV, will not match; CSV files need a suitable pattern and may open with different import behavior.

The loop behaves unpredictably after adding more code: Dir maintains its search state. Calling Dir elsewhere inside the loop—for example, from a helper procedure that also searches files—can interfere with the original search. If processing needs its own file search, first store the matching filenames in a collection or use a separate file-enumeration method, then process that stored list.

Excel asks about links or shows security notices: The sample suppresses the external-link update by passing UpdateLinks:=0, but it does not override all prompts or security protections. Do not disable security features merely to make a batch run without interruption. Review the source files and use Excel’s normal trust controls.

Windows, Mac, and Excel for the web

This example targets desktop Excel for Microsoft 365 on Windows. The VBA Editor shortcut there is Alt+F11; on many Mac keyboards, use Option+F11, sometimes with the Fn key depending on keyboard settings. Excel for the web does not run VBA macros, so use the desktop app to run this code.

The core VBA ideas—Dir, a loop, and Workbooks.Open—are also used in desktop Excel for Mac, but folder-picker support, file permissions, and path handling can differ by Excel version and macOS configuration. If the folder picker is unavailable, use a known folder path that is valid on that Mac. Test the macro with a small set of files before relying on it in a cross-platform workflow.

Frequently Asked Questions

How do I open all Excel files in a folder with VBA? Use Dir(folderPath & "*.xlsx") to get the first matching filename, then call Workbooks.Open inside a Do While loop. Call Dir() at the end of each pass to retrieve the next match.

Can VBA open files in subfolders too? The sample does not; its search is limited to one folder. To include subfolders, the macro must enumerate each child folder as well, usually with recursion or a file-system object, and then open the matching files it finds.

How do I open only .xlsx files? Change the search pattern from *.xls* to *.xlsx. This narrows the matches and avoids opening other Excel formats such as .xlsm or .xlsb.

Can I open the files without updating their links? Yes. The sample uses UpdateLinks:=0 in Workbooks.Open. This prevents Excel from updating external workbook links during that opening operation; it does not remove links or guarantee that no other prompts will appear.

Can I close each workbook after processing it? Yes. After the processing code for that file, use wb.Close SaveChanges:=False to close without saving, or choose an appropriate save workflow if you need to retain changes. Make sure the macro has already copied or recorded anything it needs from the source.

When a loop is better than opening files manually

Manual opening is sensible for a small number of files that need individual review. A VBA loop is more useful when the same repeatable task applies to many workbooks, such as reading a known worksheet from each file or consolidating a consistent set of values. The benefit comes from automating the repeated operation, not from opening as many files as possible. A reliable batch macro uses a clear file filter, references each workbook explicitly, handles errors visibly, and closes files when they are no longer needed.

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.