Using MacScript with VBA to pull a file name

[Orion19]

Hi All and TIA,

I'm trying to use VBA to launch a file picker and save the file name that the user selects in a certain cell (on a Mac - msoFileDialogFilePicker isn't compatible). I managed to do this with the file path using the code below. However, I found the MacScript command on google and can't figure out how to edit it to store the file name instead of the file path. Any suggestions? Thanks again!!!

Sub AddWordTemplate()

Dim FirstRow As Long
Dim folderPath As String
Dim RootFolder As String
Dim scriptstr As String

FirstRow = Sheet2.Range("F99999").End(xlUp).Row + 1 
RootFolder = MacScript("return (path to desktop folder) as String")
scriptstr = "(choose file with prompt ""Select the file""" & " default location alias """ & RootFolder & """) as string"
folderPath = MacScript(scriptstr)
Sheet2.Range("F" & FirstRow).Value = folderPath

End Sub

 

[Orion19]

I figured it out by finding a function meant to clear everything to the left of the last / in a file path. I changed it to ":", updated the sub, and it worked great. My apologies to who wrote the function. In all my googling I lost track of who to give credit to.

Sub AddWordTemplate()

Dim FirstRow As Long
Dim folderPath As String
Dim RootFolder As String
Dim scriptstr As String

FirstRow = Range("F99999").End(xlUp).Row + 1
RootFolder = MacScript("return (path to desktop folder) as String")
scriptstr = "(choose file with prompt ""Select the file""" & " default location alias """ & RootFolder & """) as string"
folderPath = MacScript(scriptstr)
Range("F" & FirstRow).Value = folderPath
Range("E" & FirstRow).Value = GetFilenameFromPath(folderPath)


End Sub

Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost ':'

    If Right$(strPath, 1) <> ":" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If

End Function