VBA Dir Function in Excel

VBA Dir function in Excel is categorized as File and Directory function. This built-in VBA Dir function returns the first name of a file or directory that matches a pattern in Excel VBA.The VBA Dir function is specifically useful for listing all files located in a specified directory.

This function can be used in either procedure or function in a VBA editor window in Excel. We can use this VBA Dir Function in any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the Dir function, where we can use this Dir Function and real-time examples in Excel VBA.

Table of Contents:

Overview
Syntax of VBA Dir Function
Parameters or Arguments
Where we can apply or use VBA Dir Function?
Example 1: Check whether file exists or not?
Example 2: List all .xls Files in a Folder using Wild card character(*)
Example 3: List all Files in a Folder using Wild card character(?)
Example 4: List all files in a ‘C’ Directory using File Attribute(vbDirectory)
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax of VBA Dir Function

The syntax of the Dir Function in VBA is

Dir([PathName],[Attribute])

Note: The Dir Function returns a string value.

Parameters or Arguments:

The Dir function has two arguments in Excel VBA.
where
PathName: It is an optional parameter. The PathName argument represents a file, folder or directory. If specified path is not available, then DIR function will return a zero-length string.
Attribute: It is an optional parameter. The Attribute represents the file attributes. These file attributes can be one or a combination of the following values.

VBA CONSTANT	VALUE	DESCRIPTION
vbNormal	0	Normal (Default)
vbReadOnly	1	Read-only
vbHidden	2	Hidden
vbSystem	4	System file
vbVolume	8	Volume label
vbDirectory	16	Directory or folder
vbAlias	64	File name is an alias

Note: We can use wildcard characters, when we want to specify multiple file attributes. We can use either * and ? as wildcard characters.

Wild Card Character	Description
*	Allows you to match any string of any length (including zero length)
?	Allows you to match on a single character

Where we can apply or use VBA Dir Function?

We can use this Dir Function in VBA MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

Example 1: Check whether file exists or not

Here is a simple example of the VBA Dir function. This below example checks whether file exists or not.

'Check whether file exists or not
Sub VBA_Dir_Function_Ex1()

    'Variable declaration
    Dim sPath As String
    Dim sStatus As String
    
    sPath = "C:\Temp\VBAF1\String_Functions.xls"
             
    sStatus = Dir(sPath)
    
    If sStatus <> "" Then
        MsgBox "Specified file found.", vbInformation, "VBA Dir Function"
    Else
        MsgBox "Specified file not found.", vbInformation, "VBA Dir Function"
    End If
    
End Sub

Output: Here is the screen shot of the first example output.

Example 2: List all .xls Files in a Folder using Wild card character(*)

Here is a simple example of the VBA Dir function. This below example lists all .xls Files in a folder using Wild card character(*) and returns all available .xls files.

'List all .xls Files in a Folder using Wild card character(*)
Sub VBA_Dir_Function_Ex2()

    'Variable declaration
    Dim sPath As String
    Dim sFile As String, fList As String
    
    sPath = "C:\VBAF1\VBA Functions\VBA Text Functions\*.xls"
             
    'Read current file
    sFile = Dir(sPath)
       
    'Loop through all files in a directory
    Do While sFile <> ""
        fList = fList & vbCrLf & sFile
        sFile = Dir()
    Loop
        
    MsgBox "List of all .xls files: " & fList, vbInformation, "VBA Dir Function"
       
End Sub

Output: Here is the screen shot of the second example output.

Example 3: List all Files in a Folder using Wild card character(* and ?)

Here is a simple example of the VBA Dir function. This below example lists all .doc Files in a folder using Wild card character(* and ?) and returns all available .doc files.

'List all Files in a Folder using Wild card character(?)
Sub VBA_Dir_Function_Ex3()

    'Variable declaration
    Dim sPath As String
    Dim sFile As String, fList As String
    
    sPath = "C:\VBAF1\VBA Functions\VBA Text Functions\*.doc?"
             
    'Read current file
    sFile = Dir(sPath)
       
    'Loop through all files in a directory
    Do While sFile <> ""
        fList = fList & vbCrLf & sFile
        sFile = Dir()
    Loop
        
    MsgBox "List of all .xls files: " & fList, vbInformation, "VBA Dir Function"
       
End Sub

Output: Here is the screen shot of the third example output.

Example 4: List all files in a ‘C’ Directory using File Attribute(vbDirectory)

Here is a simple example of the VBA Dir function. This below example lists all available files from ‘C’ Directory. It uses file attribute vbDirectory in the following example.

'List all files in a 'C' Directory using File Attribute(vbDirectory)
Sub VBA_Dir_Function_Ex4()

    'Variable declaration
    Dim sPath As String
    Dim sFile As String, fList As String
    
    sPath = "C:\"
             
    'Read current file
    sFile = Dir(sPath, vbDirectory)
       
    'Loop through all files in a directory
    Do While sFile <> ""
        fList = fList & vbCrLf & sFile
        sFile = Dir()
    Loop
        
    MsgBox "List of all files in 'C' Directory: " & fList, vbInformation, "VBA Dir Function"
       
End Sub

Output: Here is the screen shot of the fourth example output.