One of the most advantageous uses of Excel VBA in our day-to-day life is that we can check if a file exists on our machine or not using it. In this article, I’ll show you how you can check whether a file exists or not on our computers using Excel VBA.
VBA Code to Check If a File Exists or Not (Quick View)
Sub Check_If_a_File_Exists()
File_Name = "E:\ExcelDemy\Book1.xlsm"
File_Name = Dir(File_Name)
If File_Name = "" Then
MsgBox "The file doesn't exist."
Else
MsgBox "The file exists."
End If
End Sub
An Overview of the VBA Code to Check If a File Exists or Not (Step-by-Step Analysis)
So, without further delay, let’s go to our main discussion today. We’ll break down the VBA code step-by-step to learn how to check whether a file exists or not on our computers.
⧪ Step 1: Inserting the Necessary Input
At the very outset of the code, we have to insert the necessary input into it. There is only one input required in this code, and that’s the name of the file that we are looking for. Insert the full file name (Along with the associated directory).
File_Name = "C:\ExcelDemy\Book1.xlsm"
⧪ Step 2: Extracting the File Name with the VBA Dir Function
Next, we’ll use a small trick. We’ll use the VBA Dir function to extract the name of the file. If no such file exists, the Dir function will return a null string.
File_Name = Dir(File_Name)
⧪ Step 3: Checking whether the File Exists by Using an If-Block
This is the most important step. We’ve told earlier that if no file in the given name exists, the Dir function returns a null string. We’ll use this property within an If-block to check whether the file exists or not.
If File_Name = "" Then
MsgBox "The file doesn't exist."
Else
MsgBox "The file exists."
End If
So the complete VBA code will be:
⧭ VBA Code:
Sub Check_If_a_File_Exists()
File_Name = "E:\ExcelDemy\Book1.xlsm"
File_Name = Dir(File_Name)
If File_Name = "" Then
MsgBox "The file doesn't exist."
Else
MsgBox "The file exists."
End If
End Sub
⧭ Output:
Run the code. On my computer, a message box will display, “The file exists”, because it actually exists on my machine.
On your machine, the output may be different depending on whether the file exists or not.
Read More: Excel VBA: Check If a Sheet Exists
Developing a Macro to Check a Range of Files Exists or Not Using Excel VBA
Here we’ve got a data set in an Excel worksheet that contains the name of some files along with the directories in range B4:B8.
Our objective is to develop a Macro that’ll write Exist / Doesn’t Exist beside each of them after checking whether they exist or not on the computer.
⧪ Step 1: Opening the VBA Window
Press ALT + F11 on your keyboard to open the Visual Basic window.
⧪ Step 2: Inserting a New Module
Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.
⧪ Step 3: Putting the VBA Code
This is the most important step. Insert the following VBA code in the module.
⧭ VBA Code:
Sub Check_If_a_Range_of_File_Exist()
Set Rng = ActiveSheet.Range("B4:B8")
For i = 1 To Rng.Rows.Count
File_Name = Dir(Rng.Cells(i, 1))
If File_Name = "" Then
Rng.Cells(i, 2) = "Doesn't Exist"
Else
Rng.Cells(i, 2) = "Exists"
End If
Next i
End Sub
⧪ Step 5: Running the Code
Click on the Run Sub / UserForm tool from the toolbar above.
The code will run. And you’ll get “Exists” for the files that exist, and “Doesn’t Exist” for the files that don’t exist.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Therefore, this is the process to develop a Macro to send an Email from Excel with an attachment. Do you have any questions? Feel free to ask us.
Related Articles
- If Statement in Excel VBA
- Excel VBA: If Cell Contains Value Then Return a Specified Output
- Excel VBA: If Statement Based on Cell Value
- VBA IF Statement with Multiple Conditions in Excel
- Excel VBA: Combining If with And for Multiple Conditions
- Excel VBA: Combined If and Or
- Excel VBA Nested If Then Else in a For Next Loop
- Excel VBA to Check If String Contains Letters
- Else Without If Error VBA in Excel