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.
4.7 (3,118 ratings)
View Course
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 –
