In VBA, there are many interesting functions with its specific functions. In this article, I will share about DIR function and some of its applications in practice.
Dir function
Returns a string representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.
Syntax
Dir [ (pathname, [ attributes ] ) ]
- Pathname:
The address of the file/folder in the computer.
- Attributes:
You can choose, you can leave it blank. However, if left blank, some cases even though the path is correct, DIR does not get the value. For example, in the case of hidden files, alias files, …
The attributes argument settings are:
|
Constant |
Description |
|
vbNormal |
(Default) Specifies files with no attributes. |
|
vbReadOnly |
Specifies read-only files in addition to files with no attributes. |
|
vbHidden |
Specifies hidden files in addition to files with no attributes. |
|
vbSystem |
Specifies system files in addition to files with no attributes. Not available on the Macintosh. |
|
vbVolume |
Specifies volume label; if any other attribute is specified, vbVolume is ignored. Not available on the Macintosh. |
|
vbDirectory |
Specifies directories or folders in addition to files with no attributes. |
|
vbAlias |
Specified file name is an alias. Available only on the Macintosh. |
Some examples of DIR function
1. Get the File Name
First, we create an excel file called Test.xlsx in drive D. Then we use the code below to get the file name.
Sub GetFileName()
Dim FileName As String
FileName = Dir(“D:\Test.xlsx”)
MsgBox “File Name is” & FileName
End Sub
Through the above code, it is easy to see that Dir function easily returns the filename (name and extension).
2. Check if a Directory Exists or Not
Sub CheckDirectory ()
Dim FileName As String
FileName = Dir(“D:\Test.xlsx”)
If FileName = “” Then
MsgBox “File is not exist”
Else
MsgBox “File Name is” & FileName
End If
End Sub
Code idea: You can easily determine whether the file exists or not, by checking whether FileName is equal to whitespace.
3. Get names of all File in One Folder
Sub Listfile()
Dim FileName As String
FileName = Dir(“D:\”, vbHidden)
Do While FileName <> “”
MsgBox FileName
FileName = Dir()
Loop
End Sub
For this case, you use the Do While loop to get name of all File in the directory.