VBA DIR Function

What are VBA DIR Function?

DIR stands for Directory in VBA. VBA DIM Function is a built-in VBA function that allows us to get a file from a given folder or path name. For instance, if we have an Excel file called “Sales Report.xlsx” in one of the folders, we can access the VBA DIR Function.

In case of multiple files in the same folder, we use the VBA DIR function with loops to instantly get all the file names. Not only all files, but we can also get the 3rd file, 4th file, and so on. For example, look at the following image.

Assuming we need to get the file named “data.xlsx” from this location path, then we can use the following code.

Sub DIR_Intro()
Dim FN As String
FN = Dir(“C:\Users\jeevanay\Downloads\New folder\data.xlsx”)
MsgBox FN
End Sub

When we execute this VBA code, we get the following value in a message box.

We get the file name called “data.xlsx.”

Syntax of VBA DIR Function

The following is the syntax of the VBA DIR function.

DIR([PathName], [Attributes as VbFileAttribute = vbNormal]) As String

Interestingly all the arguments of the VBA DIR function are optional; we will discuss this in the examples section.

[Path Name]: This is an optional argument. Here, we need to provide the path name from which we need to access the file. We must provide the complete folder address here. If nothing is found in the given path, it will return an empty string (“”).

[Attributes]: Here, we need to provide some attributes, and based on that, the VBA DITR Function will retrieve the file name based on the attributes given. For example, if we want to retrieve the read-only or hidden files and the no-attribute files, then we need to specify that attribute in this argument.

Following is the list of attributes available.

Constant	Value	Description
vbNormal	0	This is the Default attributes i.e., no attributes.
vbReadOnly	1	Specifies read-only files along with the files with no attributes.
vbHidden	2	Specifies hidden files along with the files with no attributes.
VbSystem	4	Specifies system files along with the files with no attributes. Not available on the Macintosh.
vbVolume	8	Specifies volume label; if any other attributed is specified, vbVolume is ignored. Not available on the Macintosh.
vbDirectory	16	Specifies directories or folders in addition to files with no attributes.
vbAlias	64	Specified file name is an alias. Available only on the Macintosh.

The output of this VBA DIR function is a string.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to use DIR Function in VBA?

Let us show you a step-by-step approach to apply the VBA DIR function and get the file name from the given folder path name. For example, look at the following image of a file in a folder.

We have some files in a folder. Let’s write some code to get one of these files.

Step 1: Start the subprocedure by naming the VBA macro.

Step 2: Define a variable to store the filename returned by the VBA DIR function.

Since the output of the VBA DIR function is a string value, we have assigned a String data type to the variable.

Step 3: Now, we assign the VBA DIR function to the variable.

Step 4: For the path name argument we must give the path address from where we must get the file name. To get the path address, go to the file folder and click on the location path at the top.

Copy the path and paste it inside the VBA DIR function with double quotes.

Step 5: Now have a folder path of the file’s location. However, to get the file name, we must enter the file name along with the file extension.

Enter a backslash after the copied folder path, then enter the file name with the extension. Now the VBA DIR function will extract the file name from the given path name.

Step 6: Show the variable result in a message box.

Sub Dir_Basic()
Dim FileName As String
FileName = Dir(“C:\Users\User\OneDrive\Desktop\Sales Reports\Sales Report Feb.xlsx”)
MsgBox FileName
End Sub

Let’s execute the code, and we will get the following result in a message box.

The VBA DIR Function has returned the file name from the given folder, i.e., Sales Report Feb.xlsx

Examples to use VBA DIR Function

Let us show you some real time examples of applying the VBA DIR Function.

Example #1: Accessing the File Name Using DIR Function

Let us show you some real-time examples of applying the VBA DIR Function.

Assume we need to get a “Cost Report” file from this folder. We can use the following VBA code, which uses the VBA DIR function to get a specific file name.

We have segregated the code into 4 different parts. Let us explain the code part by part to understand it better.

Part #1: In this part we have defined a variable to assign the file name to the variable. Since we already know the file name “Cost Name.xlsx,” we have assigned the same to the variable. The most important thing is that we need to provide the file extension. Here, the file extension is “xlsx” and we have provided the same after the file name separated by a dot (.).

Part #2: Next, we must decide where this file exists. For this, we have defined a variable and copied the folder path where that file resides from our computer.

One notable thing is that once we have copied the folder path, we enter an extra backslash (\) (in yellow) in the above code. After the final folder name, we must enter the file name, which this back slash will separate.

Part #3: In this part, we have applied the VBA DIR function to the newly defined variable to get the file name. The VBA Dir relative path is provided through the variable assigned to the variable in Part #2.

For the VBA DIR function, we have concatenated the Part#2 and Part#1 variables.

