In Excel VBA, the Dir function finds a file or folder that matches a path and optional wildcard pattern. Call it once with a path to get the first match, then call Dir() repeatedly to get the next matches until it returns an empty string.
What the Dir function does
Dir is a built-in VBA function for checking and listing file-system entries. It is not a worksheet formula: you use it in VBA code, such as a macro, to locate files before opening, importing, or processing them. For example, a macro can use it to find every Excel workbook in a reports folder.
A call returns a name, not a complete path. If you search C:\Reports\*.xlsx, a result might be January.xlsx, so combine that name with the folder path when you need the full location. If nothing matches, or there are no more matches in the current search, Dir returns a zero-length string, written as "".
The function is useful for simple, non-recursive searches. It does not open a file, read its contents, or search through subfolders automatically. Those tasks require additional VBA code or another file-handling approach.
Syntax, arguments, and return value
The VBA syntax is Dir[(pathname, [attributes])]. The optional square brackets in that notation mean the attributes argument is optional; they are not characters to type into your code.
- pathname: A file or folder path. It can include wildcards such as
*and?. This is required to start a search. - attributes: Optional constants that affect which entries are included. If omitted, the search uses the default behavior for files with no attributes.
- return value: A string containing one matching name. An empty string means there is no match to return.
After the first call, omit the path to continue the same search. For instance, Dir(searchPath) starts the enumeration and Dir() advances it. Supplying a new path starts a different search; it does not ask for the next result from the previous one.
List matching files in a folder
This example prints the names of normal Excel workbooks in a Windows folder to the Immediate window in the VBA editor. It assumes the folder path ends in a backslash, so concatenating it with a returned name makes a valid full path.
Save a copy of your workbook before running any macro. VBA changes cannot be undone with Ctrl+Z. The example below only prints file paths, but the same precaution matters especially if you later add code that edits or deletes files.
Sub ListXlsxFiles()
Dim folderPath As String
Dim fileName As String
' The folder path ends with a backslash.
folderPath = "C:\Reports\"
' Start the search, then request each next match.
fileName = Dir(folderPath & "*.xlsx", vbNormal)
Do While Len(fileName) > 0
Debug.Print folderPath & fileName
fileName = Dir()
Loop
End Sub
To use it, open the VBA editor with Alt+F11 in Excel for Windows, insert the procedure in a standard module, and change the folder path. Run the macro, then view the Immediate window in the editor. On Excel for Mac, Option+F11 opens the VBA editor on many keyboards; you may need Fn as well. Excel for the web cannot run VBA macros.
The loop tests the returned name before processing it. That check is important: once the search is exhausted, Dir() returns "", and attempting to build or open a path from that empty result could lead to incorrect logic.
Wildcards and file attributes
Wildcards let one search match several names. An asterisk (*) stands for zero or more characters, while a question mark (?) stands for one character. For example, *.csv finds names ending in .csv, and Report_??.xlsx matches names such as Report_01.xlsx with exactly two characters in that part of the name. A wildcard search still checks only the folder named in the path.
The optional attributes argument uses VBA constants. Common values include:
vbNormal(0): files with no attributes; this is the value used in the listing example.vbReadOnly(1),vbHidden(2), andvbSystem(4): entries with those respective attributes.vbDirectory(16): includes directory entries in a search.vbArchive(32): entries with the archive attribute.
Constants can be combined with the VBA Or operator when a search should include more than one attribute. The exact filter you need depends on the files in the folder: a search using vbNormal may not include a file marked read-only or hidden. Also, vbDirectory is used to include directories; do not treat it as a guarantee that every returned name is a folder. If you need to distinguish a folder from a file, inspect its attributes rather than relying on the returned name alone.
Checking a path and handling folders
A common use is to check whether a known file exists before trying to open it. A simple check is to call Dir with the full file path and test whether the result has a nonzero length. Keep in mind that the attributes you request can affect whether a file is found. If the target may be hidden or read-only, choose attributes that account for that possibility instead of assuming the default search covers every file.
When joining a folder and a filename, make sure there is exactly one path separator between them. The listing example avoids a missing separator by ending folderPath with \. If paths are built from separate components, VBA’s Application.PathSeparator can help supply the current application’s separator, but it does not fix an otherwise invalid path.
To search folders as well as files, use a directory-aware search and check the attributes of each returned entry. A folder can itself contain another folder, but Dir will not descend into it for you. A recursive search needs additional logic to visit each subfolder, and that is where the function’s enumeration limitation becomes especially important.
Important limitations and common mistakes
- Forgetting to advance: Calling
Dir(path)repeatedly restarts the search. CallDir()after the first call to get subsequent matches. - Assuming results are full paths: The function normally returns the matching name. Add the folder path yourself when you need a complete path.
- Expecting a sorted list: Do not rely on
Dirto return names alphabetically or in another particular order. Sort the collected names separately if order matters. - Trying to nest searches:
Dirmaintains the state for its current enumeration. Starting another search while looping through the first can disrupt the sequence. This makes a straightforward recursive directory walk with repeatedDircalls unreliable. - Expecting a recursive search: A pattern such as
*.xlsxmatches entries in the specified folder, not every subfolder below it. - Ignoring errors or attributes: A misspelled path, inaccessible location, or unexpected file attribute can prevent the match you expect. An empty result means no matching entry was returned; it does not explain why.
For nested folder work, one practical design is to collect the names from the current folder before starting another enumeration, then process those names afterward. For more involved traversal, use a file-system object or another method designed for managing folders and their contents. Avoid putting a second Dir(path) call inside the first search loop unless you have deliberately preserved the first search’s results.
Dir compared with other file-handling options
Choose Dir when the job is small and specific: test for a known file, or loop through matching names in one folder. It is concise and needs no extra object library reference. For example, printing every workbook name in one input folder is a natural fit.
Consider the FileSystemObject when you need a more structured way to work with files and folders, such as examining folder objects or traversing a directory tree. It can be used from VBA through the Scripting.FileSystemObject object model. Some projects set a reference to the Microsoft Scripting Runtime; late binding is another option, though it gives less help from the VBA editor while writing code. This is a separate approach, not a more powerful mode of Dir.
For importing data rather than merely locating files, Excel’s interface tools may be preferable. Power Query can combine files from a folder through Excel’s Data tools, which is often easier to refresh and inspect than a custom macro. The right choice depends on whether you need a quick VBA check, repeatable folder traversal, or a refreshable data-import workflow.
Platform and macro safety notes
The examples here use Excel for Microsoft 365 on Windows and Windows-style paths such as C:\Reports\. A Mac does not use a Windows drive-letter path, so adapt the path to the Mac file system and check the behavior of any attributes your code relies on. Excel for the web does not execute VBA; use desktop Excel for these macros. If a procedure will be shared, test it on the operating systems and folder locations its users will actually use.
Dir itself only returns names; it does not delete or modify a file. The risk comes from what the macro does with those names afterward. Run code only when you understand its actions, particularly if it opens, overwrites, moves, or deletes files. Keep a backup and test file-handling changes on a sample folder first.
Frequently Asked Questions
What does Dir() return when there are no more files?
It returns an empty string, "". A loop commonly uses Len(fileName) > 0 to keep processing results and stops when that test becomes false.
How do I get the next file with Dir in VBA?
Start with a call that supplies a pathname, such as Dir(folderPath & "*.xlsx"). After processing that result, call Dir() without arguments to request the next match in that search.
Can Dir search subfolders automatically?
No. A wildcard search applies to the folder in its pathname and does not recurse. To include subfolders, write folder-traversal logic or use a file-system object.
Does Dir return the full file path?
Usually it returns the matching name, not the full path. Retain the folder path and join it to the returned name when you need to open or otherwise refer to the file.
Can I use Dir in an Excel worksheet cell?
No. Dir is a VBA function, not a worksheet function. Call it from VBA code in desktop Excel; it is not available as a formula in a cell or as a macro in Excel for the web.
Related
- Excel Slicers for Multiple PivotTables: How to Connect
- Excel Named Ranges in Data Validation: How to Use Them
- Excel Unable to Remove Duplicates? Common Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: How to Edit a PivotTableYes. In Excel for Microsoft 365 on Windows, VBA can edit a PivotTable by changing its fields, layout,…
- TMG Macro Recorder? Excel’s Built-In Option Explained“TMG macro recorder” is not the name of a standard feature in Microsoft Excel. If you mean recording…
- Excel VBA UserForm Dynamic List: Populate and UpdateTo create a dynamic list in an Excel VBA UserForm, clear a ListBox or ComboBox and refill it…
- How to Activate a SteelSeries MacroTo activate a SteelSeries macro, open SteelSeries GG, select your supported device under Engine, assign a saved macro…
- VBA Copy Paste Row: Excel Code and Common FixesTo copy an entire row to another row in Excel with VBA, use Rows(sourceRow).Copy Destination:=Rows(targetRow). For example, Worksheets("Data").Rows(5).Copy…
- Excel Macro to Get Row Count: Choose the Right VBA MethodTo count data rows in an Excel macro, count a column that has one nonblank entry for every…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.