[Excel VBA] How to Read Specific Lines from a Text File (EUC-JP Support)

There are many cases where you need to extract only specific lines from large log files or text data. If a file is too large, reading the entire content can negatively affect performance. In such cases, reading only the necessary part by specifying the line number is effective.

This article introduces how to use Excel VBA and ADODB.Stream to read a specific number of lines starting from an arbitrary position in an EUC-JP encoded text file.

Scenario

• Target File: systemlog_euc.txt (Saved in EUC-JP format).
• Goal: Read 3 lines starting from line 10 and display them in an Excel sheet.
• Benefit: Efficiently extract only the necessary parts without reading the entire file.

VBA Code: Reading Specific Lines

This code skips the beginning of the file and reads only the specified number of lines.

Sub ReadPartialTextLines()
    Dim startLine As Long: startLine = 10
    Dim numberOfLines As Long: numberOfLines = 3
    Dim currentLine As Long
    Dim stream As Object
    Dim i As Long
    
    Set stream = CreateObject("ADODB.Stream")
    With stream
        .Charset = "EUC-JP"
        .Type = 2 ' Text mode
        .LineSeparator = 10 ' Line Separator LF
        .Open
        .LoadFromFile ThisWorkbook.Path & "\systemlog_euc.txt"
        
        ' Skip lines
        For currentLine = 1 To startLine - 1
            If .EOS Then Exit For
            .SkipLine
        Next
        
        ' Read specified number of lines
        For i = 1 To numberOfLines
            If .EOS Then Exit For
            ThisWorkbook.Worksheets(1).Cells(i, 1).Value = .ReadText(-2)
        Next
        
        .Close
    End With
End Sub

Explanation of Key Properties

Execution Result (Example)

The content will be output to Column A as follows:

• Log message line 10
• Log message line 11
• Log message line 12

Important Notes

• File Check: If the file does not exist, an error will occur. It is safer to add code to check if the file exists before running the stream logic.
• Line Breaks: If your file uses Windows-style line breaks (CRLF), change the LineSeparator to -1.
• Encoding: If you need to read UTF-8 or Shift-JIS files, simply change the string in .Charset (e.g., "UTF-8").

Application Points

• Filtering: You can add If statements inside the reading loop to filter content based on specific keywords.
• CSV Parsing: By splitting the read line using a comma or tab delimiter (using the Split function), you can easily adapt this code to handle CSV data.

Summary

By using ADODB.Stream in Excel VBA, you can pinpoint and extract only the necessary lines from huge text files.

This method is highly recommended when building systems to efficiently read business logs, data reports, or system integration files.