VBA allows you to check if a file or folder exists by using the Dir function.
Using the Dir Command to Check If a File Exists
As we mentioned in the introduction, the Dir function allows us to check if a selected file exists on the computer. Here is the code:
Sub CheckFileExists ()
Dim strFileName As String
Dim strFileExists As String
strFileName = "C:\Users\Nikola\Desktop\VBA articles\Test File Exists.xlsx"
strFileExists = Dir(strFileName)
If strFileExists = "" Then
MsgBox "The selected file doesn't exist"
MsgBox "The selected file exists"
We first assigned the file path to the variable strFileName. Then we use the Dir function to get the file name into the variable strFileExists. If the file exists in the directory, its name will be assigned to the string variable strFileExists. If it does not exist then strFileExists will remain blank. Finally, the message box appears informing us if the file exists or not.
Using the Dir Command to Check If a Folder Exists
Similarly to checking if a file exists, you can check if a folder exists. You just need to add one argument to the Dir command. Let’s look at the code:
Sub CheckFolderExists ()
Dim strFolderName As String
Dim strFolderExists As String
strFolderName = "C:\Users\Nikola\Desktop\VBA articles\Test Folder\"
strFolderExists = Dir(strFolderName, vbDirectory)
If strFolderExists = "" Then
MsgBox "The selected folder doesn't exist"
MsgBox "The selected folder exists"
We first assigned the folder path to the variable strFolderName. Then we use the Dir function to get the file name into the variable strFileExists. In order to check a folder, we need to add the second argument to the function – vbDirecotry. If the folder exists in the directory, its name will be assigned to the variable strFolderExists. If not strFolderExists will remain blank.
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!