`Dir` Function

Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

Syntax

Dir[(pathname[, attributes])]

Parameters

pathname: Optional. String expression that specifies a file name, directory name, or folder name. May include wildcards (* and ?). If not specified, uses the pattern from the previous Dir call.
attributes: Optional. Constant or numeric expression whose sum specifies file attributes. If omitted, returns files that match pathname but have no attributes.

Attributes

vbNormal (0): Normal files (default)
vbReadOnly (1): Read-only files
vbHidden (2): Hidden files
vbSystem (4): System files
vbVolume (8): Volume label (pathname ignored)
vbDirectory (16): Directories or folders
vbArchive (32): Files that have changed since last backup

Return Value

Returns a String containing the name of a file, directory, or folder that matches the specified pattern and attributes. Returns a zero-length string ("") when no more files are found.

Remarks

The Dir function is used to retrieve file and directory names that match a pattern. It's commonly used to iterate through files in a directory or to check if a file exists.

Important Characteristics:

First call with pathname initializes search and returns first match
Subsequent calls without arguments return next matching file
Returns empty string ("") when no more matches found
Case-insensitive pattern matching
Supports wildcards: * (multiple chars) and ? (single char)
Does not return "." and ".." directory entries
Order of returned files is not guaranteed (typically file system order)
Maintains internal state between calls
Multiple Dir loops cannot be nested without complications
Changing directory during Dir enumeration can cause issues

Wildcards

* - Matches zero or more characters
? - Matches exactly one character
*.* - All files with extensions
*.txt - All .txt files
test?.dat - Files like test1.dat, testA.dat

Examples

Basic Usage

' Get first .txt file in current directory
Dim fileName As String
fileName = Dir("*.txt")
MsgBox fileName

' Check if specific file exists
If Len(Dir("C:\data\report.txt")) > 0 Then
MsgBox "File exists"
End If

