Given sample Excel data in the format:
filepath | test |
---|---|
C:\file.txt | =PERSONAL.XLSB!checkExists(A2) |
C:\dir\file.txt | TRUE |
C:\fake_dir | FALSE |
C:\fake_dir\file.txt | FALSE |
TRUE |
I want to run the following User-Defined Formula, called checkExists
; this uses DIR
to check the value in a given cell to see if that directory or file exists, and returns a boolean.
Public Function checkExists(path As String) As Boolean
' Check if string is empty
If (IsEmpty(path) = False) Then
' Check if file/dir exists
If ((Dir(path, vbNormal + vbDirectory)) <> vbNullString = False) Then
' String is not empty, and file/dir exists; return True
checkExists = True
Else
' File does not exist; return False
checkExists = False
End If
Else
' String is empty; return False
checkExists = False
End If
End Function
I'm struggling with getting it to recognise that a given cell is blank -- such as A6 here. The formula as written returns TRUE
here, when I need FALSE
.
I realise I'm passing it a String
, and there is probably another object type I need to pass it, such as a Range or Variant... but I can't work out how to get those object types to work with my code. I also don't know if using both vbNormal
(files) and vbDirectory
(directories) is causing an issue. I've looked up calling things like ActiveCell
, {Range}.Address
, and so on, but I'm stuck.
Any help would be much appreciated. Thank you.
Dir
Using Len
Public Function checkExists(fPath As String) As Boolean
' Check if the length of the string is greater than 0
If Len(fPath) > 0 Then
' Check if the length of 'Dir' is greater than 0
If Len(Dir(fPath, vbNormal + vbDirectory)) > 0 Then
' File/dir exists; return True
checkExists = True
Else
' File does not exist; checkExists is False by default
'checkExists = False
End If
Else
' The length of the string is 0; checkExists is False by default
'checkExists = False
End If
End Function
Using Len
Public Function checkExistsLen(fPath As String) As Boolean
If Len(fPath) > 0 Then
If Len(Dir(fPath, vbNormal + vbDirectory)) > 0 Then
checkExists = True
End If
End If
End Function
Using vbNullString
or ""
Public Function checkExists(fPath As String) As Boolean
If fPath <> "" Then
If Dir(fPath, vbNormal + vbDirectory) <> "" Then
checkExists = True
End If
End If
End Function