Mac Ventura下Excel VBA指定UTF-8加载CSV到数组的方法

针对Mac Ventura下Excel VBA无法用Open语句指定UTF-8编码导致乱码、且无法使用Scripting.FileSystemObject和ADODB.Stream的问题，提供两种可行方案：

方法1：借助AppleScript读取UTF-8文件 #

利用Mac系统的AppleScript读取UTF-8编码的文件内容，再传递给VBA解析为数组，绕开Open语句的编码限制。

Function ReadUTF8CSVToArray(filePath As String) As Variant
    Dim appleScriptCode As String
    Dim fileContent As String
    Dim lines() As String
    Dim dataArray() As Variant
    Dim i As Long, j As Long
    
    ' 构建AppleScript代码，读取UTF-8格式文件
    appleScriptCode = "set fileRef to open for access file " & Quote(filePath) & _
                      " set fileContent to read fileRef as «class utf8»" & _
                      " close access fileRef" & _
                      " return fileContent"
                      
    ' 执行AppleScript获取文件内容
    fileContent = MacScript(appleScriptCode)
    
    ' 统一换行符并分割为行
    lines = Split(Replace(fileContent, vbCr, vbLf), vbLf)
    
    ' 初始化数组并填充数据
    ReDim dataArray(0 To UBound(lines), 0 To 0)
    For i = 0 To UBound(lines)
        If Trim(lines(i)) <> "" Then
            Dim cols() As String
            cols = Split(lines(i), ",")
            
            ' 动态调整数组列数
            If UBound(cols) > UBound(dataArray, 2) Then
                ReDim Preserve dataArray(0 To UBound(lines), 0 To UBound(cols))
            End If
            
            ' 写入每行数据
            For j = 0 To UBound(cols)
                dataArray(i, j) = cols(j)
            Next j
        End If
    Next i
    
    ReadUTF8CSVToArray = dataArray
End Function

' 辅助函数：给路径添加引号，兼容含空格的文件路径
Function Quote(text As String) As String
    Quote = """" & text & """"
End Function

注意：如果CSV包含带引号且含逗号的字段，需替换Split逻辑为更严谨的CSV解析规则，避免分割错误。

方法2：用QueryTables加载到临时工作表转数组 #

利用你已能使用的QueryTables，通过隐藏临时工作表中转，直接将CSV数据转为数组，处理复杂CSV更可靠。

Function LoadUTF8CSVToArrayViaQuery(filePath As String) As Variant
    Dim tempSheet As Worksheet
    Dim qt As QueryTable
    Dim dataRange As Range
    Dim dataArray As Variant
    
    ' 创建隐藏临时工作表
    Set tempSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    tempSheet.Visible = xlSheetHidden
    
    ' 配置QueryTables加载UTF-8 CSV
    Set qt = tempSheet.QueryTables.Add( _
        Connection:="TEXT;" & filePath, _
        Destination:=tempSheet.Range("A1"))
        
    With qt
        .TextFilePlatform = 65001 ' 指定UTF-8编码
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = Array(xlTextFormat) ' 避免自动转换格式
        .Refresh BackgroundQuery:=False
        .Delete ' 加载完成后清理QueryTable
    End With
    
    ' 提取数据为数组
    Set dataRange = tempSheet.UsedRange
    dataArray = dataRange.Value
    
    ' 删除临时工作表
    Application.DisplayAlerts = False
    tempSheet.Delete
    Application.DisplayAlerts = True
    
    LoadUTF8CSVToArrayViaQuery = dataArray
End Function

优化建议 #

处理大量文件时，添加以下代码提升运行效率：

Application.ScreenUpdating = False
Application.EnableEvents = False
' 批量处理文件的代码
Application.ScreenUpdating = True
Application.EnableEvents = True

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