VBA Check IF a Workbook Exists in a Folder (Excel File)

To check if a workbook exists in a specific folder, you can use the DIR function. DIR is a function that stands for the directory. You need to specify the path of the file along with the name and extension. If a file exists, it returns the file name, otherwise a blank value.

Write a Code to Check IF an Excel File Exists in a Folder

Use the following steps:

1. First, specify a range or a message box to get the result of the function.
2. Now, enter the function name “DIR” and type starting parentheses.
3. After that, enter the path of the file using double quotation marks and make sure to enter the file extension along with the file name.
4. Next, type the closing parentheses to close the line of code.

Sub vba_Check_workbook()
MsgBox Dir("C:UsersDellDesktopbook1.xlsx")
End Sub

When you run this line of code, it shows you a message box with the file name which means the file exists in the location that you have specified.

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

Check for Multiple Files IF Exist in a Folder

There could be a chance that you need to check for multiple files if they all exist in a folder. In that case, you can use the following code.

Sub vba_check_workbook()

Dim myFolder As String
Dim myFileName As String
Dim myRange As Range
Dim myCell As Range

Set myRange = Range("A1:A5")
myFolder = "C:UsersDellDesktopData"

For Each myCell In myRange
    myFileName = myCell.Value
    If Dir(myFolder & "" & myFileName) = "" Then
        myCell.Offset(0, 1) = "File Doesn't Exists."
    Else
        myCell.Offset(0, 1) = "File Exists"
    End If
Next myCell

End Sub

To understand this code, I have broken it into three parts.

• In the FIRST part, you have all the variables that we need to use in the code. A variable to use as the folder address, file name, the range where you have all the names, and each cell of the range.
• In the SECOND part, you have defined the range address where you have all the names of the files that you wish to check. And also the assigned folder path to the variable.
• In the THIRD part, you have a FOR EACH LOOP to loop through all the files in the folder. This loop uses the DIR to match all the names that you have in the range. It also uses IF STATEMENT to enter a value to the corresponding cell as a remark if the file exists or not.

Note: If you want to use this code, you need to change the folder path, and you need to define the range where you have all the names of the workbooks.

WHAT IS VBA IN EXCEL