Test if Folder, File or Sheet exists or File is open

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

---

On this page, you will find code to check if a folder, file or Sheet exists and code to test if a workbook is open. In every example, I use a MsgBox to tell you if the folder, file or sheet exists or not. Replace the MsgBox for the code that you want to run when the folder, file or sheet exists or not. If you want to test the code you must copy the code and functions in a standard module of your workbook's project.

Copy the code in a Standard module of your workbook, if you just started with VBA see this page.
Where do I paste the code that I find on the internet

 

Test if Folder exist

Below are three examples to test if the folder "test" exist
FolderPath = "C:\Users\Ron\test"

The first one uses VBA Dir and the other two use FSO (FileSystemObject). Note:read the information in the second FSO example good because you must set a reference to the "Microsoft Scripting Runtime" in the VBA editor if you want to use this example. When you use the first FSO example you not have to set a reference in the VBA editor to "Microsoft Scripting Runtime" because it use Late Binding.

When you use the Intellisense help you see that there are a lot more nice options when you use FSO.

 

Sub Test_Folder_Exist_With_Dir()
    Dim FolderPath As String

    FolderPath = "C:\Users\Ron\test"
    If Right(FolderPath, 1) <> "\" Then
        FolderPath = FolderPath & "\"
    End If
    
    If Dir(FolderPath, vbDirectory) <> vbNullString Then
        MsgBox "Folder exist"
    Else
        MsgBox "Folder doesn't exist"
    End If
End Sub


Sub Test_Folder_Exist_FSO_Late_binding()
'No need to set a reference if you use Late binding
    Dim FSO As Object
    Dim FolderPath As String

    Set FSO = CreateObject("scripting.filesystemobject")

    FolderPath = "C:\Users\Ron\test"
    If Right(FolderPath, 1) <> "\" Then
        FolderPath = FolderPath & "\"
    End If

    If FSO.FolderExists(FolderPath) = False Then
        MsgBox "Folder doesn't exist"
    Else
        MsgBox "Folder exist"
    End If

End Sub


Sub Test_Folder_Exist_FSO_Early_binding()
'If you want to use the Intellisense help showing you the properties
'and methods of the objects as you type you can use Early binding.
'Add a reference to "Microsoft Scripting Runtime" in the VBA editor
'(Tools>References)if you want that.

    Dim FSO As Scripting.FileSystemObject
    Dim FolderPath As String

    Set FSO = New Scripting.FileSystemObject

    FolderPath = "C:\Users\Ron\test"
    If Right(FolderPath, 1) <> "\" Then
        FolderPath = FolderPath & "\"
    End If

    If FSO.FolderExists(FolderPath) = False Then
        MsgBox "Folder doesn't exist"
    Else
        MsgBox "Folder exist"
    End If

End Sub

 

Test if File exist

Below are three examples to test if the file "book1.xlsm" exists
FilePath = "C:\Users\Ron\test\book1.xlsm"

The first one uses VBA Dir and the other two use FSO (FileSystemObject). Note:read the information in the second FSO example good because you must set a reference to the "Microsoft Scripting Runtime" in the VBA editor if you want to use this example. When you use the first FSO example you not have to set a reference in the VBA editor to "Microsoft Scripting Runtime" because it use Late Binding.

When you use the Intellisense help you see that there are a lot more nice options when you use FSO.

Extension

Be sure you enter the correct extension in the FilePath. If you not see the extensions of your files in Windows Explorer you can do this to see them.

1: Open Windows Explorer
2: Win XP : Click on Tools>Folder Options
2: Vista or Windows 7 : Click on Organize>Folder and Search Options
3: On the View tab uncheck “Hide extensions for known file types"

Sub Test_File_Exist_With_Dir()
    Dim FilePath As String
    Dim TestStr As String

    FilePath = "C:\Users\Ron\test\book1.xlsm"

    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath)
    On Error GoTo 0
    If TestStr = "" Then
        MsgBox "File doesn't exist"
    Else
        MsgBox "File exist"
    End If