Part #4: Finally, we are just showcasing the VBA DIR function result in a message box.

Let’s execute this code, and we will get the following result in a message box in VBA.

We see the file name in the message box.

Example #2: Open File by Using DIR Function

In the previous example, we have seen how to get the file name from the given folder. However, the actual task will be to open the file and start working with that file.

We must go one step further from the previous example to open the file returned by the VBA DIR function.

For example, assume we must open the file named “Cost Report.xlsx.” using the “Workbooks.Open” method. The following is the syntax of the Open method.

Here, we must provide the file name, i.e., complete folder path along with the file name in that path.

In the previous example, the folder path and file name were extracted to variables using the VBA DIR function. We will use the same code with the addition of the Workbooks.Open method to open the file returned by the VBA DIR function. The following code will open the file.

Sub Dir_Ex2()
‘Define a variable to assign the file name
Dim MyFile_Name As String
‘Set file name
MyFile_Name = “Cost Report.xlsx”
‘Define a variable to assign the folder path
Dim Folder_Path As String
‘Assign the folder path
Folder_Path = “C:\Users\User\OneDrive\Desktop\Sales Reports\Costing\”
‘Define a variable to apply VBA DIR function
Dim File_Name As String
‘Assign the DIR function to get the file name
File_Name = Dir(Folder_Path & MyFile_Name)
‘Open the file using the Workbook.Open method
Workbooks.Open Folder_Path & File_Name
End Sub

All the code remains the same except the last line of the code. We have added the Workbooks.Open method. For this method, we have provided the folder path through the variable “Folder_Path” and then concatenated the file name through the variable “File_Name.”

When we execute this code, it will open the workbook “Cost Report.xlsx.”

Example #3: Multiple Open Workbooks using DIR Function

When we have multiple workbooks in a folder, opening multiple workbooks is possible through VBA DIR Function. We need to use the DO WHILE loop in VBA to go through each file and open them.

For example, look at the following image of files in a folder.

We have four files in this folder or location. Let’s use the DO WHILE loop and open every workbook.

Part #1: We have defined a variable and have assigned it the folder path where we must loop through all the files.

Part #2: Here, we have defined another variable, and for this variable, we have used the VBA DIR function, and we have given the folder path variable followed by the VBA DIR function wildcards.

We have concatenated the Excel wildcard asterisk (*) followed by the file extension “xlsx.” Here, the wildcard asterisk (*) will match all the file names and the extension “xlsx.”

Part #3: Next, we used the DO WHILE loop to open each workbook one after the other until the variable MyFile_Name becomes empty, i.e., once all the files looped through this variable value becomes empty.

Another thing we must observe here is that we have used DIR with no parameters, i.e.,

MyFile_Name = Dir()

It is used to remove the variable value once the file is opened. It is because once the existing file is opened, it will go to the following instead of the same file in the folder.

Execute this code, and you can see all the files opened in your system.

Note: This code will not open the file named “W Allowance Calculation.xlsm” because the file extension of this file is not “xlsx”; instead, it is “xlsm.” In the VBA DIR function wildcards, we have given the file extension as “xlsx”; hence it will ignore all the other file extensions.

Example #4: Get the File Name in the Folder

We can dynamically fetch all the file names from the given folder. For example, look at the following folder image.

Let’s get the file names stored in cells using the following code.

Sub Dir_Ex4()
‘Define a variable to assign the folder path
Dim FileName As String
‘Assign the folder path to the DIR function
FileName = Dir(“C:\Users\v-jeevanay\Desktop\Sales Reports\”)
‘Define a variable to increment row numbers to store file names in cell
Dim k As Long
k = 1
‘Apply a loop to go through all the files
Do While FileName <> “”
‘Store the file name a cell
Cells(k, 1).Value = FileName
‘Set the file name to nil before move to the next file
FileName = Dir()
‘Once the first file name fetched and stored, ….
‘we need to store the next file name in next cell hence increment the k value by 1
k = k + 1
Loop
End Sub

Once we execute this code, we will get all file names stored like the following in a worksheet.

There were three files and one folder in the given folder, and the VBA DIR function loop through files and stored their names in cells.

However, to get the folder name, also we need to provide the “vbDirectory” in the VBA DIR function. Once we specify the vbDirectory in the second argument of the VBA DIR function, it will fetch both file and folder names.

Important Things to Note

• When we provide the folder path, we must concatenate the backslash (\) before giving the file name.
• When we provide the file name, we also need to give a file extension.
• Wildcard character (*) will match file names followed by the file extension.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA DIR Function. Here we learn how to apply DIR function to fetch filename from folder with examples. You can learn more from the following articles –

• VBA Offset
• VBA UserForm
• VBA Activate Sheet