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.