Make Folder if it not exist 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

Note
: on this page is example code to create a folder in the Office folder, check it out.

The macros below will make a folder in an existing folder if it not exist. Note: I use the special folder desktop in one example but you can use other special folders also. Check out this page for more info.

Sub TestMakeFolder1()
'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") & "TestFolder1"

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
MkDir MacScript("return POSIX path of (" & Chr(34) & FolderPath & Chr(34) & ")")
MsgBox "Folder not exists but created ."
End If
End Sub


Sub TestMakeFolder2()
'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/TestFolder2/"

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
MkDir MacScript("return POSIX path of (" & Chr(34) & FolderPath & Chr(34) & ")")
MsgBox "Folder not exists but created ."
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
11-May-2021
Web design by Will Woodgate