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.
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:
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.
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
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.
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
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.
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.
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.
Download Practice Workbook
Download the following practice workbook. It will help you understand the subject better.
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. Thanks for your patience in reading this article.
