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:
- 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:\Users\Dell\Desktop\book1.xlsx") End Sub
When you run this line of code, it shows you a message box with the file name that means the file exists on the location that you have specified.
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:\Users\Dell\Desktop\Data" 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 loops 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 in the, and you need to define the range where you have all the names of the workbooks.
More on VBA Workbooks
VBA Save Workbook | VBA Close Workbook | VBA Delete Workbook | VBA ThisWorkbook | VBA Rename Workbook | VBA Activate Workbook | VBA Combine Workbook | VBA Protect Workbook (Unprotect) | VBA Check IF a Workbook is Open | VBA Open Workbook | VBA Create New Workbook (Excel File)
If you are preparing for an interview, make sure to check out these VBA interview questions.