Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Make Directory if it not exist

The macros below will make a folder in an existing folder if it not exist. Important : every macro call the function named MakeFolderIfNotExist, copy this custom function also in your code module. Note: I use the special folder desktop in the first example but you can use other special folders also. Check out this page for more info.

Note : The reason that I use Applescript instead of VBA MkDir in the 2011 part of the function is that Excel 2011 have problems with long folder names (max of 32 characters). This is fixed in the new Mac Office version. The code below will work in 2011 and up if it is correct. If you have problems let me know.

Sub MakeFolderTest1()
'Make folder on the Desktop
    MakeFolderIfNotExist (MacScript("return (path to desktop folder) as string") & "TestFolder1")
End Sub

Sub MakeFolderTest2()
'Add folder in the same path as your workbook with this code
    MakeFolderIfNotExist (ThisWorkbook.Path & Application.PathSeparator & "TestFolder2")
End Sub

'Change the path of the two macro below before you test them

Sub MakeFolderTest3()
'Enter the complete path
    MakeFolderIfNotExist ("YosemiteLacie256:Users:rondebruin:Desktop:TestFolder3")
End Sub

Sub MakeFolderTest4()
'Do not include the harddisk name if you use a posix path
    MakeFolderIfNotExist ("/Users/rondebruin/Desktop/TestFolder4")
End Sub
Function MakeFolderIfNotExist(Folderstring As String)
' Ron de Bruin, 2-March-2019
    Dim ScriptToMakeFolder As String
    Dim TestStr As String
    Dim FolderStr As String
    If Val(Application.Version) < 15 Then
        ScriptToMakeFolder = "tell application " & Chr(34) & _
                             "Finder" & Chr(34) & Chr(13)
        ScriptToMakeFolder = ScriptToMakeFolder & _
                "do shell script ""mkdir -p "" & quoted form of posix path of (" & _
                        Chr(34) & Folderstring & Chr(34) & ")" & Chr(13)
        ScriptToMakeFolder = ScriptToMakeFolder & "end tell"
        On Error Resume Next
        MacScript (ScriptToMakeFolder)
        On Error GoTo 0
        FolderStr = MacScript("return POSIX path of (" & _
                        Chr(34) & Folderstring & Chr(34) & ")")
        On Error Resume Next
        TestStr = Dir(FolderStr & "*", vbDirectory)
        On Error GoTo 0
        If TestStr = vbNullString Then
            MkDir FolderStr
        End If
    End If
End Function

Important for Excel 2016, read this page :

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