您提供的代码工作方式如下：
1.它设置指向文件最后一个字符的指针
1.然后，它向后读取该文件，直到找到换行符
1.它返回最后一行读取的所有内容。
为了根据您的需要进行修改，我添加了一个Boolean secondRun，它让代码再次运行步骤2，从而记录倒数第二行：

Dim MyChar As String, Pointer As Long, LastLine As String
Open "MyTextFile.Txt" For Binary As #1
Pointer = LOF(1) - 2
MyChar = Chr$(32)
Dim secondRun As Boolean
Do
    ' Read character at position "Pointer" into variable "MyChar"
    Get #1, Pointer, MyChar
    If MyChar = vbCr Or MyChar = vbLf Then  ' Linebreak = line read completely
        If Not secondRun Then
            ' Run again if we've read only one line so far
            secondRun = True
            LastLine = ""
            Pointer = Pointer - 2
        Else
            Exit Do
        End If
    Else: Pointer = Pointer - 1
        ' Add character to result String
        LastLine = MyChar & LastLine
    End If
Loop
MsgBox " 2nd last line is " & LastLine

这取决于你的方法。但是如果文件真的那么大，你可能不希望Excel加载整个文件。所以，你可能会在不知道文件有多大和有多少行的情况下打开文件并逐行读取。在这种情况下，最简单的方法可能是一次在两个独立的字符串变量中存储两行。一旦你到达最后一行，你就可以退出循环。如上面的代码所示-并且不仅输出最后一行（正如代码中已经完成的那样），还输出该文件倒数第二行的内容。

Public Sub GetSecondLastRow()
Dim strSecondLastLine As String
Dim strFileToImport As String
Dim strLastLine As String
Dim intPointer As Integer
Dim lngCounter As Long

