VBA Check File Exists

Check File Exists Using Excel VBA

VBA Check File Exists helps to Check if file Exists in Location using Excel VBA. After mentioning the file path in the computer, what if someone deletes the file or change the folder path of the file? Obviously, our code will throw an error in such cases. To resolve this issue, we can do one thing before we actually open the file, we can check whether the mentioned file exists or not.

In this article, we will show you how to check whether the particular mentioned file exists or not.

How to Check if File Exists or Not?

• How excel VBA knows whether the file exists or not??
• By default, it cannot!!!
• So, then how??
• We need to use the function called “Dir” to check whether the file exists or not.

What does DIR Function Do?

VBA DIR function returns the name of the file name with its extension in the specified folder path. When the folder doesn’t have any file, it returns the empty string.

So by using this function, we can actually test whether the file exists or not. Even without the DIR function, we can test whether the file exists or not. We will see some of the examples below.

How to Use VBA Check File Exists in Excel?

We will learn how to use a VBA Check File Exists Function with few examples in excel.

Example #1 – VBA Check File Exists

Ok, let’s write some code to test the file exists or not. Follow the below steps to write code on your own.

Step 1: For this, go to the VBA window and under the Insert menu select Module as shown below.

Step 2: Start the subprocedure.

Code:

Sub File_Example()

End Sub

Step 3: Define the variable as String.

Code:

Sub File_Example()

Dim FilePath As String

End Sub

Step 4: Now, I want to test the file named as “Chapter-11. InputBoxes.xlsm” in my E-Drive”. I will assign my file path to this variable.

Code:

Sub File_Example()

Dim FilePath As String

FilePath = "D:\Test File.xlsx"

End Sub

Step 5: Now define one more variable to apply the DIR function.

Code:

Sub File_Example()

Dim FilePath As String
Dim FileExists As String

FilePath = "D:\Test File.xlsx"

End Sub

Step 6: Now, for the second variable, open the DIR function.

Code:

Sub File_Example()

Dim FilePath As String
Dim FileExists As String

FilePath = "D:\Test File.xlsx"
FileExists = Dir(

End Sub

Step 7: DIR function requires the file path. Since we have already assigned the file path to the variable “FilePath”, we can simply pass this variable to the DIR function.

Code:

Sub File_Example()

Dim FilePath As String
Dim FileExists As String

FilePath = "D:\Test File.xlsx"
FileExists = Dir(FilePath)

End Sub

Step 8: Now DIR function returns only the File Name as “Chapter-11. InputBoxes” from the mentioned file path. So let’s show the result in a message box.

Code:

Sub File_Example()

Dim FilePath As String
Dim FileExists As String

FilePath = "D:\Test File.xlsx"
FileExists = Dir(FilePath)

MsgBox FileExits

End Sub

Step 9: Now run the macro to see the result.

Since there is a file that exists in the mentioned path, our DIR function filtered the file name from the huge path.

Step 10: Now, I will change the file name to a different thing which is not there in the mentioned path.

Code:

Sub File_Example()

Dim FilePath As String
Dim FileExists As String

FilePath = "D:\ File.xlsx"
FileExists = Dir(FilePath)

MsgBox (FileExists)

End Sub

Step 11: If I run the code now, it will return an empty string in the message box.

DIR function is best suited to use with the IF statement in VBA. Above, we could see only the file name with its extension if it exists, or else we could only see the empty string.

Example #2 – DIR with IF Condition

Step 1: But using the IF statement with the DIR function, we can alter our results. For an example, look at the below code.

Code:

Sub File_Example1()

Dim FilePath As String
Dim FileExists As String
FilePath = "C:\Users\cba_28\Desktop\Alex\Final Input.xlsm"

FileExists = Dir(FilePath)

     If FileExists = "" Then
MsgBox "File doesnt exists in the mentioned path"
     Else
MsgBox "File exists in the mentioned path"
End If

End Sub

Step 2: Here IF condition checks whether the variable “FileExists” value is nothing (“”) or not. If the variable value is nothing (“”), then it will return the result as “File doesn’t exist in the mentioned path”, or else it will return the result as “File exists in the mentioned path.”

Below is an example of a screenshot of the same.

By using the DIR function, we can check whether the file exists or not.

Recommended Articles

This is a guide to VBA Check File Exists. Here we discuss how to use Excel VBA Check File Exists Function along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

1. VBA Copy Paste
2. VBA Web Scraping
3. VBA Subscript out of Range
4. VBA RGB