Excel for Mac - Get File Dialog

Overview¶

Code¶

'======================================================================================================================
Public Function GetFileName(dlgTitle As String) As String

    Dim result As String
    
    result = ""
    
    #If Mac Then
        result = GetFileName_Mac()
    #Else
        result = GetFileName_Windows(dlgTitle)
    #End If
    
    GetFileName = result
    
End Function
'======================================================================================================================
Public Function GetFileName_Windows(dlgTitle As String) As String
    
    Dim result As String
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
     
        .Filters.Clear
        .Filters.Add "CSV Files (*.csv)", "*.csv", 1
        .Filters.Add "All Files (*.*)", "*.*", 2
        .title = dlgTitle
        .AllowMultiSelect = False
     
        If .Show = True Then
     
            result = .SelectedItems(1)
     
        End If
     
    End With
    
    GetFileName_Windows = result

End Function
'======================================================================================================================
' Adapted from https://stackoverflow.com/questions/12263016/is-there-a-mac-pc-variant-of-application-getopenfilename
Function GetFileName_Mac() As String

    Dim startPath As String
    Dim script As String
    Dim result As String
    Dim MySplit As Variant
    Dim N As Long
    Dim FName As String
    Dim mybook As Workbook

    On Error Resume Next
    startPath = MacScript("return (path to documents folder) as String")
    
    'Or use startPath = "Macintosh HD:Users:Ron:Desktop:TestFolder:"

    ' In the following statement, change true to false in the line "multiple
    ' selections allowed true" if you do not want to be able to select more
    ' than one file. Additionally, if you want to filter for multiple files, change
    ' {""com.microsoft.Excel.xls""} to
    ' {""com.microsoft.excel.xls"",""public.comma-separated-values-text""}
    ' if you want to filter on xls and csv files, for example.
    script = _
    "set applescript's text item delimiters to "","" " & vbNewLine & _
               "set theFiles to (choose file of type " & _
             " {""public.comma-separated-values-text""} " & _
               "with prompt ""Please select a file or files"" default location alias """ & _
               startPath & """ multiple selections allowed false) as string" & vbNewLine & _
               "set applescript's text item delimiters to """" " & vbNewLine & _
               "return theFiles"

    result = MacScript(script)
    result = Replace$(result, "Macintosh HD", "", Count:=1)
    result = Replace$(result, ":", "/")
    GetFileName_Mac = result
    
End Function