Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Get path of Special folders on Mac

In Windows this is a easy way to get your Documents folder path if you not changed the default file path.

Application.DefaultFilePath

But on a Mac this will return nothing if you not set it.
Mac Excel does not have a default directory until you set one in
Excel > preferences > General > Preferred File Location

But you can use MacScript like this if you want to get the path of your Documents folder or other special folders on your Mac. Change the NameFolder string to the special folder name that you want to get from the system.

Sub SpecialFolderExample()
' Call AppleScript to get a special folder
    Dim NameFolder As String
    Dim SpecialFolder As String

' You can use : home, documents, desktop, music, pictures, movies, applications
    NameFolder = "documents"

    If Int(Val(Application.Version)) > 14 Then
    SpecialFolder = _
    MacScript("return POSIX path of (path to " & NameFolder & " folder) as string")
    'Replace line needed for the special folders Home and documents
    SpecialFolder = _
    Replace(SpecialFolder, "/Library/Containers/com.microsoft.Excel/Data", "")
    Else
    SpecialFolder = MacScript("return (path to " & NameFolder & " folder) as string")
    End If

    MsgBox SpecialFolder
End Sub

You see in the code that I use a replace line for the documents and home folder in the part of Excel 2016. For example you can use this line instead of the last msgbox line to open a file in your documents folder if you want : Workbooks.Open SpecialFolder & "FileName.xlsm"

There is also code on my site to test if it file exists before you try to open it, look here.

Important for Excel 2016, read this page :

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

 

Other special folders are :

home, documents, desktop, music, pictures, movies, applications