Use an Open File dialog in VBA with both Mac and Windows

Function myGetOpenFileName(Optional sPath As String) As String
Dim sFile As String
Dim sMacScript As String

    If isMac Then
        If sPath = vbNullString Then
            sPath = "the path to documents folder"
        Else
            sPath = " alias """ & sPath & """"
        End If
        sMacScript = "set sFile to (choose file of type ({" & _
            """com.microsoft.Excel.xls"", ""org.openxmlformats.spreadsheetml.sheet""," & _
            """public.comma-separated-values-text"", ""public.text"", ""public.csv""," & _                   """org.openxmlformats.spreadsheetml.sheet.macroenabled""}) with prompt " & _
            """Select a file to import"" default location " & sPath & ") as string" _
            & vbLf & _
            "return sFile"
         Debug.Print sMacScript
        sFile = MacScript(sMacScript)

    Else 'windows

        sFile = Application.GetOpenFilename("CSV files,*.csv,Excel 2007 files,*.xlsx", 1, _
            "Select file to import from", "&Import", False)

    End If

    myGetOpenFileName = sFileEnd Function
Advertisements

3 Comments on “Use an Open File dialog in VBA with both Mac and Windows”

  1. WOW that exacly what i needed!!! Tx for posting this, it will help many other with the return of VBA in office 2011. Continue the good work!

  2. Kalle Forsman says:

    I think this might really help with my issue of porting a script to be used for mac users. But I’m kind of rusty and i get hung up on the part
    “sMacScript = “set sFile to (choose file of type ({” & _
    “””com.microsoft.Excel.xls””, “”org.openxmlformats.spreadsheetml.sheet””,” & _
    “””public.comma-separated-values-text””, “”public.text””, “”public.csv””,” & _ “””org.openxmlformats.spreadsheetml.sheet.macroenabled””}) with prompt ” & _
    “””Select a file to import”” default location ” & sPath & “) as string” _
    & vbLf & _
    “return sFile” ”
    Where VBA returns a compilation error, this part i surely made do have other information added to it witch i don’t get how to make the formatting for. All I’m trying to do is to save a .csv file. So other formats are not interesting.

    If you could explain so i understand that would really help me a lot.

    Thanks!

  3. Madhawa says:

    Thank you very much for the code.It was very helpful to me.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s