VBA Dir Function

VBA Dir Function

Overview of VBA Dir Function:


VBA Dir function is categorized as File and Direcetory function. This built-in VBA Dir function returns the first name of a file or diectory 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.

Syntax of VBA Dir Function

The syntax of the Dir Function in VBA is

Dir([PathName],[Attribute])

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.
VBA Dir Function

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.
VBA Dir Function

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.
VBA Dir Function

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.
VBA Dir Function

Instructions to use Macro Codes

Here are the step by step instructions to use the Dir Function in Excel VBA.

  • Open an Excel workbook.
  • Press Alt+F11 to open VBA Editor window.
  • Go to Insert menu and click on module from the available options.
  • Copy above specified macros to the VBA editor.
  • Select any procedure from above codes and click on ‘Run’ command or use Keyboard shortcut ‘F5’.

List of VBA Functions:

Please click on below link to go back to see all List of VBA Functions.

VBA File & Directory Functions List of VBA Functions

Leave a Comment

Your email address will not be published. Required fields are marked *