In Windows this is a easy way to get your Documents folder path if you not changed the default file path.
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 :
home, documents, desktop, music, pictures, movies, applications