VBA Read Text File in Excel — Line Input vs Input #, and the EOF Loop That Doesn't Lose Rows

TL;DR — Reading a text file has three tools. Line Input # reads one raw line into a string (you split it yourself). Input # parses delimited fields straight into variables (only safe on files that Write # produced). Input(LOF(f), #f) reads the whole file at once. Loop with Do Until EOF(f) — check EOF before each read, or you run past the end and hit error 62.

Dim n As Integer, line As String
n = FreeFile
Open "C:\data.txt" For Input As #n
Do Until EOF(n)                 ' check for end BEFORE reading
    Line Input #n, line         ' one raw line into a string
    Debug.Print line
Loop
Close #n

Once a file is open on a channel, reading it back looks trivial — until the columns come out scrambled or the loop drops the last row. Both problems come from picking the wrong reader. VBA gives you three, and they are not interchangeable: one hands you the raw line, one tries to parse it for you, and one reads everything at once. The craft is matching the reader to how the file was written — and knowing that Input # and Write # are a matched pair, as are Line Input # and Print #.

What you'll learn

The mental model — three readers, and picking wrong scrambles your data
Line Input # — the raw line you split yourself (the safe default)
Input # — the parser that is the twin of Write # (and chokes on plain CSV)
The Do Until EOF() loop, and the off-by-one that raises error 62
Input(LOF(f), #f) — reading the whole file into one string
When a CSV is better opened as a workbook than parsed by hand

The mental model: three readers, one that fits your file

The insight that prevents most read bugs: the reader must match how the file was written.

Line Input # reads one line as a raw string, newline stripped. It does no parsing — you decide how to split it. Predictable, and it never misinterprets your data.
Input # reads a parsed list of values straight into variables, using the same quotes-and-commas rules that Write # writes. It is the exact twin of Write #.
Input(LOF(f), #f) (the function, not the statement) reads the entire file into a single string in one call.

So the pairing rule falls out immediately: a file written with Write # is read with Input #; a file written with Print # (or by any other program) is read with Line Input # and split by you. Cross the pairs — feed a plain CSV to Input # — and the fields land in the wrong variables.

Line Input #: the raw line you split yourself

Line Input # is the workhorse and the safe default. It reads exactly one line into a string, and what you do next is up to you:

Dim parts() As String
Line Input #n, line
parts = Split(line, ",")        ' YOU choose the delimiter

Because it never parses, it never guesses wrong. The one thing to watch is that a naive Split(line, ",") also splits commas that sit inside a quoted field ("Rome, Italy" becomes two parts). If your data has quoted commas, either parse the quotes properly or — usually simpler — let Excel do it, covered below.

Input #: the twin of Write # (and why it wrecks a plain CSV)

Input # reads parsed values directly into typed variables:

Dim name As String, city As String, age As Long
Input #n, name, city, age       ' expects "Ann","Rome",42  (Write # format)

On a file that Write # produced, this is perfect and fast — the quotes tell Input # where each string starts and stops. On a plain CSV (Ann,Rome,42) it usually still works, but it breaks the moment a field contains an unquoted comma, an apostrophe, or a stray quotation mark: fields shift, a " starts a string that never closes, and the rest of the row lands in the wrong variables. That is why Input # is only safe on files written by Write #. For anything else, Line Input # plus your own split is the honest choice.

The EOF loop and the off-by-one that raises error 62

You do not know how many lines a file has, so you read until the end — and the end test is where the classic bug lives. Check EOF before each read:

Do Until EOF(n)          ' correct: test, THEN read
    Line Input #n, line
    ' ... process line ...
Loop

The broken shape reads first and tests later, so it attempts one read past the last line and raises error 62 Input past end of file:

Do While Not EOF(n)
    ' ... process ...
    Line Input #n, line   ' on the final pass this reads past the end -> error 62
Loop

EOF(n) returns True once the last line has been read. Put the EOF check at the top of the loop so it fires before the read that would overrun. This single ordering fixes the "my loop drops or duplicates the last row" bug.

Input(LOF(f), #f): reading the whole file at once

When you want the entire file as one string — to search it, count something, or hand it to a parser — read it in a single call:

Dim whole As String
whole = Input(LOF(n), #n)        ' LOF(n) = length of the file in bytes

LOF returns the file's length, and Input(length, #n) reads that many characters. It is the fastest way to load a small-to-medium file, at the cost of holding the whole thing in memory — fine for a few megabytes, wasteful for a giant log you only need to scan line by line. For that, stay with the Line Input # loop.

When to skip parsing and open the workbook instead

If the file is really a spreadsheet — a .csv you want as rows and columns, with quoted commas handled correctly — hand-parsing text is the hard way. Workbooks.Open reads a CSV into a real sheet, handles the quoting rules for you, and lets you read Cells(r, c) directly:

Dim wb As Workbook
Set wb = Workbooks.Open("C:\data.csv")   ' Excel parses the CSV for you

Reserve the built-in text readers for genuine text work — logs, fixed-width files, config, or VBA-to-VBA round trips with Write #/Input #. For tabular data destined for cells, opening the workbook is simpler and more correct. Note that built-in reading is ANSI, so a UTF-8 file with accented characters needs ADODB.Stream or the FileSystemObject to read correctly.

The honest verdict: Line Input by default, Input # only for Write # files

Three readers, and the choice is not about speed — it is about matching the writer:

Line Input # is the default → it hands you the raw line and never misreads; you split it, so you stay in control.
Input # only for Write # files → it is the twin of Write # and scrambles on a plain CSV with unquoted commas or stray quotes.
Check EOF at the top of the loop → read-then-test overruns the last line and raises error 62.
Tabular data → open the workbook → let Excel handle CSV quoting instead of parsing it by hand.

The clearest sign you chose the wrong reader is columns landing in the wrong variables. When that happens, you fed Input # a file it did not write; switch to Line Input # and split it yourself.

How ExcelMaster helps

Reading a file back means matching the reader to the writer — Input # for Write # files, Line Input # for everything else — putting the EOF check before the read so the last row is not lost, and knowing when opening the CSV as a workbook beats parsing text by hand. That is a lot of judgment for "read a file."

ExcelMaster writes the reader that matches your file. Describe the job — "import this log into a sheet," or "read each line and pull out the error rows" — and it produces the Do Until EOF loop with Line Input #, the correct split, and a Close, or opens the CSV as a workbook when that is the cleaner path — anchored to ThisWorkbook.Path. You describe the data you need; it writes the code that reads every row exactly once.

Frequently asked questions

How do I read a text file line by line in VBA?

Open it For Input, then loop with Do Until EOF(n) and Line Input #n, line to read one raw line each pass, and Close #n when done. Take the channel number from FreeFile. Put the EOF check at the top of the loop, before the read, so the loop does not attempt a read past the last line and raise error 62.

What is the difference between Line Input # and Input # in VBA?

Line Input # reads one raw line into a string and leaves parsing to you, so it never misreads your data. Input # parses delimited fields straight into variables using the quotes-and-commas rules that Write # writes, so it is only reliable on files that Write # produced. For a plain CSV, use Line Input # and split the line yourself.

Why does my VBA read loop skip or duplicate the last line?

Because the EOF check is in the wrong place. If you read first and test EOF afterwards, the loop either overruns the last line — raising error 62 Input past end of file — or mishandles it. Use Do Until EOF(n) at the top of the loop so the end-of-file test runs before each Line Input #.

How do I read a whole text file into one string in VBA?

Use the Input function with LOF: whole = Input(LOF(n), #n) after opening the file For Input. LOF returns the file length in bytes and Input(length, #n) reads that many characters in one call. It is the fastest way to load a small-to-medium file, but it holds the entire file in memory, so use the Line Input # loop for very large files.

How do I read a CSV file in VBA?

If the CSV is tabular data destined for cells, the simplest correct way is Workbooks.Open, which parses the quoting rules and gives you a real sheet to read with Cells(r, c). If you must parse text directly, use Line Input # and split each line, handling quoted commas yourself — and reach for ADODB.Stream or the FileSystemObject if the file is UTF-8, since built-in reading is ANSI.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-25.