Excel 2016 for Mac - set starting path for choose folder dialog

[mcomp72]

I've written a Workbook in Windows, and am now trying to get it to work on Mac. One thing I can't figure out is how to tell Excel 2016/2019 for Mac to start at a particular folder when asking the user to choose a folder. Here's the code I'm using.

Sub GetFolderName_Mac()

'I modified code that I found on Ron de Bruin's website:
'https://www.rondebruin.nl/mac/mac017.htm

Dim RootFolder As String
Dim scriptstr As String

On Error Resume Next

If Val(Application.Version) < 15 Then 'Excel 2011 for Mac
        
    RootFolder = ThisWorkbook.Path
    
    scriptstr = "(choose folder with prompt ""Select the folder you want""" & _
        " default location alias """ & RootFolder & """) as string"

Else 'Excel 2016 for Mac or newer
    
    RootFolder = Replace(ThisWorkbook.Path, "/", ":")
    
    scriptstr = "return posix path of (choose folder with prompt ""Select the folder you want""" & _
        " default location alias """ & RootFolder & """) as string"

End If

MacFolderPath = MacScript(scriptstr)
On Error GoTo 0

'more code here to continue process

End Sub

If run on Excel 2011 for Mac, the code works. It does NOT work on Excel 2016 or 2019 for Mac. It seems to be because RootFolder is not quite correct. I had first tried setting RootFolder = ThisWorkbook.Path, but the MacScript command didn't like the slashes in the path. The variable has to have : as the path separator for that MacScript line to work. So that's why I am using the Replace command.

This is how RootFolder showing up on Excel 2016/2019 now:

:Users:mattc:Desktop:

I'm guessing I need the name of the hard drive at the beginning of the string, so it could look something like this:

macOS Sierra:Users:mattc:Desktop:

But 'ThisWorkbook.Path' doesn't return the hard drive name on Excel 2016/2019.

Anyone have any idea how I can get it to do what I want it to do? (I can't type in the hard drive name directly in the code, because my Workbook will be used by many different people.)

 

[mcomp72]

I figured out a solution to my problem. After much trial & error, I figured out how to return the name of the hard drive on a Mac.

Dim HardDriveName As String
HardDriveName = MacScript("return (path to startup disk) as string")

The value of HardDriveName, in my case, was "El Capitan:"

I can now concatenate this to the other string, to get the full string that I need.