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

Question

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...")

score 5 · Answer 1 · answered Dec 15 '12 at 21:52

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!

In 2021, on new versions of Excel / new macbook, I still have to do this, but this was the ONLY solution that worked! To be VBA noobies, simply create a new module and copy the above function to it. Then modify your other code arrodingly with the IF Then lines of code but without the # — David Erickson, May 05 '21 at 23:29

score 1 · Accepted Answer · edited May 23 '17 at 12:13

1

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

edited May 23 '17 at 12:13

Community

1
1

answered Sep 04 '12 at 12:41

Siddharth Rout

147,039
17
206
250

What error message did you get? Is Excel 2011 updated with the latest service pack (though I doubt that will be the reason)? – Siddharth Rout Sep 04 '12 at 12:50
It is updated, and the error is `Run-time error '1004': Method: 'GetSaveAsFilename' of object '_Application' failed` in the line `NewFile = Application.GetSaveAsFilename(InitialFileName:="Open Order Log - " & Format(Date, "dd-mm-yyyy") & ".xlsx", fileFilter:=NewFileType)` – Matt Ridge Sep 04 '12 at 12:54
Ok When does the error occur? The moment that line is `executed`? or when you press `cancel` or when you press `save`? Can you show us the code for the complete sub? – Siddharth Rout Sep 04 '12 at 12:57
or better still can you upload your file? I can test it in MAC – Siddharth Rout Sep 04 '12 at 12:58
http://www.kaboomlabs.com/excel/OORv14.xlsm This will only be up for a few minutes... since it is a lot of what I've worked on since I've come here. That being said click on Jobs List tab, and click on Update PO Tracking. – Matt Ridge Sep 04 '12 at 13:06
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/16249/discussion-between-matt-ridge-and-siddharth-rout) – Matt Ridge Sep 04 '12 at 13:10
Ok, added in the script you linked to in chat, and I'm getting an error, after the script opens the browser screen, and I select the file in question, and then once selected I open it and I get this error. http://www.kaboomlabs.com/excel/img/error.jpg the error highlights `Set opened_workbook = Application.Workbooks.Open(filename)` here. – Matt Ridge Sep 04 '12 at 13:52
Then it is a different thing altogether :) At least your `Application.GetOpenFilename` lcode is now working and not giving you any error. :) I will check your file in sometime. – Siddharth Rout Sep 04 '12 at 13:57
Thanks, just edited prior response with the error highlighted. – Matt Ridge Sep 04 '12 at 13:57
What value do you get for `filename` when you debug it? – Siddharth Rout Sep 04 '12 at 14:05
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/16253/discussion-between-siddharth-rout-and-matt-ridge) – Siddharth Rout Sep 04 '12 at 14:08

score 0 · Answer 3 · answered Feb 16 '13 at 04:31

0

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

answered Feb 16 '13 at 04:31

JeeShen Lee

3,476
5
39
59

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

3 Answers3

Linked

Related