CODEWITHSUNDEEP
vbaexcel
user6632933
user6632933

Reputation:

Check if directory exists

I have this piece of code, but can't make it run for it returns Bad file name or number error : 

If Dir(ws.txtFldr, vbDirectory) = "" Then
    MsgBox "Output Directory does not exist!", vbExclamation, "Error!"
    Exit Sub
End If

I just want to check if the given directory exists, if not, then prompt the message. txtFldr is an activeX textbox control and ws is a worksheet. I'm actually saving it in either a shared dir or just local.

Upvotes: 1

Views: 13834

Answers (3)

Jerry
Jerry

Reputation: 98

Option Explicit

Sub CheckFolderExistsWithInputBox() Dim folderPath As String

' Prompt the user to enter the folder path
folderPath = Trim(InputBox("Please enter the directory path to check:", "Check Folder"))

' Check if the user canceled the input or left it blank
If folderPath = "" Then
    MsgBox "No folder path specified!", vbExclamation, "Error!"
    Exit Sub
End If

' Check if the directory exists
If Dir(folderPath, vbDirectory) = "" Then
    MsgBox "Output Directory does not exist!", vbExclamation, "Error!"
    Exit Sub
End If

' If the folder exists, proceed
MsgBox "Directory exists!", vbInformation, "Success"

End Sub

Upvotes: 0

freen
freen

Reputation: 1

Unfortunately Dir(strFullPath, vbDirectory) returns the name of a file or a directory matching strFullPath. To check the name refers to a directory you have to use the GetAttr function:

Public Function FileFolderExists(stringfolderName As String) As Boolean
    FileFolderExists = False
    If Dir(stringfolderName, vbDirectory) = "" Then Return
    If GetAttr(stringfolderName) And vbDirectory Then
       FileFolderExists = True
    End If
 End Function

Upvotes: 0

Andrew Truckle
Andrew Truckle

Reputation: 19207

How about this:

If Len(Dir(ws.txtFldr, vbDirectory)) = 0 Then
    MsgBox "Output Directory does not exist!", vbExclamation, "Error!"
    Exit Sub
End If

Many other ways are also shown by doing a search on the internet. For example, here it has another approach:

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists

    If strFullPath = vbNullString Then Exit Function
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True

EarlyExit:
    On Error GoTo 0
End Function

Usage:

Public Sub TestFolderExistence()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Test if directory exists
    If FileFolderExists(ws.txtFldr) Then
        MsgBox "Folder exists!"
    Else
        MsgBox "Folder does not exist!"
    End If
End Sub

This second approach is using some the Error handling functionality available in VBA and GoTo labels to bypass code.

Upvotes: 2

Related Questions