End Sub


Sub Test_File_Exist_FSO_Late_binding()
'No need to set a reference if you use Late binding
    Dim FSO As Object
    Dim FilePath As String

    Set FSO = CreateObject("scripting.filesystemobject")

    FilePath = "C:\Users\Ron\test\book1.xlsm"

    If FSO.FileExists(FilePath) = False Then
        MsgBox "file doesn't exist"
    Else
        MsgBox "File exist"
    End If

End Sub

Sub Test_File_Exist_FSO_Early_binding()
'If you want to use the Intellisense help showing you the properties
'and methods of the objects as you type you can use Early binding.
'Add a reference to "Microsoft Scripting Runtime" in the VBA editor
'(Tools>References)if you want that.

    Dim FSO As Scripting.FileSystemObject
    Dim FilePath As String

    Set FSO = New Scripting.FileSystemObject

    FilePath = "C:\Users\Ron\test\book1.xlsm"

    If FSO.FileExists(FilePath) = False Then
        MsgBox "File doesn't exist"
    Else
        MsgBox "File exist"
    End If

End Sub

 

Test if file is open

Below are two examples to test if the file "book1.xlsm" is open. The second example use a function(UDF), the advantage of a UDF is that all macros in your workbook can call this function and if you copy the UDF in a add-in all your open workbooks can use the UDF. Less code in your macros.

Sub Test_If_File_Is_Open_1()
    Dim TestWorkbook As Workbook

    Set TestWorkbook = Nothing
    On Error Resume Next
    Set TestWorkbook = Workbooks("Book1.xlsm")
    On Error GoTo 0

    If TestWorkbook Is Nothing Then
        MsgBox "The File is not open!"
    Else
        MsgBox "The File is open!"
    End If

End Sub

Do not forget to copy the function below the macro if you use the example below.

Sub Test_If_File_Is_Open_2()
    If bIsBookOpen("Book1.xlsm") Then
        MsgBox "The File is open!"
    Else
        MsgBox "The File is not open!"
    End If
End Sub

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
    On Error Resume Next
    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Note: if you have more then one Excel instance open and want to test if the workbook is open
in one of them then look at the code in this KB article.
http://support.microsoft.com/?kbid=138621

See also this page from Chip Pearson
http://www.cpearson.com/excel/IsFileOpen.aspx

 

Test if Sheet exists

Below are three examples to test if a sheet named "total" exists.

The third example use a function(UDF), the advantage of a UDF is that all macros in your workbook can call this function and if you copy the UDF in a add-in all your open workbooks can use the UDF. Less code in your macros.

Sub Sheet_Test_1()
    Dim sh As Worksheet

    On Error Resume Next
    Set sh = ActiveWorkbook.Sheets("total")
    If Err.Number <> 0 Then
        MsgBox "The sheet doesn't exist"
        Err.Clear
        On Error GoTo 0
    Else
        MsgBox "The sheet exist"
    End If

End Sub

Sub Sheet_Test_2()
    Dim SheetExist As Boolean

    SheetExist = False
    On Error Resume Next
    SheetExist = CBool(Len(ActiveWorkbook.Sheets.Item("total").Name))
    On Error GoTo 0

    If SheetExist = False Then
        MsgBox "The sheet doesn't exist"
    Else
        MsgBox "The sheet exist"
    End If

End Sub

Do not forget to copy the function below the macro if you use the example below.

Sub Sheet_Test_3_With_Function()
    If SheetExists("total", ActiveWorkbook) = False Then
        MsgBox "The sheet doesn't exist"
    Else
        MsgBox "The sheet exist"
    End If
End Sub

Function SheetExists(SName As String, _
                     Optional ByVal wb As Workbook) As Boolean
'Chip Pearson
    On Error Resume Next
    If wb Is Nothing Then Set wb = ThisWorkbook
    SheetExists = CBool(Len(wb.Sheets(SName).Name))
End Function

 

Copyright © 2022 Ron de Bruin

Site designed by Spider Web Woman Designs