user prompt to import CSV into active worksheet (mac)

Hi all,

I am wondering if anybody could help with creating a macro that lets the user choose a CSV file to import into a worksheet.

I have done a bit of research and it seems that Mac Office has a bit of trouble with GetOpenFilename or Application.FileDialog(msoFileDialogFilePicker)

When I record the macro of just importing one particular csv I get:

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;Macintosh HD:Users:joshuacarter:Desktop:Macro test:55_bow56-9.csv", _
        Destination:=Range("A1"))
        .Name = "55_bow56-9"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlMacintosh
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
        .UseListObject = False
    End With
End Sub

This is just a for a specific csv file and what I would like to do is have the user be able to choose the file and then the import happen with the parameters needed into the active worksheet.

I have tinkered around with some things I have found and came up with a macro that does give a prompt:

Sub Select_File_Or_Files_Mac()
    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:YourUserName:Desktop:TestFolder:"

    MyScript = "set applescript's text item delimiters to (ASCII character 10) " & 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)
    On Error GoTo 0

    If MyFiles <> "" Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    MySplit = Split(MyFiles, Chr(10))
        For N = LBound(MySplit) To UBound(MySplit)

            'Get file name only and test if it is open
            fname = Right(MySplit(N), Len(MySplit(N)) - InStrRev(MySplit(N), _
            Application.PathSeparator, , 1))
            
           Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MySplit(N))
                On Error GoTo 0
                
                Next
                Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
ws.Name = "CSV"

With ActiveSheet.QueryTables.Add( _
        Connection:="TEXT;" & fname, _
        Destination:=Range("A1"))
        .Name = "CSV"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlMacintosh
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
End With
             End If

                

End Sub

Once the prompt is given on this macro and the user selects the csv, it imports the csv into a completely new workbook and does not apply the delimiters.

How can I tailor the above macro to import into the active worksheet and also maintain the delimiters?

thanks in advance!