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

Use the Dir function with the full file path. It returns the file name when the file exists and an empty string when it does not:

If Dir("C:\Reports\Sales.xlsx") <> "" Then
    MsgBox "File exists"
Else
    MsgBox "File not found"
End If

To check for a folder rather than a file, add vbDirectory: Dir("C:\Reports\", vbDirectory) <> "". Wildcards also work, so Dir("C:\Reports\Sales*.xlsx") tells you whether any matching file is there.

CreateObject("Scripting.FileSystemObject").FileExists("C:\Reports\Sales.xlsx") returns True or False directly. Use it inside a loop that already uses Dir to list files, because a second Dir call with a path resets that loop.

To check whether a workbook is open rather than saved in a folder, test the Workbooks collection instead:

Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("Sales.xlsx")
On Error GoTo 0
If wb Is Nothing Then MsgBox "Sales.xlsx is not open"

When you join a folder and a file name, put a backslash between them: myFolder & "\" & myFile. Without it, every file reports as missing. Skip blank names too, because Dir("C:\Reports\") returns the first file in that folder, so an empty cell reports as found.

Table of Content hide

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

Check for Multiple Files IF Exist in a Folder

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

Use the following steps:

code to check if an excel file exist in a folder

First, specify a range or a message box to get the result of the function.
Now, enter the function name “DIR” and type starting parentheses.
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.
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.

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

Check for Multiple Files IF Exist in a Folder

Related Tutorials