strFileToImport = ThisWorkbook.Path & IIf(InStr(1, ThisWorkbook.Path, "\") > 0, "\", "/") & "MyTextFile.txt"

intPointer = FreeFile()
Open strFileToImport For Input Access Read Lock Read As #intPointer

lngCounter = 0
Do Until EOF(lngCounter)
    strSecondLastLine = strLastLine
    Line Input #intPointer, strLastLine
    lngCounter = lngCounter + 1
Loop

Close intPointer

Debug.Print "Content of the second last row:"
Debug.Print "---------------------------------------"
Debug.Print strSecondLastLine
Debug.Print "---------------------------------------"
Debug.Print "Content of the last row:"
Debug.Print "---------------------------------------"
Debug.Print strLastLine

End Sub

另一种方法是首先查询文件的行数，然后使用ADO获取该文件中倒数第二条记录。但我怀疑这是否会更快。ADO的问题是，您会得到一个包含整个文本文件的巨大的recordset返回。这是由于在子句SELECT * from MyTextFile.txt中没有where限制。因此，整个文本文件会在之前进入内存。然后--当然--您可以检查RecordCount，并再次使用光标快进浏览所有记录，直到您碰到倒数第二行。不幸的是，ADO不支持
行编号（）超过（按@@行计数排序）。
否则，您可以先使用select count(1) from MyTextFile.txt获取行计数，然后只获取适用的行。
所以，在任何情况下，我几乎可以肯定（没有测试过）ADO的性能会低于标准，如果文本文件像你说的那么大，第一个解决方案是要走的路。如果你仍然喜欢ADO，那么这是它的代码（基于下面的SO问题/答案：Copying text from .txt file in Excel using ADO ignores first row）。

Sub ImportTextFile()

'Imports text file into Excel workbook using ADO.
'If the number of records exceeds 65536 then it splits it over more than one sheet.
Dim strFilePath As String, strFilename As String, strFullPath As String
Dim lngCounter As Long
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim oFSObj As Object

'Get a text file name
strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")

If strFullPath = "False" Then Exit Sub  'User pressed Cancel on the open file dialog

'This gives us a full path name e.g. C:\temp\folder\file.txt
'We need to split this into path and file name
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")

strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path
strFilename = oFSObj.GetFile(strFullPath).Name

'Open an ADO connection to the folder specified
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & strFilePath & ";" & _
               "Extended Properties=""text;HDR=No;FMT=Delimited"""

Set oRS = New ADODB.Recordset
'Now actually open the text file and import into Excel
oRS.Open "SELECT count(1) FROM [" & strFilename & "]", oConn, 3, 1, 1

Range("A1").CopyFromRecordset oRS

Set oRS = New ADODB.Recordset
'Now actually open the text file and import into Excel
oRS.Open "SELECT * FROM [" & strFilename & "]", oConn, 3, 1, 1

While Not oRS.EOF And Not oRS.BOF
    If oRS.AbsolutePosition = Range("A1").Value2 Then
        Range("A2").Value = oRS.Fields(0).Value
    End If
    oRS.MoveNext
Wend

oRS.Close
oConn.Close

End Sub

你可以试试这个：

Public Function GetSecondLastLine(sFileName As String, Optional sLineDelimiter As String = vbCrLf) As String

    Dim sContent    As String
    Dim aLines()    As String

    sContent = TextFromFile(sFileName)

    aLines = Split(sContent, sLineDelimiter)

    GetSecondLastLine = aLines(UBound(aLines) - 1)

End Function

Public Function TextFromFile(sFileName As String) As String

    Dim lFile As Long

    lFile = FreeFile
    Open sFileName For Input As #lFile
    TextFromFile = Input$(LOF(lFile), lFile)
    Close #lFile

End Function

如有必要，可以更改行分隔符（例如，vbLF的vbCR）

“符合要求”版本

改进了 Verzweifler 的回答，恕我直言：

• Linux兼容（换行符只能使用LF，而不能使用CR LF）
• 考虑多个打开的文件
• 使用空的、固定长度的字符串作为char（不需要赋值）

Public Function GetSecondLastLine(ByVal filePath As String) As String
    ' Variables
    Dim fileNumber As Long
    ' Use first unused file number.
    fileNumber = FreeFile
    Dim pointer As Long
    ' String of fixed length 1.
    Dim char As String * 1
    Dim secondLastLine As String
    Dim secondRun As Boolean
    
    ' Read last two lines of file.
    Open filePath For Binary As fileNumber
    ' Set pointer to last file position.
    pointer = LOF(fileNumber)
    Do
        ' Read char at position "pointer" into "char".
        Get fileNumber, pointer, char
        If char = vbCr Then
            ' Just skip CRs for Linux compat.
            pointer = pointer - 1
        ElseIf char = vbLf Then
            If Not secondRun Then
                secondRun = True
                secondLastLine = vbNullString
                pointer = pointer - 1
            Else
                Exit Do
            End If
        Else
            pointer = pointer - 1
            ' Add char to result String.
            secondLastLine = char & secondLastLine
        End If
    Loop
    Close fileNumber
    GetSecondToLastLine = secondLastLine
End Function

MsgBox " 2nd last line is " & GetSecondLastLine("MyTextFile.txt")

扩展版本

• 获取最后行数lineCount

Public Function GetLastLines(ByVal filePath As String, Optional ByVal lineCount As Long = 1) As String()
    Dim fileNumber As Long
    ' Use first unused file number.
    fileNumber = FreeFile
    Dim pointer As Long
    ' String of fixed length 1.
    Dim char As String * 1
    Dim currentLineNumber As Long
    currentLineNumber = 0
    Dim lastLines() As String
    ReDim lastLines(0 To lineCount - 1)

    ' Open file.
    Open filePath For Binary As fileNumber
    ' Set pointer to last file position.
    pointer = LOF(fileNumber)
    Do
        ' Read char at position "pointer" into "char".
        Get fileNumber, pointer, char
        If char = vbCr Then
            ' Just skip CRs for Linux compat.
            pointer = pointer - 1
        ElseIf char = vbLf Then
            ' Read count last lines of file.
            If currentLineNumber < lineCount - 1 Then
                currentLineNumber = currentLineNumber + 1
                pointer = pointer - 1
            Else
                Exit Do
            End If
        Else
            pointer = pointer - 1
            ' Add char to result string.
            lastLines(currentLineNumber) = char & lastLines(currentLineNumber)
        End If
    Loop
    Close fileNumber
    GetLastLines = lastLines
End Function

Dim line As Variant
For Each line In GetLastLines("MyTextFile.txt", 2)
    Debug.Print line
Next