Excel VBA: The Dir Function

In Microsoft Excel VBA, the Dir function is used to return the first filename from a specified directory, and list of attributes. The filename is returned as a string.

The Dir function can then be entered without any arguments to return the next filename from that directory.

The most common use of the Dir function is to loop through all the files in a folder to perform an action on each one. Other common uses include checking if a file or a directory exists, or to look for a specific file such as the latest one.

Dir Function Syntax

When entering the Dir function into the Visual Basic Editor the quick info list should appear and prompt for the following.

Dir( [PathName], [Attributes As VbFileAttribute = vbNormal]) As String

Pathname: The directory or folder that you want to use. This can be entered as a string, or a variable that contains one. It should include the backslash (\) at the end.

The filename can be entered if you are looking for a specific file. Wildcard characters can also be used to identify types of files, such as (*.xlsx) for a workbook.

Attributes: A list of attributes is shown in the table below. This is optional and a combination can be used. Attributes can be included for a more targeted match.

For example the vbDirectory attribute can be used to specify that you want to return a directory and not a filename.

Attribute	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	Filename is an alias

Examples

Example 1 – Loop Through the Files in a Folder

Sub AllFiles()

Dim MyFolder As String ‘Path containing the files for looping

Dim MyFile As String ‘Filename obtained by Dir function

 

MyFolder = “C:\ExcelFiles” ‘Assign directory to MyFolder variable

MyFile = Dir(MyFolder) ‘Dir gets the first file of the folder

‘Loop through all files until Dir cannot find anymore

Do While MyFile <> ""

    The statements you want to run on each file

   

    MyFile = Dir ‘Dir gets the next file in the folder

Loop

End Sub

Example 2 – List the Files from a Folder on a Worksheet

Sub ListFiles()

Dim MyDirectory As String ‘Folder containing the files

Dim MyFile As String ‘The filename to enter on the worksheet

Dim NextRow As Long ‘The row for the next filename in list

MyDirectory = “C:\ExcelFiles” ‘Assign directory to MyDirectory variable

MyFile = Dir(MyDirectory) ‘Dir gets the first file in the folder

‘Find the next empty row in the list and store in NextRow variable

NextRow = Application.CountA(Range(“A:A”)) + 1

Do Until MyFile = “”

      Cells(NextRow, 1).value = MyFile

     

      NextRow = NextRow + 1 ‘Move to the next row

      MyFile = Dir ‘Dir gets the name of next file in the folder

Loop

End Sub

Example 3 – Check if a Files Exists

Sub FileExists()

      Dim TheFolder As string ‘Location of the file

      Dim FiletoCheck As String ‘Name of the file you want to check

TheFolder = “C:\ExcelFiles” ‘Assign directory to TheFolder variable

‘Capture the name of file to check for using an input box

FiletoCheck = InputBox(“Enter the name of the file you want to look for”, “Enter file name”)

‘If FiletoCheck is an empty string then file not found

If FiletoCheck = “” Then

     

      Msgbox “Oh no, the file does not exist”

Else

      Msgbox “Yes, the file exists.”

End If

End Sub

-->