Search code examples
vbaexcelexcel-2013

VBA "Check if folder exists" works only when there is a file in the folder

I got the original code from www.rondebruin.nl

It is designed to test whether or not a folder already exists in the directory. I modified it to fit my needs and it seemed to work well.

Today, i discovered that it only works properly if the folder being tested for is NOT empty. If it is empty, then it returns false (i.e. the folder does not exist).

I can't figure out why this is. 

FolderPath = sPfad
If Right(FolderPath, 1) <> "\" Then
    FolderPath = FolderPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(FolderPath)
On Error GoTo 0
If TestStr = "" Then
    Test_Folder_Exist_With_Dir = False
    Exit Function
Else
    Test_Folder_Exist_With_Dir = True
    Exit Function
End If

I suspect the answer lies in the TestStr = Dir(FolderPath) but haven't been able to get to the bottom of it. The MSDN article basically explains that Dir() always returns something. However the examples give are all such that there is a file present to return.

I basically need to get it so that it recognizes the folder regardless of whether there is something in it or not.

Any help appreciated!

Solution

  • The following line returns a number greater than 0 if the folder exists, regardless of whether the folder has any files in it

    len(dir("C:\Users\user\Desktop\Tests\tt", vbDirectory))