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 an 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 which 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 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 a 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 following values:

Attribute Name Description
vbNormal Normal (Default). Specifies files with no attributes.
vbReadOnly Specifies read-only files as well as files with no attributes.
vbHidden Specifies hidden files as well as files with no attributes.
vbSystem Specifies System files as well as files with no attributes.
vbVolume Specifies volume label; If you use any other attribute with this one then, volume label is ignored.
vbDirectory Specifies Directories or Folders.
vbArchive Specifies Archives or Backup Files.
vbAlias Specifies Files having different names.

 

Few Important points about VBA DIR Function:

  • Both the arguments in DIR Function are optional.
  • You can use 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 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 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 though all the folders inside a path (immediate child folders only).

To do this we can use 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, first time with two arguments and second time with no arguments to fetch the subsequent files.

Example 7: Create a VBA code that can iterate though 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

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.

Privacy Policy | Disclaimer | About