It very essential in many excel VBA project to
check existence of File, Folder and Worksheet for particular excel file. It consumes
a lot time to write the functions or macros again and again. I have included
all those if exists macros on this article. Thus, you can make use of
them in your project to help yourselves to save ample amount of time.
All these, if exists VBA codes are listed as below.
To Verify, You can copy and paste mentioned code into standard module in your project
or new workbook.
Check if Folder exists:Check if File exists:Check if Sheet exists:Check if file is open:
Here you go to check Folder Existence:
Sub If_Folder_Exists()
Dim fso As Object
Dim fPath As String
Set fso = CreateObject("scripting.filesystemobject")
fPath = "C:\Users\Dreams\Desktop\Test\ABC"
If Right(fPath, 1) <> "\" Then
fPath = fPath & "\"
End If
If fso.FolderExists(fPath) = False Then
MsgBox "Folder does not exist!"
Else
MsgBox "Folder is exist!"
End If
End Sub
Use below VBA code to check File:
Sub If_File_Exist()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com
'
Dim fso As Object
Dim fPath As String
Set fso = CreateObject("scripting.filesystemobject")
fPath = "C:\Users\Dreams\Desktop\Test\ABC\book1.xlsm"
If fso.FileExists(fPath) = False Then
MsgBox "file does not exist!"
Else
MsgBox "File is exist!"
End If
End Sub
Use below VBA code to validate if worksheet is exists:
Sub If_Sheet_Exists()
Dim sht As Worksheet
On Error Resume Next
Set sht = ActiveWorkbook.Sheets("Test_Sheet")
If Err.Number <> 0 Then
MsgBox "Sheet which you looking for does not exist!"
Err.Clear
On Error GoTo 0
Else
MsgBox "Sheet which you looking for is exist!"
End If
End Sub
Use below VBA code to validate if file is open:
Sub If_File_Open()
Dim tWbk As Workbook
Set tWbk = Nothing
On Error Resume Next
Set tWbk = Workbooks("Book1.xlsm")
On Error GoTo 0
If tWbk Is Nothing Then
MsgBox "The File is not open!"
Else
MsgBox "The File is open!"
End If
End Sub