如何修改Excel VBA脚本实现批量导入UTF-8编码的日文文本文件？

核心问题说明 #

你当前使用的Workbooks.Open方法打开文本文件时，会默认调用系统本地编码读取UTF-8编码的日文文件，因此出现字符乱码。指定UTF-8编码不需要额外配置.TextFilePlatform参数，直接在打开文本文件时通过OpenText方法的Origin参数传入65001即可实现UTF-8编码读取。

修改后完整VBA脚本 #

Sub CombineTextFiles()
'updateby Extendoffice 调整UTF-8编码支持
    Dim xFilesToOpen As Variant
    Dim I As Integer
    Dim xWb As Workbook
    Dim xTempWb As Workbook
    Dim xDelimiter As String
    Dim xScreen As Boolean
    On Error GoTo ErrHandler
    xScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False
    xDelimiter = "|"
    xFilesToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Kutools for Excel", , True)
    If TypeName(xFilesToOpen) = "Boolean" Then
        MsgBox "No files were selected", , "Kutools for Excel"
        GoTo ExitHandler
    End If
    I = 1
    ' 替换原有Open方法为OpenText，指定UTF-8编码（Origin:=65001）
    Workbooks.OpenText Filename:=xFilesToOpen(I), Origin:=65001, Delimiter:=xDelimiter, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote
    Set xTempWb = ActiveWorkbook
    xTempWb.Sheets(1).Copy
    Set xWb = Application.ActiveWorkbook
    xTempWb.Close False
    Do While I < UBound(xFilesToOpen)
        I = I + 1
        ' 循环内同样替换为带编码指定的OpenText方法
        Workbooks.OpenText Filename:=xFilesToOpen(I), Origin:=65001, Delimiter:=xDelimiter, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote
        Set xTempWb = ActiveWorkbook
        With xWb
            xTempWb.Sheets(1).Move after:=.Sheets(.Sheets.Count)
        End With
    Loop
ExitHandler:
    Application.ScreenUpdating = xScreen
    Set xWb = Nothing
    Set xTempWb = Nothing
    Exit Sub
ErrHandler:
    MsgBox Err.Description, , "Kutools for Excel"
    Resume ExitHandler
End Sub

调整说明 #

• 所有Workbooks.Open打开txt文件的逻辑全部替换为Workbooks.OpenText，新增Origin:=65001参数指定UTF-8编码，从读取阶段就避免日文乱码
• 直接在OpenText阶段指定分隔符，省去后续逐列拆分的TextToColumns步骤，运行效率更高
• 如果你需要保留原有TextToColumns的拆分逻辑，只需把Workbooks.Open(xFilesToOpen(I))替换为以下代码即可，其余原有逻辑不用改动：

Workbooks.OpenText Filename:=xFilesToOpen(I), Origin:=65001
Set xTempWb = ActiveWorkbook

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