Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use VBA DIR Function in Excel (7 Practical Examples)

The DIR function in VBA mainly shows you the directory or the files from a given folder. It can also return the first file with this function. This function has lots of uses to get specific files and folders. All you need to just insert the file path in the pathname of the VBA code. You may find it tougher to use the VBA DIR codes. Don’t worry, in this article, we are going to show you some examples for a better visualization to use the VBA DIR function. Hope you will be able to use the function after reading the article. So, let’s get started.

Overview - VBA DIR Function


Download Practice Workbook

Download the following practice workbook. It will help you understand the subject better.


Introduction to the DIR Function

Summary:

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

VBA DIR function syntax in Excel

Syntax:

Dir [ (pathname, [ attributes ] ) ]

Arguments Explanation:

Argument Required/Optional Explanation
PathName Optional Path to access and specifies the file
Attributes Optional Constant or numeric expression specifies the attributes of matching files

There are a few pre-defined attributes, they are-

Attribute Name Description
vbNormal Files with no specific attributes
vbReadOnly Read-only files with no attributes
vbHidden Hidden files with no attributes
vbSystem System files with no attributes
vbVolume Volume label
vbDirectory Directories or folders with no attributes
vbAlias Specified filename is an alias

7 Examples of Using the VBA DIR Function in Excel

From the description, you might have understood that the VBA DIR function provides the file name from the provided pathname. Let’s understand it by example. Here we have created a directory Exceldemy_Folder to show you various examples. There are different small folders and files in this folder.

Dataset


1. Find the Filename from Path

In our folder, we can find a specific file by declaring the file name path.

After copying the file’s path, you need to run the code.

For this reason, go to the Developer tab >> pick Visual Basic. Then go to Insert tab >> select Module. In the General dialog box, we write the code.

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

Sub FileNames()
Dim FN As String
FN = Dir("E:\Exceldemy\Sales_of_January.xlsx")
MsgBox FN
End Sub

 

Code to Find the Filename from Path in Excel DIR FunctionHere within our code, we have set the pathname as E:\Exceldemy\Sales_of_January.xlsx

Code Breakdown:

  • Initially, we declared a string variable called FN. And the output of the Dir function was saved in this variable.
  • Next, the Dir function finds the file name and returns it from the provided path.
  • Then the MsgBox sets the output through the message box. The MsgBox returns output using a message box.
  • Then, run the code with the F5 key.

Find the Filename from Path

Finally, we have found the file named Sales_of_January.xlsx.


2. 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 Exceldemy folder exists. Write down the following code in the general box and run it with the F5 key.

Sub CheckFile()
Dim PN As String
Dim File As String
PN = "E:\Exceldemy"
File = Dir(PN, vbDirectory)
If File <> "" Then
    MsgBox File & " exists"
Else
    MsgBox "The file doesn't exist"
End If
End Sub

Check the Existence of a Directory in excel DIR function

Code Breakdown:

  • We have declared two variables; PN 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 in the File variable.
  • Then we checked whether the variable is empty or not. If 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, the Exceldemy directory exists, so we will find “Exceldemy exists”, where Exceldemy is the name of the folder.


3. Create a Folder That Does Not Exist

You can create a folder that does not exist on your PC. For this, you have to create a pathname that does not exist in your folders. Let’s imagine we are going to create a directory called Exceldemy_1. We will use the MkDir command to create the directory, but before that, we need to write the following VBA code.

Sub CheckFile()
Dim PN As String
Dim File As String 
PN = "E:\Exceldemy_1"
File = Dir(PN, vbDirectory) 
If File <> "" Then
    MsgBox File & " File folder exists"
Else
    MkDir PN
    MsgBox "A file folder has been created with the name" & File
End If 
End Sub

Create a Folder That Does Not Exist in Excel DIR Function

Here we have written a command to create the directory using the pathname from the Else block of our code. Run the code with the F5 key.

The directory has been created. Let’s look at the directory folder. The Exceldemy_1 folder is now visible on your computer.

Create a Folder That Does Not Exist


Similar Readings:


4. Find the First File from a Directory

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

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

Sub FirstFileinFolder()
Dim FN As String
Dim PN As String
PN = "E:\Exceldemy\"
FN = Dir(PN)
MsgBox "First File: " & FN
End Sub

Code to Find the First File from a Directory in Excel Dir Function

You can see the basic code; we have passed the pathname into the Dir function. Now Run the code with the F5 key, you will find the first file in this directory.


5. 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. To find all the files in a specific directory, you have to write the following VBA code.

Sub AllFile()
Dim FN As String
Dim FL As String
FN = Dir("E:\Exceldemy\")
Do While FN <> ""
    FL = FL & vbNewLine & FN
    FN = Dir()    
Loop
MsgBox ("File List:" & FL)
End Sub

Code to Find All Files from a Directory in Excel DIR function

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

Execute the code, and you will find all the files in the directory, like in the image below.


6. Find All the Files and Folders from a Directory

We have mentioned in the previous section where to find all the files. We can also find all the sub-folders in a folder. To do this, write the following VBA code that we have attached below.

Sub AllFileFolders()
Dim AN As String
Dim Lst As String
AN = Dir("E:\Exceldemy_Folder\", vbDirectory) 
Do While AN <> ""
    Lst = Lst & vbNewLine & AN
    AN = Dir()   
Loop 
MsgBox ("File Lst:" & Lst)
End Sub

Code to Find All the Files and Folders from a Directory in Excel DIR function

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


7. Find All the Files of a Specific Type

Using the VBA Dir function, we can find any particular type of file. Let’s explore with an example.

We are going to find .csv files from our directory. Our code will be like the following-

Sub SpecialTypeFiles()
Dim FL As String
Dim FN As String
FN = Dir("E:\Exceldemy_Folder\Exceldemy\*.csv")
Do While FN <> ""
FL = FL & vbNewLine & FN
FN = Dir()
Loop
MsgBox ("List of .csv Files:" & FL)
End Sub

Code to Find All the Files of a Specific Type in Excel DIR function

We hope you have understood the code, which uses a similar mechanism for finding files. In the pathname, we used a wildcard (*). This asterisk (*) denotes that any character up to any number can occur. The wildcard has been used in such a way that the file name can be anything but must be a .csv file.

When you run the code, it will return the .csv files from our Exceldemy directory.


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Conclusion

That’s all about today’s session. And these are some easy examples of the VBA Dir function in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website, Exceldemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo