VBA Help: FileExists with status on Col J

[harky]

Hi
I need a smiliar vbs but with Sub, not function which able to update the status on Col J when i activate the macro.

It will left a remarks

Directory & Link Ok
File was not found in folder

The best if can differentiate which one is Folder, which is link

H	I	J
FOLDER Path​	SINGLE Path​	Status
	C:\Users\ABC\Desktop\SavedFolder\Email1\test.JPG	Link Ok
C:\Users\ABC\Desktop\SavedFolder\Email2\		Folder was not found

<tbody>
</tbody>

Function FileExist(path As String) As Boolean
    If Dir(path) <> vbNullString Then FileExist = True
End Function

 

[pbornemeier]

Some non-Dir functions to validate file/folder existence:

Function FileExists(ByVal FileSpec As String) As Boolean
  ' Karl Peterson MS VB MVP
  Dim Attr As Long
  ' Guard against bad FileSpec by ignoring errors
  ' retrieving its attributes.
  On Error Resume Next
  Attr = GetAttr(FileSpec)
  If Err.number = 0 Then
    ' No error, so something was found.
    ' If Directory attribute set, then not a file.
    FileExists = Not ((Attr And vbDirectory) = vbDirectory)
  End If
End Function

Function DirExists(ByVal FileSpec As String) As Boolean
  ' Karl Peterson MS VB MVP
  Dim Attr As Long
  ' Guard against bad FileSpec by ignoring errors
  ' retrieving its attributes.
  On Error Resume Next
  Attr = GetAttr(FileSpec)
  If Err.number = 0 Then
    ' No error, so something was found.
    ' If Directory attribute set, then not a file.
    DirExists = (Attr And vbDirectory) = vbDirectory
  End If
End Function

Function FSOFolderExists(sFolderName As String) As Boolean
    'With/Without trailing slash works
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    FSOFolderExists = fso.FolderExists(sFolderName)
    Set fso = Nothing

End Function

Function FSOFileExists(sFilePathNameExt As String) As Boolean

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    FSOFileExists = fso.FileExists(sFilePathNameExt)
    Set fso = Nothing

End Function