Check if File or Directory exists

Here are problems with long file names with Dir on a Mac, 27/28 characters (without the ext) is the maximum,
together with the extension it is 32 characters.
The macro below will not give you the correct answer with long file names.

Sub Test_File_Exist_With_Dir()
    Dim FilePath As String
    Dim TestStr As String

    'After 32 filename + extension characters the test is not working anymore
    FilePath = "Ron:Users:rondebruin:Documents:123451234512345123451234512.xlsm"

    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath)
    On Error GoTo 0
    If TestStr = "" Then
        MsgBox "File doesn't exist"
    Else
        MsgBox "File exist"
    End If

End Sub




But you can call AppleScript with VBA, no problems with the max of 27 file name characters.
Below there is a example macro to test a file and a folder.

Sub FileExistsOnMac()
'Call AppleScript to test if file exists, this example have no problem
'with long file names like the other examples(max of 27/28 characters)
Dim Filestr As String
Dim scriptToRun As String
Dim Result As Boolean

Filestr = "Macintosh HD:Users:Ron:Documents:MyWorkbook 15-nov-11 16-22-57.pdf"

scriptToRun = scriptToRun & "tell application " & Chr(34) & "Finder" & Chr(34) & Chr(13)
scriptToRun = scriptToRun & "exists file " & Chr(34) & Filestr & Chr(34) & Chr(13)
scriptToRun = scriptToRun & "end tell" & Chr(13)

Result = MacScript(scriptToRun)
MsgBox Result
End Sub



Sub FolderExistsOnMac()
'Call AppleScript to test if folder exists
Dim Filestr As String
Dim scriptToRun As String
Dim Result As Boolean

Filestr = "Macintosh HD:Users:Ron:Documents:Test"

scriptToRun = scriptToRun & "tell application " & Chr(34) & "Finder" & Chr(34) & Chr(13)
scriptToRun = scriptToRun & "exists folder " & Chr(34) & Filestr & Chr(34) & Chr(13)
scriptToRun = scriptToRun & "end tell" & Chr(13)

Result = MacScript(scriptToRun)
MsgBox Result
End Sub