Check if File/Folder exists | Mac Excel Automation

Check if File or Folder exists on Mac

Note : When you use VBA in Mac Excel 2016 or higher that is working with files and folders you will notice that it is possible that it will ask you permission to access the file or folder (Grant File Access Prompt), this is because of Apple’s sandbox requirements.This means that when you want to save/open files or check if it exists with VBA code the first time you will be prompted to allow access on the first attempt to access such a folder or file. If you want to avoid problems like this read this page : Problems with Apple’s sandbox requirements

In Excel 2011 we have problems with long file names when you use VBA Dir or GetAttr on a Mac, 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. If you want an example for 2011 you can mail me, I not publish it on my website because Excel 2011 is not supported anymore.

In Office 2016 and higher for the Mac they fixed the problem with long file names so we can use VBA Dir now in the 2016 and higher to test if a file or folder exists or you can use the AppleScriptTask examples to do it on this page : https://macexcel.com/examples/setupinfo/applescriptask/

Note: In the 4 macro examples below the macros call the FileOrFolderExistsOnYourMac function to test if a file or folder exists on your Mac Desktop.

Sub TestFile1()
    'Note: This macro uses the FileOrFolderExistsOnYourMac function.
    'Note : Use 1 as second argument for File and 2 for Folder
    'Test if a file with the name FileName.xlsm is on your desktop
    Dim FilePath As String
    FilePath = MacScript("return (path to desktop folder) as string") & "FileName.xlsm"

    If FileOrFolderExistsOnYourMac(FilePath, 1) = True Then
        MsgBox "File exists."
    Else
        MsgBox "File does not exist."
    End If
End Sub


Sub TestFile2()
    'Note: This macro uses the FileOrFolderExistsOnYourMac function.
    'Note : Use 1 as second argument for File and 2 for Folder
    'Test if a file with the name FileName.xlsm is on your desktop
    Dim FilePath As String
    FilePath = "/Users/rondebruin/Desktop/FileName.xlsm"

    If FileOrFolderExistsOnYourMac(FilePath, 1) = True Then
        MsgBox "File exists."
    Else
        MsgBox "File does not exist."
    End If
End Sub


Sub TestFolder1()
    'Note: This macro uses the FileOrFolderExistsOnYourMac function.
    'Note : Use 1 as second argument for File and 2 for Folder
    'Test if the folder with the name TestFolder is on your desktop
    Dim FolderPath As String
    FolderPath = MacScript("return (path to desktop folder) as string") & "TestFolder"

    If Right(FolderPath, 1) = Application.PathSeparator Then
        MsgBox "Remove the / at the end of the FolderPath"
        Exit Sub
    End If

    If FileOrFolderExistsOnYourMac(FolderPath, 2) = True Then
        MsgBox "Folder exists."
    Else
        MsgBox "Folder not exists."
    End If
End Sub


Sub TestFolder2()
    'Note: This macro uses the FileOrFolderExistsOnYourMac function.
    'Note : Use 1 as second argument for File and 2 for Folder
    'Test if the folder with the name TestFolder is on your desktop
    Dim FolderPath As String
    FolderPath = "/Users/rondebruin/Desktop/TestFolder"

    If Right(FolderPath, 1) = Application.PathSeparator Then
        MsgBox "Remove the / at the end of the FolderPath"
        Exit Sub
    End If

    If FileOrFolderExistsOnYourMac(FolderPath, 2) = True Then
        MsgBox "Folder exists."
    Else
        MsgBox "Folder not exists."
    End If
End Sub


Function FileOrFolderExistsOnYourMac(FileOrFolderstr As String, FileOrFolder As Long) As Boolean
    'Ron de Bruin : 13-Dec-2020, for Excel 2016 and higher
    'Function to test if a file or folder exist on your Mac
    'Use 1 as second argument for File and 2 for Folder
    Dim ScriptToCheckFileFolder As String
    Dim FileOrFolderPath As String
    
    If FileOrFolder = 1 Then
        'File test
        On Error Resume Next
        FileOrFolderPath = Dir(FileOrFolderstr & "*")
        On Error GoTo 0
        If Not FileOrFolderPath = vbNullString Then FileOrFolderExistsOnYourMac = True
    Else
        'folder test
        On Error Resume Next
        FileOrFolderPath = Dir(FileOrFolderstr & "*", vbDirectory)
        On Error GoTo 0
        If Not FileOrFolderPath = vbNullString Then FileOrFolderExistsOnYourMac = True
    End If
End Function