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.

See this page if you just started with VBA.
http://www.rondebruin.nl/code.htm


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 Intellisense help you see that there are a lot more nice options when you use FSO.

I use it for example on this page
http://www.rondebruin.nl/folder.htm

Sub Test_Folder_Exist_With_Dir()
    Dim FolderPath As String
    Dim TestStr As String

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

    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FolderPath)
    On Error GoTo 0
    If TestStr = "" Then
        MsgBox "Folder doesn't exist"
    Else
        MsgBox "Folder 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 the Intellisense help you see that there are a lot more nice options when you use FSO.

I use it for example on this page
http://www.rondebruin.nl/folder.htm


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 : 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"

    If FSO.FolderExists(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 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

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