Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Select Folder with VBA/Macscript in Mac Office

With the example below you can select a folder in a browse dialog and also set the Root folder. In this example it will always open on the desktop, see the commented line in the code to use a fixed path. Note : if you want to use a fixed path use the  : as separator in both 2011 and 2016.

Sub Select_Folder_On_Mac()
    Dim folderPath As String
    Dim RootFolder As String
    Dim scriptstr As String

    On Error Resume Next
    RootFolder = MacScript("return (path to desktop folder) as String")
    'Or use RootFolder = "Macintosh HD:Users:YourUserName:Desktop:TestMap:"
    'Note : for a fixed path use : as seperator in 2011 and 2016

    If Val(Application.Version) < 15 Then
        scriptstr = "(choose folder with prompt ""Select the folder""" & _
            " default location alias """ & RootFolder & """) as string"
    Else
        scriptstr = "return posix path of (choose folder with prompt ""Select the folder""" & _
            " default location alias """ & RootFolder & """) as string"
    End If

    folderPath = MacScript(scriptstr)
    On Error GoTo 0

    If folderPath <> "" Then
        MsgBox folderPath
    End If
End Sub

You see that in 2011 it return a path like this :

Macintosh HD:Users:RDB:Desktop:YourFolder:

And in 2016  a path like this :

/Users/RDB/Desktop/YourFolder/

Reason is that in 2016 you must use the posix path in VBA codes