[Excel VBA] How to Read Text Files Using FSO (ReadAll / ReadLine)

There are many situations where you want to read external text files (.txt), such as CSV files or system logs, into Excel to use as data.

By using the FileSystemObject (FSO), you can easily open text files and read their contents with VBA. There are two main approaches to reading files: .ReadAll (reading the entire file at once) and .ReadLine (reading line by line).

This article explains both methods with code examples.

[Preparation] Reference Settings

To use FSO smoothly, I recommend checking “Microsoft Scripting Runtime” in the VBA editor.

1. Go to Tools > References.
2. Check Microsoft Scripting Runtime.

Method 1: Read the Entire File at Once (.ReadAll)

This method reads the entire content of the file into a single large string at once. Since the code is simple, it is suitable for handling relatively small files (up to a few MB).

Completed VBA Code

' Reference: Microsoft Scripting Runtime
Sub ReadTextFile_AllAtOnce()

    ' Declare variables
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Dim filePath As String
    Dim fileContent As String
    Dim linesArray As Variant
    Dim i As Long
    
    ' Path of the text file to read
    filePath = ThisWorkbook.Path & "\SampleData.txt"
    
    If Not fso.FileExists(filePath) Then
        MsgBox "The specified file was not found.", vbCritical
        Exit Sub
    End If
    
    '--- 1. Open file in reading mode ---
    Set ts = fso.OpenTextFile(filePath, ForReading)
    
    '--- 2. Read the entire content at once with .ReadAll ---
    fileContent = ts.ReadAll
    
    '--- 3. Close the file ---
    ts.Close
    
    '--- 4. Split the content by line breaks and store in an array ---
    linesArray = Split(fileContent, vbCrLf)
    
    '--- 5. Write the array content to cells ---
    For i = 0 To UBound(linesArray)
        ActiveSheet.Cells(i + 1, "A").Value = linesArray(i)
    Next i
    
    MsgBox "File reading and expansion complete."

End Sub

Key Points of the Code

• fso.OpenTextFile(filePath, ForReading): Opens the file in “ForReading” (read-only) mode.
• ts.ReadAll: The ReadAll method of the TextStream object (ts) returns the entire file content as a single concatenated string.
• Split(fileContent, vbCrLf): The Split function splits the large string using the line break code (vbCrLf) as a delimiter, converting it into an array where each element is a line.

Method 2: Read Line by Line with a Loop (.ReadLine)

When handling huge files (e.g., several GB), .ReadAll might consume too much memory. In such cases, using the .ReadLine method to read one line at a time is more memory-friendly and ensures stable processing.

Completed VBA Code

' Reference: Microsoft Scripting Runtime
Sub ReadTextFile_LineByLine()

    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Dim filePath As String
    Dim lineBuffer As String
    Dim rowCounter As Long
    
    filePath = ThisWorkbook.Path & "\SampleData.txt"
    If Not fso.FileExists(filePath) Then Exit Sub
    
    Set ts = fso.OpenTextFile(filePath, ForReading)
    rowCounter = 1

    '--- Loop until the end of the file (AtEndOfStream) ---
    Do Until ts.AtEndOfStream
        ' Read only one line with .ReadLine
        lineBuffer = ts.ReadLine
        
        ' Write the read line to the cell
        ActiveSheet.Cells(rowCounter, "C").Value = lineBuffer
        
        rowCounter = rowCounter + 1
    Loop
    
    ts.Close
    MsgBox "Line-by-line reading complete."

End Sub

Key Points of the Code

• Do Until ts.AtEndOfStream: The .AtEndOfStream property of TextStream returns True when the end of the file is reached. This is used to create a loop that runs “until the end of the file.”
• ts.ReadLine: Reads only one line from the file and advances the file pointer to the start of the next line. Repeating this in a loop allows you to process the file line by line.

Summary

In conclusion, for standard-sized text files, combining .ReadAll and Split is easy and convenient. Consider using loop processing with .ReadLine only when you know the file is extremely large.