VBA导入CSV文件时如何设置UTF-8编码？

原代码使用Open和Line Input语句读取文件，这类方法依赖系统默认编码，无法正确识别UTF-8编码的内容，导致非ASCII字符（如中文）乱码。以下提供两种可靠的解决方案：

方法一：使用ADODB.Stream读取UTF-8内容 #

通过ADODB.Stream组件直接按UTF-8编码读取文件内容，再拆分处理数据，兼容带BOM和不带BOM的UTF-8文件：

Sub ImportCSV_UTF8()
    Dim dialogBox As FileDialog
    Dim selectedFile As String
    Set dialogBox = Application.FileDialog(msoFileDialogFilePicker)

    With dialogBox
        .Filters.Add "CSV", "*.CSV", 1
        .AllowMultiSelect = False
        If .Show = True Then
            selectedFile = .SelectedItems(1)
        End If
    End With

    If selectedFile = "" Then Exit Sub

    ' 初始化ADODB.Stream读取UTF-8文件
    Dim stream As Object
    Set stream = CreateObject("ADODB.Stream")
    With stream
        .Charset = "UTF-8"
        .Open
        .LoadFromFile selectedFile
        Dim fileContent As String
        fileContent = .ReadText
        .Close
    End With

    ' 按行拆分文件内容
    Dim lines As Variant
    lines = Split(fileContent, vbCrLf)

    Dim rowNumber As Long
    Dim lineItems As Variant
    Dim iteration As Integer
    rowNumber = 1

    ' 逐行写入到指定区域
    For Each line In lines
        If Trim(line) <> "" Then ' 跳过空行
            lineItems = Split(line, ";")
            For iteration = 0 To UBound(lineItems)
                If iteration <= 14 Then ' 限制到原代码的15列范围
                    Range("ImportRange").Cells(rowNumber, iteration + 1) = lineItems(iteration)
                End If
            Next
            rowNumber = rowNumber + 1
        End If
    Next

    Set stream = Nothing
End Sub

方法二：用Workbooks.Open直接导入UTF-8 CSV #

利用Excel内置的文件打开功能，指定UTF-8编码参数，代码更简洁，适合无需逐行处理的场景：

Sub ImportCSV_UTF8_Alternative()
    Dim dialogBox As FileDialog
    Dim selectedFile As String
    Set dialogBox = Application.FileDialog(msoFileDialogFilePicker)

    With dialogBox
        .Filters.Add "CSV", "*.CSV", 1
        .AllowMultiSelect = False
        If .Show = True Then
            selectedFile = .SelectedItems(1)
        End If
    End With

    If selectedFile = "" Then Exit Sub

    ' 以UTF-8编码打开CSV文件
    Dim wbCSV As Workbook
    Set wbCSV = Workbooks.Open(Filename:=selectedFile, _
                               Format:=6, ' 对应分号分隔的CSV格式
                               Delimiter:=";", _
                               Local:=False, _
                               Origin:=65001) ' 65001是UTF-8的编码标识

    ' 将CSV数据复制到目标区域
    wbCSV.Sheets(1).UsedRange.Copy ThisWorkbook.Range("ImportRange").Cells(1, 1)

    ' 关闭CSV文件，不保存修改
    wbCSV.Close SaveChanges:=False
    Set wbCSV = Nothing
End Sub

注意事项 #

• 方法一中使用的ADODB.Stream组件是Windows系统自带的，无需额外安装。
• 方法二中，Format:=6对应分号分隔的CSV，如果你的CSV是逗号分隔，将参数改为Format:=2即可。
• 两种方法都保留了原代码的文件选择逻辑，同时修复了编码问题，且避免了原代码中固定列数可能导致的数组越界错误。

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