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 GetSpecialFolderPath_MacScript()
'Return the path of special folders on you Mac
'Ron de Bruin, 21-Sept-2017
'Is working in Excel 2011 and 2016
    Dim NameFolder As String

    NameFolder = "documents folder"

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

    MsgBox SpecialFolder

'***Other folders that you can use are***
'applications folder
'desktop folder
'desktop pictures folder
'documents folder
'downloads folder
'favorites folder
'Folder Action scripts
'Fonts
'Help
'home folder
'internet plugins folder from user domain
'keychain folder
'library folder
'modem scripts folder from user domain
'movies folder
'music folder
'Pictures folder
'preferences
'printer descriptions from local domain
'Public folder
'scripting additions folder
'scripts folder
'services folder
'shared documents
'shared libraries folder from user domain
'sites folder
'startup disk
'startup items
'system folder
'system preferences
'temporary items
'trash
'users folder
'utilities folder
'workflows folder
'voices
End Sub

You see in the code that I use a replace line for the documents and home folder in the part for Excel 2016 to get the correct path. For example you can use this line instead of the 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

We can do the same with the new AppleScriptTask function in Mac Excel 2016, see this page on my site if you want to know more about the AppleScriptTask function. You can mail me for a example.