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.

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.

Syntax:

Arguments Explanation:

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

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.

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

Here within our code, we have set the pathname as E:\Exceldemy\Sales_of_January.xlsx

• Then, run the code with the F5 key.

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

Read More: How to Use VBA Function Procedure with Arguments in Excel

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

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

Read More: Create Custom VBA functions and Use them in the Worksheet

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

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.

Similar Readings

• How to Use VBA While Wend Statement in Excel (4 Examples)
  
• Use VBA UCASE function in Excel (4 Examples)
• How to Use InStr Function in VBA (3 Easy Examples)
  
• Use VBA LCase Function in Excel (3 Easy Examples)
• How to Use VBA IsError Function (6 Examples)

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

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

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

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

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.

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.

Related Articles

• How to Use VBA Forecast Function in Excel (3 Ideal Examples)
  
• Call a Sub in VBA in Excel (4 Examples)
  
• How to Create a Body Mass Index Calculator in Excel Using VBA
• Use VBA Asc Function (5 Practical Examples)
  
• How to Use VBA Large Function in Excel (4 Suitable Examples)
• Use VBA StrComp in Excel (5 Common Examples)
• How to Use IsNull Function in Excel VBA (5 Examples)