' Get volume label
Dim volumeLabel As String
volumeLabel = Dir("C:\", vbVolume)

Iterate Through Files

Sub ListAllTextFiles()
Dim fileName As String

' First call with pattern
fileName = Dir("C:\Documents\*.txt")

' Loop through all matches
Do While fileName <> ""
Debug.Print fileName
fileName = Dir  ' Subsequent calls without arguments
Loop
End Sub

Count Files

Function CountFiles(path As String, pattern As String) As Long
Dim fileName As String
Dim count As Long

count = 0
fileName = Dir(path & "\" & pattern)

Do While fileName <> ""
count = count + 1
fileName = Dir
Loop

CountFiles = count
End Function

Common Patterns

File Existence Check

Function FileExists(filePath As String) As Boolean
FileExists = (Len(Dir(filePath)) > 0)
End Function

' Usage
If FileExists("C:\data\file.txt") Then
' File exists
End If

Get All Files in Directory

Function GetFileList(folderPath As String, pattern As String) As Variant
Dim files() As String
Dim fileName As String
Dim count As Long

count = 0
ReDim files(0 To 100)

fileName = Dir(folderPath & "\" & pattern)
Do While fileName <> ""
files(count) = fileName
count = count + 1

If count > UBound(files) Then
ReDim Preserve files(0 To UBound(files) + 100)
End If

fileName = Dir
Loop

If count > 0 Then
ReDim Preserve files(0 To count - 1)
GetFileList = files
Else
GetFileList = Array()
End If
End Function

Find Files by Attribute

Sub ListHiddenFiles(folderPath As String)
Dim fileName As String

fileName = Dir(folderPath & "\*.*", vbHidden)
Do While fileName <> ""
Debug.Print "Hidden: " & fileName
fileName = Dir
Loop
End Sub

Sub ListDirectories(folderPath As String)
Dim dirName As String

dirName = Dir(folderPath & "\*.*", vbDirectory)
Do While dirName <> ""
' Filter out "." and ".." if they appear
If dirName <> "." And dirName <> ".." Then
' Check if it's actually a directory
If GetAttr(folderPath & "\" & dirName) And vbDirectory Then
Debug.Print "Directory: " & dirName
End If
End If
dirName = Dir
Loop
End Sub

Search Multiple File Types

Function FindDocuments(folderPath As String) As Variant
Dim files() As String
Dim fileName As String
Dim count As Long
Dim extensions As Variant
Dim i As Integer

extensions = Array("*.txt", "*.doc", "*.docx", "*.pdf")
ReDim files(0 To 100)
count = 0

For i = LBound(extensions) To UBound(extensions)
fileName = Dir(folderPath & "\" & extensions(i))
Do While fileName <> ""
files(count) = fileName
count = count + 1

If count > UBound(files) Then
ReDim Preserve files(0 To UBound(files) + 100)
End If

fileName = Dir
Loop
Next i

If count > 0 Then
ReDim Preserve files(0 To count - 1)
FindDocuments = files
Else
FindDocuments = Array()
End If
End Function

Get Full File Paths

Function GetFullPaths(folderPath As String, pattern As String) As Variant
Dim paths() As String
Dim fileName As String
Dim count As Long

count = 0
ReDim paths(0 To 100)

fileName = Dir(folderPath & "\" & pattern)
Do While fileName <> ""
paths(count) = folderPath & "\" & fileName
count = count + 1

If count > UBound(paths) Then
ReDim Preserve paths(0 To UBound(paths) + 100)
End If

fileName = Dir
Loop

If count > 0 Then
ReDim Preserve paths(0 To count - 1)
GetFullPaths = paths
Else
GetFullPaths = Array()
End If
End Function

Delete All Files Matching Pattern

Sub DeleteMatchingFiles(folderPath As String, pattern As String)
Dim fileName As String
Dim fullPath As String

fileName = Dir(folderPath & "\" & pattern)
Do While fileName <> ""
fullPath = folderPath & "\" & fileName

' Get next file BEFORE deleting (Dir state would be lost)
fileName = Dir

' Delete the file
Kill fullPath
Loop
End Sub

Find Newest File

Function GetNewestFile(folderPath As String, pattern As String) As String
Dim fileName As String
Dim newestFile As String
Dim newestDate As Date
Dim currentDate As Date
Dim fullPath As String

newestDate = 0
fileName = Dir(folderPath & "\" & pattern)

Do While fileName <> ""
fullPath = folderPath & "\" & fileName
currentDate = FileDateTime(fullPath)

If currentDate > newestDate Then
newestDate = currentDate
newestFile = fileName
End If

fileName = Dir
Loop

GetNewestFile = newestFile
End Function

Calculate Total Size

Function GetTotalFileSize(folderPath As String, pattern As String) As Double
Dim fileName As String
Dim totalSize As Double
Dim fullPath As String

totalSize = 0
fileName = Dir(folderPath & "\" & pattern)

Do While fileName <> ""
fullPath = folderPath & "\" & fileName
totalSize = totalSize + FileLen(fullPath)
fileName = Dir
Loop

GetTotalFileSize = totalSize
End Function

Recursive Directory Search

Sub SearchRecursive(folderPath As String, pattern As String)
Dim fileName As String
Dim dirName As String
Dim fullPath As String

' Search files in current directory
fileName = Dir(folderPath & "\" & pattern)
Do While fileName <> ""
Debug.Print folderPath & "\" & fileName
fileName = Dir
Loop

' Search subdirectories
dirName = Dir(folderPath & "\*.*", vbDirectory)
Do While dirName <> ""
If dirName <> "." And dirName <> ".." Then
fullPath = folderPath & "\" & dirName
If GetAttr(fullPath) And vbDirectory Then
SearchRecursive fullPath, pattern
End If
End If
dirName = Dir
Loop
End Sub

Advanced Usage

File Filter with Multiple Criteria

Function FindFilesAdvanced(folderPath As String, _
minSize As Long, maxSize As Long, _
afterDate As Date) As Variant
Dim files() As String
Dim fileName As String
Dim fullPath As String
Dim fileSize As Long
Dim fileDate As Date
Dim count As Long

count = 0
ReDim files(0 To 100)

fileName = Dir(folderPath & "\*.*")
Do While fileName <> ""
fullPath = folderPath & "\" & fileName
fileSize = FileLen(fullPath)
fileDate = FileDateTime(fullPath)

If fileSize >= minSize And fileSize <= maxSize And fileDate > afterDate Then
files(count) = fileName
count = count + 1

If count > UBound(files) Then
ReDim Preserve files(0 To UBound(files) + 100)
End If
End If

fileName = Dir
Loop

If count > 0 Then
ReDim Preserve files(0 To count - 1)
FindFilesAdvanced = files
Else
FindFilesAdvanced = Array()
End If
End Function

Safe Dir Loop Helper

' Helper to avoid nested Dir issues
Type FileInfo
Name As String
FullPath As String
Size As Long
Modified As Date
End Type

Function GetFileInfoList(folderPath As String, pattern As String) As Variant
Dim files() As FileInfo
Dim fileName As String
Dim count As Long

count = 0
ReDim files(0 To 100)

fileName = Dir(folderPath & "\" & pattern)
Do While fileName <> ""
files(count).Name = fileName
files(count).FullPath = folderPath & "\" & fileName
files(count).Size = FileLen(files(count).FullPath)
files(count).Modified = FileDateTime(files(count).FullPath)

count = count + 1
If count > UBound(files) Then
ReDim Preserve files(0 To UBound(files) + 100)
End If

fileName = Dir
Loop

If count > 0 Then
ReDim Preserve files(0 To count - 1)
GetFileInfoList = files
Else
GetFileInfoList = Array()
End If
End Function

Backup Old Files

Sub BackupOldFiles(sourcePath As String, backupPath As String, daysOld As Integer)
Dim fileName As String
Dim fullPath As String
Dim cutoffDate As Date

cutoffDate = Date - daysOld
fileName = Dir(sourcePath & "\*.*")

Do While fileName <> ""
fullPath = sourcePath & "\" & fileName

If FileDateTime(fullPath) < cutoffDate Then
FileCopy fullPath, backupPath & "\" & fileName
End If

fileName = Dir
Loop
End Sub

Build File Index

Function BuildFileIndex(rootPath As String) As Collection
Dim index As New Collection
Dim fileName As String

' Add all files to collection with full path as key
fileName = Dir(rootPath & "\*.*")
Do While fileName <> ""
On Error Resume Next
index.Add fileName, UCase(fileName)
On Error GoTo 0
fileName = Dir
Loop

Set BuildFileIndex = index
End Function

File Synchronization Check

Function CompareDirectories(path1 As String, path2 As String) As String
Dim files1 As Collection
Dim files2 As Collection
Dim fileName As String
Dim report As String

Set files1 = New Collection
Set files2 = New Collection

' Get files from first directory
fileName = Dir(path1 & "\*.*")
Do While fileName <> ""
files1.Add fileName
fileName = Dir
Loop

' Get files from second directory
fileName = Dir(path2 & "\*.*")
Do While fileName <> ""
files2.Add fileName
fileName = Dir
Loop

' Compare (simplified - full version would check both ways)
report = "Files only in " & path1 & ":" & vbCrLf
' ... comparison logic ...

CompareDirectories = report
End Function

Generate Directory Listing Report

Sub ExportDirectoryListing(folderPath As String, outputFile As String)
Dim fileName As String
Dim fullPath As String
Dim fileNum As Integer

fileNum = FreeFile
Open outputFile For Output As #fileNum

Print #fileNum, "Directory Listing for: " & folderPath
Print #fileNum, "Generated: " & Now
Print #fileNum, String(80, "-")
Print #fileNum, "Filename" & vbTab & "Size" & vbTab & "Modified"
Print #fileNum, String(80, "-")

fileName = Dir(folderPath & "\*.*")
Do While fileName <> ""
fullPath = folderPath & "\" & fileName
Print #fileNum, fileName & vbTab & _
FileLen(fullPath) & vbTab & _
FileDateTime(fullPath)
fileName = Dir
Loop

Close #fileNum
End Sub

Error Handling

Function SafeDir(pathname As String, Optional attributes As Integer = 0) As String
On Error Resume Next
SafeDir = Dir(pathname, attributes)
If Err.Number <> 0 Then
SafeDir = ""
End If
End Function

Function SafeFileExists(filePath As String) As Boolean
On Error Resume Next
SafeFileExists = (Len(Dir(filePath)) > 0)
If Err.Number <> 0 Then
SafeFileExists = False
End If
End Function

Common Errors

Error 52 (Bad file name or number): Invalid pathname or pattern
Error 76 (Path not found): Directory does not exist
Error 68 (Device unavailable): Drive not ready or network path unavailable

Performance Considerations

Dir is relatively fast for simple file enumeration
For large directories, consider showing progress
Network paths can be slow; consider timeout handling
Avoid nested Dir loops (collect to array first)
FileSystemObject may be faster for complex operations
Cache results if scanning same directory repeatedly

Best Practices

Always Check for Empty String

' Good - Check for no more files
fileName = Dir("*.txt")
Do While fileName <> ""
' Process file
fileName = Dir
Loop

' Avoid - May cause infinite loop
fileName = Dir("*.txt")
Do While Len(fileName) > 0  ' Less reliable
fileName = Dir
Loop

Store Files Before Processing

' Good - Collect files first
Dim files() As String, i As Integer
ReDim files(0 To 100)
count = 0

fileName = Dir("*.txt")
Do While fileName <> ""
files(count) = fileName
count = count + 1
fileName = Dir
Loop

' Now process without Dir active
For i = 0 To count - 1
ProcessFile files(i)
Next i

Use Absolute Paths

' Good - Explicit path
fileName = Dir("C:\Data\*.txt")

' Risky - Depends on current directory
fileName = Dir("*.txt")

Handle No Matches Gracefully

fileName = Dir("*.xyz")
If fileName = "" Then
MsgBox "No matching files found"
Exit Sub
End If

Comparison with Other Methods

`Dir` vs `FileSystemObject`

' Dir - Built-in, faster for simple cases
fileName = Dir("C:\Data\*.txt")

' FileSystemObject - More features, but requires reference
Dim fso As New FileSystemObject
Dim folder As Folder
Set folder = fso.GetFolder("C:\Data")
' ... more complex but more powerful

`Dir` vs `File Dialog`

' Dir - Programmatic file discovery
fileName = Dir("*.txt")

' File Dialog - User selection
fileName = Application.GetOpenFilename("Text Files (*.txt), *.txt")

Limitations

Cannot nest Dir loops reliably (single internal state)
Does not return files in sorted order
Returns only file names, not full paths
No built-in recursion into subdirectories
Cannot filter by date, size, or other attributes directly
Changing current directory during enumeration causes issues
Limited attribute filtering compared to FileSystemObject

GetAttr: Gets file attributes
SetAttr: Sets file attributes
FileLen: Returns file size
FileDateTime: Returns file modification date/time
CurDir: Returns current directory
ChDir: Changes current directory
MkDir: Creates directory
RmDir: Removes directory
Kill: Deletes file
FileCopy: Copies file
Name: Renames/moves file

Dir Function