Excel VBA读取多语言TXT文件至字符串数组的字符集兼容问题

我用Excel VBA读取多个TXT文件，将文件名与内容存储到数组中，但由于文件包含多种语言，字符集处理遇到困难。想知道是否存在支持所有语言的字符集，或者该如何解决这个问题？

原代码 #

Function create_Txt_Content_Array(file_count As Integer, path As String, Optional strType As String) As String()

Dim createArray() As String
Dim file As Variant
Dim read_file As Integer
Dim absolut_path As String
Dim i, j As Integer
Dim text_content As String
Dim objStream

Set objStream = CreateObject("ADODB.Stream")
objStream.Charset = "utf-8"

ReDim createArray(file_count - 1, 1)

    If Right(path, 1) <> "\" Then path = path & "\" 
    file = Dir(path & strType)
    absolut_path = path & file
    j = 0
    While (file <> "")
        objStream.Open
        objStream.LoadFromFile (absolut_path)
        text_content = objStream.ReadText()
        objStream.Close
        createArray(j, 0) = file
        createArray(j, 1) = text_content
        Debug.Print (text_content)
        i = i + 1
        j = j + 1
        file = Dir
        absolut_path = path & file
    Wend
    
    Set objStream = Nothing

End Function

遇到的问题 #

• 葡萄牙语文件：读取正常
• 英语文件：读取正常
• 印地语文件：无法正常读取
• 后续还需处理韩语、日语等其他语言的文件

解决方案 #

关于“通用字符集” #

不存在能支持所有语言的单一字符集，但UTF-8是目前覆盖绝大多数语言（包括印地语、韩语、日语等）的通用编码标准。问题出在：如果TXT文件本身不是UTF-8编码（比如是系统ANSI编码、UTF-16编码），硬套UTF-8读取就会出现乱码。

自动识别文件编码的解决思路 #

要正确读取多语言文件，核心是先判断文件的实际编码，再匹配对应的字符集设置。常见的编码判断方法是检测文件开头的字节顺序标记（BOM）：

• UTF-8 BOM：字节序列 EF BB BF
• UTF-16LE BOM：字节序列 FF FE
• UTF-16BE BOM：字节序列 FE FF

对于无BOM的文件，优先尝试UTF-8读取（这是多语言文件的主流编码），如果仍乱码，可再尝试系统默认ANSI编码（但ANSI仅支持单语言环境）。

改进后的VBA代码 #

Function create_Txt_Content_Array(file_count As Integer, path As String, Optional strType As String = "*.txt") As String()
    Dim createArray() As String
    Dim file As Variant
    Dim absolut_path As String
    Dim j As Integer
    Dim text_content As String
    Dim objStream As Object
    Dim fso As Object, ts As Object
    Dim bomBytes() As Byte
    Dim encoding As String

    Set objStream = CreateObject("ADODB.Stream")
    Set fso = CreateObject("Scripting.FileSystemObject")

    ReDim createArray(file_count - 1, 1)

    If Right(path, 1) <> "\" Then path = path & "\"
    file = Dir(path & strType)
    j = 0

    While file <> ""
        absolut_path = path & file
        ' 读取文件前3字节检测BOM
        Set ts = fso.OpenTextFile(absolut_path, 1, False, -1) ' TriStateBinary模式读取字节
        If Not ts.AtEndOfStream Then
            ReDim bomBytes(2)
            ts.Read 3, bomBytes
        End If
        ts.Close

        ' 根据BOM判断编码
        encoding = "utf-8" ' 默认使用UTF-8
        If UBound(bomBytes) >= 2 Then
            If bomBytes(0) = &HEF And bomBytes(1) = &HBB And bomBytes(2) = &HBF Then
                encoding = "utf-8" ' UTF-8带BOM
            ElseIf bomBytes(0) = &HFF And bomBytes(1) = &HFE Then
                encoding = "utf-16le" ' UTF-16小端编码
            ElseIf bomBytes(0) = &HFE And bomBytes(1) = &HFF Then
                encoding = "utf-16be" ' UTF-16大端编码
            End If
        End If

        ' 读取文件内容
        objStream.Open
        objStream.Type = 2 ' adTypeText
        objStream.Charset = encoding
        objStream.LoadFromFile absolut_path
        text_content = objStream.ReadText()
        objStream.Close

        ' 存入数组
        createArray(j, 0) = file
        createArray(j, 1) = text_content
        Debug.Print text_content

        j = j + 1
        file = Dir
    Wend

    Set objStream = Nothing
    Set fso = Nothing
    create_Txt_Content_Array = createArray
End Function

代码说明 #

1. 先通过文件开头的3字节检测BOM，自动匹配对应编码；
2. 无BOM的文件默认按UTF-8读取，保证多语言兼容性；
3. 支持UTF-8、UTF-16LE、UTF-16BE三种常见的多语言编码；
4. 如果遇到老版本的ANSI编码文件，可以在编码判断逻辑中增加对应编码（如Windows-1252、GB2312）的尝试分支。

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