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 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 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 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 IF statement in VBA. Above, we could see only the file name with its extension if exists or else we could only see the empty string.

Example #2 – DIR with IF Condition

Step 1: But using IF statement with 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 the example of a screenshot of the same.

By using 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 downloadable excel template. You can also go through our other suggested articles –

1. Copy Paste Function in VBA
2. Substring Excel Function
3. VBA Subscript out of Range
4. Excel ISNUMBER Formula