VBA DIR Function

Excel VBA DIR Function

VBA DIR function is also known as the directory function, this is an inbuilt function in VBA which is used to give 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 string as it returns the name of the file, there are two arguments to this function which are the path name and the attributes.

DIR function returns the very first file name in the specified folder path. For example, in your D Drive, if you have a folder name called 2019 and in that folder, if you excel file named “2019 Sales,” then you can access this file by using the DIR function.

“VBA DIR” function is very helpful in getting the name of the file by using its path folder.

VBA DIR Function

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA DIR Function (wallstreetmojo.com)

Syntax

This function has two optional arguments.

VBA Dir Formula
  • [Path Name]: As the name says, what the path to access the file is. This could be the name of the file, name of the folder, or directory as well. If any path is not assigned, I will return an empty string value, i.e. “
  • [Attributes]: This is also an optional argument, and you may not use this very often in coding. You can specify the attribute of the file in the [Path Name], 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.

VBA Dir Example 1

Examples to use VBA DIR Function

You can download this VBA Dir Excel Template here – VBA Dir Excel Template

Example #1 – Accessing the File Name using DIR Function

I will explain to you 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, for this variable, we will assign value by using the DIR function.

Code:

Sub Dir_Example1()

  Dim MyFile As String

  MyFile = Dir(

End Sub

Step 4: Now 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: I have mentioned my path to the folder, now we need to mention the file name & its extension as well. To do this first thing we need to do is we 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 what the result of the message box is.

VBA Dir Example 1-1

So 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 process. 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.

Open-File Example 2

Example #3 – Multiple Open Workbooks using DIR Function

Actually, we can access all the workbooks in the folder. In order to access each and every file, we cannot mention all the file names directly, 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. For example, if you want to access all the macro files in the folder, you can use the asterisk as the wildcard i.e., “*.xlsm*.”

Here * will match any file name with the extension of the file is 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() the reason why I have used this line because, in order to access the next file in the folder, we have to make the existing file name to nil. The moment we make the existing file name to 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 if 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.

Get all the File Names 1

Now run the code. We will get all the file names in the immediate window.

Get all the File Names 2

Recommended Articles

This 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 –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>