Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Check if File or Directory exists

There are problems with long file names when you use VBA Dir or GetAttr on a Mac in Office 2011, 27/28 characters (without the ext) is the maximum(with the extension this is 32 characters).The FSO model is not available in Office for the Mac and many other code on the internet have problems with long file names.

But you can call AppleScript with VBA to avoid the problem with long file names in Office 2011. In the new Office 2016 for the Mac version there are problems with running applescript but they fixed the problem with long file names so we can use VBA Dir in the 2016 version.

Note: In the 6 macro examples below the macros call the FileOrFolderExistsOnMac function to test a file or folder.

Important for Excel 2016, read this page :

Problems with Apple’s sandbox requirements and Mac Office 2016 with VBA code

 
Sub TestFile()
'Note: This macro uses the FileOrFolderExistsOnMac function.
'Test if a file with the name FileName.xlsm is on your desktop
    If FileOrFolderExistsOnMac(MacScript("return (path to desktop folder) as string") & _
                               "FileName.xlsm") = True Then
        MsgBox "File exists."
    Else
        MsgBox "File does not exist."
    End If
End Sub

Sub TestFile1()
'Note: This macro uses the FileOrFolderExistsOnMac function.
    If FileOrFolderExistsOnMac("YosemiteLacie256:Users:rondebruin:Desktop:FileName.xlsm") = True Then
        MsgBox "File exists."
    Else
        MsgBox "File does not exist."
    End If
End Sub

Sub TestFile2()
'Note: This macro uses the FileOrFolderExistsOnMac function.
    If FileOrFolderExistsOnMac("/Users/rondebruin/Desktop/FileName.xlsm") = True Then
        MsgBox "File exists."
    Else
        MsgBox "File does not exist."
    End If
End Sub



Sub TestFolder()
'Note: This macro uses the FileOrFolderExistsOnMac function.
'Test if a folder with the name TestFolder is on your desktop
    If FileOrFolderExistsOnMac(MacScript("return (path to desktop folder) as string") & _
                               "TestFolder") = True Then
        MsgBox "Folder exists."
    Else
        MsgBox "Folder does not exist."
    End If
End Sub

Sub TestFolder1()
'Note: This macro uses the FileOrFolderExistsOnMac function.
    If FileOrFolderExistsOnMac("YosemiteLacie256:Users:rondebruin:Desktop:TestFolder") = True Then
        MsgBox "Folder exists."
    Else
        MsgBox "Folder does not exist."
    End If
End Sub

Sub TestFolder2()
'Note: This macro uses the FileOrFolderExistsOnMac function.
    If FileOrFolderExistsOnMac("/Users/rondebruin/Desktop/TestFolder") = True Then
        MsgBox "Folder exists."
    Else
        MsgBox "Folder does not exist."
    End If
End Sub


Function FileOrFolderExistsOnMac(FileOrFolderstr As String) As Boolean
'Ron de Bruin : 26-June-2015
'Function to test whether a file or folder exist on a Mac in office 2011 and up
'Uses AppleScript to avoid the problem with long names in Office 2011,
'limit is max 32 characters including the extension in 2011.
    Dim ScriptToCheckFileFolder As String
    Dim TestStr As String

    If Val(Application.Version) < 15 Then
        ScriptToCheckFileFolder = "tell application " & Chr(34) & "System Events" & Chr(34) & _
         "to return exists disk item (" & Chr(34) & FileOrFolderstr & Chr(34) & " as string)"
        FileOrFolderExistsOnMac = MacScript(ScriptToCheckFileFolder)
    Else
        On Error Resume Next
        TestStr = Dir(FileOrFolderstr, vbDirectory)
        On Error GoTo 0
        If Not TestStr = vbNullString Then FileOrFolderExistsOnMac = True
    End If
End Function

 

Thanks to DJ Bazzie Wazzie(poster on MacScripter) for his great help.