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, 22-June-2015 ' http://www.rondebruin.nl/mac/mac010.htm Dim ScriptToMakeFolder As String Dim str 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 Else str = MacScript("return POSIX path of (" & _ Chr(34) & Folderstring & Chr(34) & ")") MkDir str End If End Function
Important for Excel 2016, read this page :