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.
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
The VBA DIR function returns the name of a file or directory from a given folder path. Conventionally It returns the first file.
Dir ([Pathname], [ Attributes ] ) ]
|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
|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|
|vbDirectory||Directories or folders with no attributes|
|vbAlias||Specified filename is an alias|
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.
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.