Excel VBA: Check If a File Exists or Not

Get FREE Advanced Excel Exercises with Solutions!

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


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


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.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo