One such function is the VBA DIR function. While by itself, it may seem like a simple function that does one specific thing. But when you combine it with some other useful elements of the VBA coding language, you can create powerful stuff (covered in the examples later in this tutorial).
What Does VBA Dir Function Do?
Use VBA DIR function when you want to get the name of the file or a folder, using their path name. To give you an example, if you have an Excel file in a folder, you can use the VBA DIR function to get the name of that Excel file (or any other type of file). What if I want to get the names of all the Excel files in the folder (or all the files – be it Excel file or not)? You can do that too! When you use DIR function once, it returns the first file name in a folder. Now if you want to get the names of the second, third, fourth files as well, you can use the DIR function again (covered later as an example).
Syntax of VBA DIR Function
pathname: This is an optional argument. This can be the file name, folder name, or directory name. If pathname is not found, VBA DIR function returns a zero-length string (“”) attributes: This is an optional argument. You can use this argument to specify some attributes and DIR function will return the file names based on those attributes. For example, if you want a list of all hidden files or read-only files (along with files with no attributes), you need to specify that in this argument.
Attributes available to use in VBA DIR function (you can use one or more of these):
Using Wildcard Characters with DIR Function
If you’re working with Windows, you can also use the wildcard characters in the DIR function. Note that you can not use these when working with VBA in Macintosh. Using wildcards can be useful when:
You want to get the file names of a particular file type (such as .XLSX or .PPTX) When you have a specific suffix/prefix in filenames and you want to get the names of these files/folders/directories. For example, if you want the names of all the files with the prefix 2019 in it, you can do that using wildcard characters.
There are three wildcard characters in Excel: Note: There is one more wildcard character – tilde (~). Since it’s not used a lot, I have skipped its explanation. You can read more about it here if interested.
VBA DIR Function – Examples
Now let’s dive in and see some examples of using the VBA DIR function.
Example 1 – Getting the File Name from its Path
When you have the path of a file, you can use the DIR function to get the name of the file from it. For example, the below code returns the name of the file and shows it in a message box. The above code uses a variable ‘FileName’ to store the file name that is returned by the DIR function. It then uses a message box to display the file name (as shown below).
And what happens when the file doesn’t exist? In that case, the DIR function would return an empty string. The below code uses an If Then Else statement to check whether the file exists or not. If the file doesn’t exist, it shows a message box with a text “File Doesn’t Exist”, else it shows the file name.
Example 2 – Check if a Directory Exists or Not (and create if it doesn’t)
The below code checks whether the folder ‘Test’ exists or not. A message box is used to show a message in case the folder exists or when it doesn’t exist. You can refine this code further to check whether the folder exists or not, and if it doesn’t, then you can use VBA to create that folder. Below is the code that uses the MkDir function to create a folder in case it doesn’t exist.
Example 3 – Get the Names of All File and Folders in a Directory
If you want to get a list of all the file and folder names in a directory, you can use the DIR Function. The below code lists all the files and folder names in the Test folder (which is located at the following path – C:\Users\sumit\Desktop\Test). I am using Debug.Print to show the names in the Immediate window. You can also use this to list the names in a message box or in a column in Excel. The Do While loop in the above code continues till all the files and folders in the given path have been covered. When there are no more files/folders to cover, FileName becomes a null string and the loop stops.
Example 4 – Get the Names of All Files in a Folder
You can use the below code to get the names of all the files in a folder/directory (and not the names of the sub-folders). This code is just like the code used in Example 3, with one minor difference. In this code, I have not specified vbDirectory in the DIR function. When you specify vbDirectory, it will give you the names of all the files as well as folders. When you don’t specify vbDirectory, DIR function will only give you the names of the files. Note: If you want to get the names of all the files in the main folder and the sub-folders, you can’t use the DIR function (as it’s not recursive). To do this, you can either use Power Query (no coding needed) or use the File System Object in VBA (with recursion).
Example 5 – Get the Names of All the Sub-Folders within a Folder
The below code would give you the names of all the sub-folders within the specified folder. It uses the GetAtr function in VBA, which allows us to check whether the name returned by the DIR function is the name of a file or a folder/directory. Again, I am using Debug.Print to get the names in the immediate window. You can get these in a message box or in Excel (by modifying the code accordingly).
Example 6 – Get the First Excel File from a Folder
With DIR function, you can specify the file extension or any suffix/prefix that you want in the file name that is returned. The below code would display the name of the first Excel file in the Test folder. Note that I have used .xls (asterisk sign on both sides). This will ensure that all the versions of Excel files are checked (.xls, xlsx, .xlsm, .xlsb).
Example 7 – Get Names of All Excel File in a Folder
Use the below code to get the names of all the Excel files in the Test folder. While the DIR function returns the name of the first Excel file only, since we are calling it again in the loop, it goes through all the files and gives us the names of all the Excel files. Hope you found this tutorial and the examples useful. Let me know your thoughts in the comments section.
Excel VBA InStr Function. Excel VBA Split Function. Excel VBA TRIM Function VBA LCASE Function. VBA UCASE Function.
Thanks in advance Dear Sumit. Sub CheckFileExistence() Dim FileName As String FileName = Dir(“C:UserssumitDesktopTestExcel File A.xlsx”) If FileName “” Then MsgBox FileName Else MsgBox “File Doesn’t Exist” End If End Sub Can you please give a example of how to get the path of subfolder under the Main Folder? Thank you. Dir(sFullFileName) “” The problem is that some files (not all) stored in the Dropbox folder are not recognized as valid, even though the same file (with the same Full Name) opens the file opening procedure. Full filename example: C:UsersandDropbox (ABC Croatia)blablatest_file av.xlsm *Note: This problem only manifests on one computer (new computer, win 10 pro 64 bit / Office 16 64 bit). Non- Dropbox files are recognized. Any suggestions? Could I make a suggested change to your second example in the “example 2” section: “Sub CreateDirectory() Dim PathName As String Dim CheckDir As String PathName = “C:UserssumitDesktopTest” CheckDir = Dir(PathName, vbDirectory) If CheckDir “” Then MsgBox CheckDir & ” folder exists” Else MkDir PathName ~ CheckDir = Dir(PathName, vbDirectory) ~ MsgBox “A folder has been created with the name” & CheckDir End If End Sub” See the line of code between the ~~, I suggest this as without it the MsgBox does not return the name of the new folder created – works fine otherwise!
