VBA DIR Function
Last Updated :
21 Aug, 2024
Blog Author :
N/A
Edited by :
Ashish Kumar Srivastav
Reviewed by :
Dheeraj Vaidya
Table Of Contents
Excel VBA DIR Function
The VBA DIR function is also known as the directory function. It is a built-in function in VBA that gives us the file name of a given file or a folder, but we need to provide the path for the file. The output returned by this function is a string as it returns the file's name. This function has two arguments: the path name and the attributes.
The DIR function returns the first file name in the specified folder path. So, for example, in your D drive, if you have a folder name called 2019. In that folder, if you have an Excel file named "2019 Sales," you can access this file using the DIR function.
The VBA DIR function is very helpful in getting the file name by using its path folder.
Table of contents
Syntax
This function has two optional arguments.
- : As the name says, what the path to access the file is. It could be the file's name, the folder's name, or the directory. It will return an empty string value if any path is not assigned.
- : This is an optional argument; you may not use this often in coding. You can specify the file's attribute in the , and the DIR function looks for only those files.
For example: If you want to access only hidden files, if you want to access only read-only files, etc., we can specify in this argument. Below are the attributes we can use.
Examples to use VBA DIR Function
Example #1 - Accessing the File Name using DIR Function
We will explain the simple example of accessing the file name using the DIR function. Follow the below steps.
Step 1: Create a macro name.
Step 2: Define the variable as String.
Code:
Sub Dir_Example1() Dim MyFile As String End Sub
Step 3: Now, we will assign a value using this variable's DIR function.
Code:
Sub Dir_Example1() Dim MyFile As String MyFile = Dir( End Sub
Step 4: Copy and paste the file folder path on your computer. Mention the pathname in double-quotes.
Code:
Sub Dir_Example1() Dim MyFile As String MyFile = Dir("E:\VBA Template End Sub
Step 5: We have mentioned my path to the folder. Now we need to mention the file name and its extension as well. To do this, we first need to put a backslash after the path (\).
After entering the backslash, we need to enter the full file name.
Code:
Sub Dir_Example1() Dim MyFile As String MyFile = Dir("E:\VBA Template\VBA Dir Excel Template.xlsm") End Sub
Step 6: Show the value of the variable in the message box.
Code:
Sub Dir_Example1() Dim MyFile As String MyFile = Dir("E:\VBA Template\VBA Dir Excel Template.xlsm") MsgBox MyFile End Sub
Now, run the code and see the message box's result.
So, the DIR function returned the file name with the file extension.
Example #2 - Open File by using DIR Function
Now, how do we open the file? This function can return the file name, but opening that file is a bit different. Follow the below steps to open the file.
Step 1: Create two variables as String.
Code:
Sub Dir_Example2() Dim FolderName As String Dim FileName As String End Sub
Step 2: Now, for the FolderName variable, assign the folder path.
Code:
Sub Dir_Example2() Dim FolderName As String Dim FileName As String FolderName = "E:\VBA Template\" End Sub
Step 3: Now, for the FileName variable, we need to get the file name by using the DIR function.
Code:
Sub Dir_Example2() Dim FolderName As String Dim FileName As String FolderName = "E:\VBA Template\" FileName = Dir( End Sub
Step 4: Now, for Path Name, we have already assigned a path to the variable FolderPath, so we can directly supply the variable here.
Code:
Sub Dir_Example2() Dim FolderName As String Dim FileName As String FolderName = "E:\VBA Template\" FileName = Dir(FolderName End Sub
Step 5: Now, we need to supply the file name. By using the ampersand symbol (&), assign the file name.
Code:
Sub Dir_Example2() Dim FolderName As String Dim FileName As String FolderName = "E:\VBA Template\" FileName = Dir(FolderName & "VBA Dir Excel Template.xlsm") End Sub
Step 6: Now, use the WORKBOOKS.OPEN method.
Code:
Sub Dir_Example2() Dim FolderName As String Dim FileName As String FolderName = "E:\VBA Template\" FileName = Dir(FolderName & "VBA Dir Excel Template.xlsm") Workbooks.Open End Sub
Step 7: File Name is a combination of FolderPath & FileName. So combine these two.
Code:
Sub Dir_Example2() Dim FolderName As String Dim FileName As String FolderName = "E:\VBA Template\" FileName = Dir(FolderName & "VBA Dir Excel Template.xlsm") Workbooks.Open FolderName & FileName End Sub
Now, run this code. It will open the mentioned file name.
Example #3 - Multiple Open Workbooks using DIR Function
We can access all the workbooks in the folder. We cannot directly mention all the file names to access every file, but we can use the wildcard character to refer to the file.
The asterisk (*) is one of those wildcard characters. It identifies any number of characters. So, for example, if you want to access all the macro files in the folder, you can use the asterisk as the wildcard, "*.xlsm*."
Here * will match any file name with the file's extension equal to "xlsm."
Code:
Sub Dir_Example3() Dim FolderName As String Dim FileName As String FolderName = "E:\VBA Template\" FileName = Dir(FolderName & "*.xlsm*") Do While FileName <> "" Workbooks.Open FolderName & FileName FileName = Dir() Loop End Sub
Now, the above code will open all the files in the folder path.
FileName = Dir() we have used this line because we have to make the existing file name nil to access the next file in the folder. The moment we make the existing file name nil when the loop runs for the second time, it will take the next file in the folder.
Example #4 - Get all the File Names in the Folder
Suppose you want the list of all the file names in the folder. We can also do this by using attributes.
Code:
Sub Dir_Example4() Dim FileName As String FileName = Dir("E:\VBA Template\", vbDirectory) Do While FileName <> "" Debug.Print FileName FileName = Dir() Loop End Sub
Make the immediate window visible by pressing Ctrl + G.
Now, run the code. We will get all the file names in the immediate window.
Recommended Articles
This article has been a guide to VBA DIR. Here, we learn how to use the VBA DIR function in excel along with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: -