Get path of Special folders on Mac

In VBA code Application.DefaultFilePath is often used to get the path to your Documents folder for example, in the older Excel versions you can set the path in Excel>Preferences but not anymore now so it is not very useful anymore. But how do you get the path to special folders on your Mac, you can use MacScript in VBA like in the macro below if you want to get the path of your Desktop 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.

With most folders you can use one line of code
MsgBox MacScript("return posix path of (path to desktop folder) as string")

But for Documents and Home you need also a replace line like I show you in the macro below, see why when you test this line : MsgBox MacScript("return posix path of (path to documents folder) as string")


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 and higher
Dim NameFolder As String

NameFolder = "desktop folder"

If Int(Val(Application.Version)) > 14 Then
'You run Mac Excel 2016 or higher
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 folder
'Help folder
'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

For example you can use this line instead of the msgbox line to open a file in your desktop folder if you want : Workbooks.Open SpecialFolder & "FileName.xlsx"
31-July-2021
Web design by Will Woodgate