Excel VBA Check if directory exists error

Use the FolderExists method of the Scripting object.

Public Function dirExists(s_directory As String) As Boolean
    Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    dirExists = oFSO.FolderExists(s_directory)
End Function

To be certain that a folder exists (and not a file) I use this function:

Public Function FolderExists(strFolderPath As String) As Boolean
    On Error Resume Next
    FolderExists = ((GetAttr(strFolderPath) And vbDirectory) = vbDirectory)
    On Error GoTo 0
End Function

It works both, with \ at the end and without.


I ended up using:

Function DirectoryExists(Directory As String) As Boolean
    DirectoryExists = False
    If Len(Dir(Directory, vbDirectory)) > 0 Then
        If (GetAttr(Directory) And vbDirectory) = vbDirectory Then
            DirectoryExists = True
        End If
    End If
End Function

which is a mix of @Brian and @ZygD answers. Where I think @Brian's answer is not enough and don't like the On Error Resume Next used in @ZygD's answer


To check for the existence of a directory using Dir, you need to specify vbDirectory as the second argument, as in something like:

If Dir("C:\2013 Recieved Schedules" & "\" & client, vbDirectory) = "" Then

Note that, with vbDirectory, Dir will return a non-empty string if the specified path already exists as a directory or as a file (provided the file doesn't have any of the read-only, hidden, or system attributes). You could use GetAttr to be certain it's a directory and not a file.