Excel VBA: Check If a File Exists or Not

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

VBA Code to Check if a File Exists in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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"

File Name to Check If a File Exists in Excel VBA

⧪ 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.

VBA Dir Function to Check If a File Exists in Excel VBA

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

VBA Code to Check if a File Exists in Excel

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.

Output to Check If a File Exists in Excel VBA

Read More: Excel VBA: Check If a Sheet Exists (2 Simple Methods)


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.

Opening VBA Window to Check If a File Exists in Excel VBA

⧪ 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.

Inserting New Module to Check If a File Exists in Excel VBA

⧪ 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.

Running the Code to Check If a File Exists in Excel VBA

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.

Read More: How to Check If Value Exists in Range in Excel (8 Ways)


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. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Tags:

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo