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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD]

FOLDER Path​

[/TD]
[TD]

SINGLE Path​

[/TD]
[TD="align: center"]Status[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C:\Users\ABC\Desktop\SavedFolder\Email1\test.JPG[/TD]
[TD]Link Ok[/TD]
[/TR]
[TR]
[TD]C:\Users\ABC\Desktop\SavedFolder\Email2\[/TD]
[TD][/TD]
[TD]Folder was not found[/TD]
[/TR]
</tbody>[/TABLE]

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