VBA DIR Function – How to Use in Excel

DIR is a very special function in VBA, its job is to return a string representing the name of a file, directory, or archive that matches a specified pattern. DIR function only returns the first file name or folder name from a location that matches the specified attributes.

To fetch other file names or folder names from that location that match previously specified attributes, you need to call the DIR Function again with no arguments. This property of DIR Function can be quite useful in iterating or enlisting files or directories present inside a location.

Please note that the DIR function can just give you the name of the file. But if you also need attributes of a file (like last modified date, size, etc.) then consider using FileObjectSystem.

VBA DIR Example

Syntax of VBA DIR Function:

The syntax of DIR is as follows:

DIR(pathname, attributes)

Here, ‘pathname’ specifies the location of a file, folder, or directory. If the ‘pathname’ is not found, DIR returns a string of zero length.

attributes’ is an optional argument. It can be anyone or summation of the following values:

Attribute NameDescription
vbNormalNormal (Default). Specifies files with no attributes.
vbReadOnlySpecifies read-only files as well as files with no attributes.
vbHiddenSpecifies hidden files as well as files with no attributes.
vbSystemSpecifies System files as well as files with no attributes.
vbVolumeSpecifies volume label; If you use any other attribute with this one then, the volume label is ignored.
vbDirectorySpecifies Directories or Folders.
vbArchiveSpecifies Archives or Backup Files.
vbAliasSpecifies Files having different names.

 

Few Important points about VBA DIR Function

  • Both the arguments in DIR Function are optional.
  • You can use the wildcard character (like: ‘?’ or ‘*’) with DIR to specify multiple files.
    • ‘*’ allows you to match any string of any length (including zero-length)
    • ‘?’ allows you to match any single character.
  • You must call the DIR Function along with the ‘pathname’ parameter for the first time. Subsequent calls to DIR Function can be made with no arguments to retrieve the next item.

5 Beginner Level Examples of DIR Function in VBA

Example 1: Supplying a file path to DIR Function

  • Dir("C:\SomeFile.txt") would return SomeFile.txt
  • Dir("C:\Some*.txt") would return SomeFile.txt (Provided there is no other file that starts with the word “Some”)
  • Dir("C:\SomeFil?.txt") would return SomeFile.txt

Example 2: Write a VBA code to retrieve the first .exe file from the Windows folder.

You can use the below code to do this:

Sub RetrieveFile()
File = Dir("C:\Windows\*.exe")
MsgBox File
End Sub

Explanation: This code retrieves the .exe file from the Windows folder and displays its name in a message box. If there are multiple .exe files inside the Windows Folder then this code will retrieve only the first one.

 Example 3: Use DIR Function to check if a File exists or not.

Below is the code to check this:

Sub RetrieveFile()
File = Dir("C:\Windows\CSUP.txt")
If Len(File) > 0 Then
MsgBox (File & " Exists")
Else
MsgBox ("File Doesn't Exists")
End If
End Sub

Explanation: In this code, we have supplied the filename to the DIR Function. If DIR returns a string whose length is greater than 0 that means “File Exists”. We have checked this by using an IF Function.

Example 4: Write a VBA Code to check if a directory is present or not. If the directory is not present then create that directory.

To accomplish this we can use the below code:

Sub RetriveFolder()
MyFolder = "C:\TestDirectory"
Fldr = Dir(MyFolder, vbDirectory)
If Len(Fldr) > 0 Then
MsgBox (Fldr & " Already Exists")
Else
MkDir MyFolder
MsgBox ("Folder Created")
End If
End Sub

Explanation: In this code, we have supplied the folder path to the DIR Function. If DIR returns a string whose length is greater than 0 that means “Folder Exists” otherwise that Folder will be created.

Example 5: The Folder “C:\Test\” contains all hidden files. Write a VBA Code to retrieve the name of the first hidden file.

Sub RetrieveFile()
MyFile = Dir("C:\Test\*.*", vbHidden)
MsgBox MyFile
End Sub

Explanation: In this code, the DIR function will search the files with hidden attribute inside the “C:\Test\” folder. If this folder contains multiple hidden files then, DIR will return the first hidden file.

3 Advanced Level Examples of DIR Function in Excel

Example 6: Create a VBA code that can iterate through all the folders inside a path (immediate child folders only).

To do this we can use the following code:

Sub Iterate_Folders()
Dim ctr As Integer
ctr = 1
Path = "C:\Windows\ " ' Path should always contain a '\' at end
FirstDir = Dir(Path, vbDirectory) ' Retrieving the first entry.
Do Until FirstDir = "" ' Start the loop.
If (GetAttr(Path & FirstDir) And vbDirectory) = vbDirectory Then
ActiveSheet.Cells(ctr, 1).Value = Path & FirstDir
ctr = ctr + 1
End If
FirstDir = Dir() ' Getting next entry.
Loop
End Sub

Explanation: In this code, we have used a Do Until Loop and DIR Function to iterate through all the folders present inside a location. It writes the result in the “A:A” range of the active sheet.

Notice that we have used DIR Function twice in this code, the first time with two arguments and the second time with no arguments to fetch the subsequent files.

Example 7: Create a VBA code that can iterate through all the files present at a location. (No need to list files under subfolders)

You can use the below code to accomplish this:

Sub Iterate_Files()
Dim ctr As Integer
ctr = 1
Path = "C:\Windows\ " ' Path should always contain a '\' at end
File = Dir(Path) ' Retrieving the first entry.
Do Until File = "" ' Start the loop.
ActiveSheet.Cells(ctr, 1).Value = Path & File
ctr = ctr + 1
File = Dir() ' Getting next entry.
Loop
End Sub

Explanation: In this code, we have used a looping statement along with DIR Function to iterate through all the files present inside a location. It writes the result in “A:A” range of active sheet.

Example 8: Use the concept of the above two examples and write a VBA code that enlists all the files inside a current location and its subfolder.

For this example you can use the following code:

Sub Retrieve_File_listing()
Worksheets(1).Cells(2, 1).Activate
Call Enlist_Directories("C:\Users\Ankit\Desktop\ExcelTrick\ ", 1)
End Sub
Public Sub Enlist_Directories(strPath As String, lngSheet As Long)
Dim strFldrList() As String
Dim lngArrayMax, x As Long
lngArrayMax = 0
strFn = Dir(strPath & "*.*", 23)
While strFn <> ""
If strFn <> "." And strFn <> ".." Then
If (GetAttr(strPath & strFn) And vbDirectory) = vbDirectory Then
lngArrayMax = lngArrayMax + 1
ReDim Preserve strFldrList(lngArrayMax)
strFldrList(lngArrayMax) = strPath & strFn & "\"
Else
ActiveCell.Value = strPath & strFn
Worksheets(lngSheet).Cells(ActiveCell.Row + 1, 1).Activate
End If
End If
strFn = Dir()
Wend
If lngArrayMax <> 0 Then
For x = 1 To lngArrayMax
Call Enlist_Directories(strFldrList(x), lngSheet)
Next
End If
End Sub

Explanation: This code first iterates through all the folders present inside a location and stores them in an array. Later it recursively calls the ‘Enlist_Directories’ function to retrieve the file names.

So, this was all about VBA DIR Function in Excel.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.