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 –
