Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Excel for Mac - Get File Dialog

RSS
Modified on Sat, Jun 06, 2020, 8:12 AM by Administrator Categorized as Microsoft Office

Overview

Unlike Excel for Windows, Excel for Mac doesn't support the Application.FileDialog(msoFileDialogFilePicker) syntax. This article provides Excel VBA code that will work on both Mac and Windows.

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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2020, Patrick Jasinski.