How to Get Last Line of a Text File in VBA

Target text file:

Methode1: (loop until last line)

1 2 3 4 5 6 7 8 9 10

Sub Sample1() Dim buf As String Const Target As String = "D:\ExcelPaidjo\LastLine\text.txt" Open Target For Input As #1 Do Until EOF(1) Line Input #1, buf Loop Close #1 MsgBox "Last Line: " & buf End Sub

Methode2: (Read all line at once, then split become array)

1 2 3 4 5 6 7 8 9 10 11 12 13

Sub Sample2() Dim buf As String, FSO As Object Dim LastRow As Long Const Target As String = "D:\ExcelPaidjo\LastLine\text.txt" Set FSO = CreateObject("Scripting.FileSystemObject") With FSO.OpenTextFile(Target, 1) buf = .ReadAll .Close End With Set FSO = Nothing LastRow = UBound(Split(buf, vbCrLf)) - 1 MsgBox Split(buf, vbCrLf)(LastRow) End Sub

Download File