VBA读取txt文档指定行与列的值

例如，我们可以使用下面的 VBA 代码来读取 txt 文档中第 13 行第 5 列的值，假设值的位置从第64个字符开始，长度为46个字符，数组分割符采用空格。

方法一：使用Open For Input as 语句直接读取

Open pathname For [Input |Output |Append] As [#]filenumber [Len = buffersize]

Sub ReadtxtFile()
'
' 使用Open For Input as 语句直接读取

    Dim lineIndex, columnIndex As Integer
    lineIndex = 13
    columnIndex = 5

    Dim FilePath As String
    Dim AnsiFilePath As String
    FilePath = "C:\path\to\your\file.txt"    ' 设置要读取的文本文件路径
    If UTF8TOANSI(FilePath) = False Then Exit Sub    ' 将TXT编码UTF8转换为ANSI
    AnsiFilePath = Left(FilePath, Len(FilePath) - 4) & "_ANSI.txt"

    ' 打开文本文件并逐行读取内容
    Open AnsiFilePath For Input As #1
        Dim lineNumber As Integer
        lineNumber = 0
        Do Until EOF(1)
            lineNumber = lineNumber + 1
            Line Input #1, textLine
            If lineNumber = lineIndex Then
                Value = Replace(Mid$(textLine, 64, 46), " ", "")    '从第64个字符开始截取46个字符
                Debug.Print Value    ' 在视图立即窗口中显示变量值
                Exit Do
            End If
        Loop
    Close #1
    Set fso = CreateObject("scripting.filesystemobject")
    fso.DeleteFile (AnsiFilePath)    ' 删除临时文件
End Sub

方法二：使用Open For Input as 语句+数组方式读取

Sub ReadtxtFile()
'
' 使用Open For Input as 语句+数组方式读取

    Dim lineIndex, columnIndex As Integer
    lineIndex = 13
    columnIndex = 5

    Dim filePath As String
    filePath = "C:\path\to\your\file.txt"    ' 设置要读取的文本文件路径

    ' 打开文本文件并逐行读取内容
    Open filePath For Input As #1

        Dim lineNumber As Integer
        lineNumber = 0
        Do Until EOF(1)
            lineNumber = lineNumber + 1
            Line Input #1, textLine
            If lineNumber = lineIndex Then
                '用 for Next循环将其他分隔符替换成空格
                DelimiterNew = " "
                For Each DelimiterOld In Array(";", "  ", "<==", ":", vbCr)
                    textLine = Replace(textLine, DelimiterOld, DelimiterNew)    '将不同分隔符替换成空格
                    textLine = Replace(textLine, "  ", DelimiterNew)    '将两个空格替换成一个空格
                Next DelimiterOld

                'fieldsArray = Split(textLine, vbTab)    ' 将每行按制表符分割为字段数组
                fieldsArray = Split(textLine, " ")    ' 将每行按空格分割为字段数组

                value = fieldsArray(columnIndex-1)    ' 获取指定列的值（列数要减1）
                
                ' 输出结果到 Immediate Window (Ctrl + G)
                Debug.Print value    '在视图-立即窗口中显示变量值
                
                Exit Do
            End If
        Loop
    Close #1
End Sub

方法三：使用CreateObject函数+数组方式读取

语法  CreateObject(class,[servername])

Sub ReadtxtFile()
'
' 使用CreateObject函数+数组方式读取

    Dim lineIndex, columnIndex As Integer
    lineIndex = 13
    columnIndex = 5
    
    Dim filePath As String
    filePath = "C:\path\to\your\file.txt"    ' 设置要读取的文本文件路径
    
    Set fso = CreateObject("scripting.filesystemobject")
    Dim txtFile As Object, textContent As String
    Set txtFile = fso.OpenTextFile(filePath, 1)

    Dim lineNumber As Integer
    lineNumber = 0
    
    Do While Not txtFile.AtEndOfStream
        lineNumber = lineNumber + 1
        textContent = txtFile.readline
        
        If lineNumber = lineIndex Then
            ' 用 for 循环将其他分隔符替换成空格
            DelimiterNew = " "
            For Each DelimiterOld In Array(";", "  ", "<==", ":", vbCr)
                  textContent = Replace(textContent, DelimiterOld, DelimiterNew)    ' 将不同分隔符替换成空格
                  textContent = Replace(textContent, "  ", DelimiterNew)    ' 将两个空格替换成一个空格
            Next DelimiterOld
            
            'fieldsArray = Split(textLine, vbTab)    ' 将每行按制表符分割为字段数组
            fieldsArray = Split(textContent, " ")     ' 将每行按空格分割为字段数组
            
            Value = fieldsArray(columnIndex - 1)  ' 获取指定列的值（列数要减1）

            ' 输出结果到 Immediate Window (Ctrl + G)
            Debug.Print value    '在视图-立即窗口中显示变量值

            Exit Do
        End If
    Loop
    
    txtFile.Close
    Set fso = Nothing
    Set txtFile = Nothing
End Sub

注意事项：

需要将 filePath 变量修改为你自己的 txt 文件路径，lineIndex, columnIndex变量修改为你自己的行与列。

总结：

采用方法一直接读取txt文档时，为避免出现乱码，我们需要使用UTF8TOANSI函数将UTF-8编码转换为ANSI，处理起来相对复杂一些，但方法二或方法三采用数组方式读取，则无需转换编码，处理起来相对简单。关于UTF8TOANSI函数，请参考《VBA转换TXT文档编码（UTF-8转换为ANSI）》一文。