How to use VBA DIR function in Excel (7 Examples)

Excel provides several VBA functions that can be used while writing a macro or to define your own function to perform your desired tasks. In this article, we are going to show you a VBA function called DIR.

Practice Workbook

You are welcome to download the workbook from the link below.

Our workbook only contains the macro codes.

The DIR Function

1. Basics of DIR

Summary

The VBA DIR function returns the name of a file or directory from a given folder path. Conventionally It returns the first file.

Syntax

Arguments

There are a few pre-defined attributes, they are

Versions

Workable from Excel 2000.

2. Seven Common Uses of DIR

I. Find the Filename from Path

From the description, you might have understood that the DIR function provides the file name from the provided pathname. Let’s understand it by example.

Here we have created a directory Example to show you various examples.

There are several files and folders within the Example folder. Let’s pick up any of the files to demonstrate and we are going with file Excel.xlsx

As our agenda to find the filename from the pathname we will set the full pathname (from very root to the file) and our code will be

Sub GetFileNames()

Dim FileName As String

FileName = Dir("C:\Users\User\Desktop\Example\Excel.xlsx")

MsgBox FileName

End Sub

Here within our code, we have set the pathname as C:\Users\User\Desktop\Example\Excel.xlsx

You can see the pathname contains the file name at the end.

Write the code at the Microsoft Visual Basic for Applications window. You will find it in the Visual Basic section in the Developer tab.

Using the Dim keyword we have declared a string variable FileName. And the DIR function generated output stores at this variable.

The DIR function will find and return the file name from the provided path.

Then the MsgBox sets the output through the message box. The MsgBox returns output using a message box, for further information visit this MsgBox article.

Run the code. You will find the file name in a message box.

We have found the filename Excel.xlsx.

II. Check the Existence of a Directory

We can check the existence of a directory using the DIR function. Let’s write the code to check whether the Example folder exists. The macro code will be like the one below

Sub CheckDirectory()

Dim PathName As String

Dim CheckDir As String

PathName = "C:\Users\User\Desktop\Example"

CheckDir = Dir(PathName, vbDirectory)

If CheckDir <> "" Then

            MsgBox CheckDir & " exists"

Else

            MsgBox "The directory doesn't exist"

End If

End Sub

We have declared two variables, PathName contains the full pathname of our checking directory.

Here within the DIR function, we have set two values, pathname and the attribute value as vbDirectory. This attribute value will help to detect the directory. And the output of this function is stored at the CheckDir variable.

Then we have checked whether the variable is empty or not. And we find that the variable is not, then declare the existence of the directory through a message box, otherwise, the return does not exist.

Here our Example directory exists, so we will find the “Folder Example exists”, where Example is the name of the folder. 

III. Create a File one does not Exist

We may need to check the existence before creating the directory. Let’s see with an example.

Let’s imagine we are going to create a directory called Example Folder 1. We will use the MkDir command to create the directory, but before that, we need to check the existence of the directory.

We have written the code for the checking, let’s write the code modifying a little bit.

Sub CreateDirectory()

Dim PathName As String

Dim CheckDir As String

PathName = "C:\Users\User\Desktop\Example\Example Folder 1"

CheckDir = Dir(PathName, vbDirectory)

If CheckDir <> "" Then

            MsgBox CheckDir & " folder exists"

Else

            MkDir PathName

            MsgBox "A folder has been created with the name" & CheckDir

End If

End Sub

Here we have written a command to create the directory using the pathname in the Else block of our code. Run the code

Since the directory already exists it triggered the declaration of existence through the message box.

Let’s change the directory name.

Now we are using Example Folder 3 as the directory name (not present in our directory). Run the code and you will find a message like the one below.

The directory has been created. Let’s look at the directory folder.

The Example Folder 3 folder has been created.

IV. Find the First File from a Directory

The prime task of the DIR function is to find the first file from the provided directory. All you need to provide is the pathname (up to the container directory) inside the function and it will return the very first file from that directory.

Let’s find the first file from our Example directory. Our code will be

Sub GetFirstFileName()

Dim FileName As String

Dim PathName As String

PathName = "C:\Users\User\Desktop\Example\"

FileName = Dir(PathName)

MsgBox "First File: " & FileName

End Sub

You can see it’s a very basic code, we have set the pathname into the DIR function. Now Run the function, you will find the first file from this directory.

V. Find All Files from a Directory

In the previous section, we have seen how to find the first file name from a directory. The situation may arise when you need to find all the files from a specific directory.

We can use the following code

Sub GetAllFiles()

Dim FileName As String

Dim FileList As String

FileName = Dir("C:\Users\User\Desktop\Example\")

Do While FileName <> ""

            FileList = FileList & vbNewLine & FileName

            FileName = Dir()
          

Loop

MsgBox ("File List:" & FileList)

End Sub

Here are two variables to store the filenames as a single (FileName) and as a list (FileList).  The Do While loop iterates up to no file remains in the directory and using this loop we push every file name into the FileList variable.

Execute the code, you will find all the file names.

VI. Find All the Files and Folder from a Directory

We have seen how to get the files from a specific folder, not only the file, we can find the folders as well.

A little modify in our previous code will lead us to the name of the files and folders from the directory. Our code will be

Sub GetAllFilesFolders()

Dim AllNames As String

Dim List As String

AllNames = Dir("C:\Users\User\Desktop\Example\", vbDirectory)

Do While AllNames <> ""

            List = List & vbNewLine & AllNames

            AllNames = Dir()


Loop


MsgBox ("File List:" & List)

End Sub

The change in our code is just the use of attribute parameters. We have used vBDirectory in that field. Run the code, you will find all the files and folders.

VII. Find All the Files of a Specific Type

Using the DIR function we can find any particular type of file. Let’s explore with example.

We are going to find .csv files from our directory. Our code will be

Sub FindTypeFiles()

Dim FileList As String

Dim FileName As String

FileName = Dir("C:\Users\User\Desktop\Example\*.csv")

Do While FileName <> ""


FileList = FileList & vbNewLine & FileName

FileName = Dir()


Loop


MsgBox ("List of .csv Files:" & FileList)


End Sub

Hope you have understood the code, similar mechanism used for finding files. We have used wildcard (*) at the pathname. This asterisk (*) denotes that any character up to any number can occur.

We have used the wildcard in such a way that the file name can be anything but has to be a .csv file.

Execute the code and it will provide you the .csv files we have in our Example directory.

Conclusion

That’s all for today. We have tried showing how you can use the VBA DIR function. You can use the function to find the file name from a directory. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your DIR function-related scenarios where you have stuck, we are ready to help.