使用Query Tables从CSV生成多份Excel工作簿：格式参数为何不生效？

背景 #

我需要将100多份法语、分号分隔的CSV文件导入Excel，但由于使用搭载Apple硅芯片的Mac，无法使用内置PowerQuery功能，于是修改了VBA与AppleScript脚本实现自动化导入。

现有代码 #

VBA脚本 #

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

    MyFiles = AppleScriptTask("excelCSVselect.scpt", "select_files", "")
    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), _
'             ":", , 1))
            
                On Error Resume Next
                Set mybook = Workbooks.Open(MySplit(N))
                On Error GoTo 0
             Next
             
Worksheets("Sheet1").Activate

With ActiveSheet.QueryTables.Add( _
        Connection:="TEXT;" & Fname, _
        Destination:=Range("A1"))
        .FieldNames = True
        .RowNumbers = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .AdjustColumnWidth = True
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileSemicolonDelimiter = True

'        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
End With
           
              End If

End Sub

AppleScript脚本（excelCSVselect.scpt） #

用于多选CSV并转换为POSIX路径列表：

on convertListToString(theList, theDelimiter)
    set AppleScript's text item delimiters to theDelimiter
    set theString to theList as string
    set AppleScript's text item delimiters to ""
    return theString
end convertListToString

on select_files()
    set filelist to {}
    set CSV_files to choose file with prompt "Pick CSV Files to convert" with multiple selections allowed
    
    repeat with a in CSV_files
        set end of filelist to (POSIX path of a as string)
    end repeat
    return convertListToString(filelist, "\n")
end select_files

select_files()

当前遇到的问题 #

• QueryTables.Add的格式参数未生效：CSV每行内容显示为整块文本，推测.TextFileSemicolonDelimiter = True未执行
• 法语特殊字符显示异常，.TextFilePlatform = 65001未生效
• 运行脚本后无法进入VBA项目，必须退出Excel才可恢复；曾删除部分QueryTable属性引发400错误，已恢复原代码

内容的提问来源于stack exchange，提问作者Ordinateur_Erreur