CODEWITHSUNDEEP
excelvbamacos
Matt Ridge
Matt Ridge

Reputation: 3651

Is there a Mac/PC variant of Application.GetOpenFilename?

Is there a universal OS variant of this? I am looking to have this code work on a Mac and PC, but this seems only to work on a PC. 

strFile = Application.GetOpenFilename("Text Files (.csv),.csv", , "Please selec text file...")

Upvotes: 3

Views: 16246

Answers (4)

Greg Lovern
Greg Lovern

Reputation: 977

This filters the GetOpenFilename dialog on CSV files for both Mac and Windows. The FileFilter parameter requirement for CSV is mutually exclusive on the two platforms:

Dim strOpenfilePath As String

#If Mac Then
    strOpenfilePath = Application.GetOpenFilename(FileFilter:="CSV", Title:="Open the CSV file...")
#Else
    strOpenfilePath = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv), *.csv", Title:="Open the CSV file...")
#End If

Upvotes: 0

JeeShen Lee
JeeShen Lee

Reputation: 3816

Check out the solution on MSDN - Programmatically Selecting Files in Excel for Windows and Excel for the Mac

Upvotes: 0

jrwagz
jrwagz

Reputation: 586

I find that I am able to use Application.GetSaveAsFileName on both PC and Mac without issue.

FName = Application.GetSaveAsFilename(fileFilter:=filterString, InitialFileName:=myInitialFileName)

However I also found that Application.GetOpenFilename does not work on the Mac, so I did some googling and came up with this function as a workaround on the Mac:

#If Mac Then
    tempfnameList = Select_File_Or_Files_Mac()
#Else
    tempfnameList = Application.GetOpenFilename(fileFilter:=filterString, Title:="Select File(s) to Open", MultiSelect:=True)
#End If

Here is the implementation of Select_File_Or_Files_Mac:

Function Select_File_Or_Files_Mac() As String()
    Dim MyPath As String
    Dim MyScript As String
    Dim MyFiles As String
    Dim MySplit As Variant
    Dim N As Long
    Dim FName As String
    Dim mybook As Workbook

    On Error Resume Next
    MyPath = MacScript("return (path to documents folder) as String")
    'Or use MyPath = "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.
    MyScript = _
    "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 """ & _
               MyPath & """ multiple selections allowed true) as string" & vbNewLine & _
               "set applescript's text item delimiters to """" " & vbNewLine & _
               "return theFiles"

    MyFiles = MacScript(MyScript)
    Dim returnList() As String
    On Error GoTo 0

    If MyFiles <> "" Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        'MsgBox MyFiles
        MySplit = Split(MyFiles, ",")
        ReDim returnList(LBound(MySplit) To UBound(MySplit))
        For N = LBound(MySplit) To UBound(MySplit)

            returnList(N) = MySplit(N)

        Next N
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        Select_File_Or_Files_Mac = returnList
    Else
        ReDim returnList(0 To 0)
        returnList(0) = "False"
        Select_File_Or_Files_Mac = returnList
    End If
End Function

I hope this helps!

Upvotes: 5

Siddharth Rout
Siddharth Rout

Reputation: 149295

It works in MAC (Excel 2011) As well. See screen shot

enter image description here

FOLLOWUP Upon discussion in chat as I suspected the error was not with Application.GetSaveAsFilename but something else. In this case it was Application.GetOpenFilename

Now Application.GetOpenFilename definitely gives a problem in Mac. I would recommend seeing this thread which also addresses your problem.

Error when closing an opened workbook in VBA Userform

Upvotes: 1

Related Questions