Excel VBA: Open a Text File for Reading, Step by Step

VBA & macros · Updated November 19, 2026

To open a text file for reading in Excel VBA, assign a free file number, use the Open statement with For Input, read the file, and close it when finished. For example, Open filePath For Input As #fileNumber opens an existing file for read-only access; a loop using Line Input # can then process it one line at a time.

What Open For Input does

VBA’s built-in file statements provide a straightforward way to read plain-text files such as .txt and many .csv files. The statement Open ... For Input is intended for reading an existing file. It does not import the contents into worksheet cells automatically: your macro decides what to do with the text after reading it, such as print it in the Immediate window, parse values, or place selected data in a range.

The basic pattern has four parts: get an unused file number with FreeFile, open the path, read until the end, and close the file. Closing matters even when a macro encounters an error, because an open file can remain unavailable to other operations until it is released. The example below includes a cleanup handler for that reason.

  • FreeFile returns an available number for the file operation.
  • Open ... For Input opens the named file for reading.
  • Line Input # reads one line at a time.
  • Close releases the file when the macro is done.

A minimal line-by-line example

Change the sample path to the location of your file. This macro prints each line in Excel’s Immediate window, which you can display in the Visual Basic Editor by pressing Ctrl+G on Windows. On Mac, use the Visual Basic Editor’s View menu to show the Immediate window; the exact menu presentation can vary by Office version.

Sub ReadTextFile()
    Dim filePath As String
    Dim fileNumber As Integer
    Dim lineText As String
    Dim errorMessage As String

    On Error GoTo ReadError

    ' Replace this sample path with the text file to read.
    filePath = "C:\Data\notes.txt"
    fileNumber = FreeFile

    Open filePath For Input As #fileNumber

    Do While Not EOF(fileNumber)
        Line Input #fileNumber, lineText
        Debug.Print lineText
    Loop

    Close #fileNumber
    Exit Sub

ReadError:
    errorMessage = Err.Description
    On Error Resume Next
    If fileNumber <> 0 Then Close #fileNumber
    On Error GoTo 0
    MsgBox "Could not read the file: " & errorMessage, vbExclamation
End Sub

EOF(fileNumber) becomes true when the read reaches the end of the file. The loop therefore handles files with different numbers of lines without needing to know their length in advance. A blank line is still a line, so the loop can return an empty string for it. The error handler captures the original error description, attempts to close the file, and then displays a message instead of leaving the file open.

The path is a VBA string. Windows paths use backslashes as shown; VBA does not require doubling them. For a Mac file, use a valid Mac path, or let the user choose the file instead of hard-coding a location. A shared workbook path may also depend on whether the user is signed in and has access to the relevant drive or network location.

Let the user choose a file

A fixed path is convenient for a repeatable process, but a file picker is less error-prone when the file changes or users store it in different folders. Excel’s GetOpenFilename method displays a selection dialog and returns the chosen path. It does not itself open the file; pass the returned path to the same reading routine. If the user cancels, the method returns Boolean False, so check for cancellation before converting the result to a string.

Sub ChooseTextFile()
    Dim selectedPath As Variant

    ' Ask the user to select a text file.
    selectedPath = Application.GetOpenFilename( _
        "Text Files (*.txt), *.txt", , "Choose a text file")

    If VarType(selectedPath) = vbBoolean Then Exit Sub

    MsgBox "Selected file: " & CStr(selectedPath)
End Sub

This example only displays the selected path; it does not read the contents. To use it in the first example, assign CStr(selectedPath) to filePath after the cancellation check, then run the open-and-read section. The dialog is available in desktop Excel, although the appearance and file locations differ between Windows and Mac. Excel for the web cannot run VBA macros.

Choose the right way to read the contents

Line Input # is useful when the file is naturally organized as one record per line, such as a log, list, or simple export. It reads a complete line into a string and removes the line-ending characters. It does not split comma-separated values into fields or interpret them as numbers and dates. If a line contains comma-separated data, your code still needs to parse the fields, and quoted commas or other CSV rules make a simple split unreliable.

For a small file that you need to process sequentially, line-by-line reading keeps the code simple and avoids loading the whole file into one variable. For a very large file, it is also generally preferable to process each line and discard it rather than retain every line in memory. If the task requires examining or transforming the entire contents as one string, VBA also has file-reading approaches based on reading a specified number of characters, but those require more care around file size and encoding.

If the real goal is to put delimited data into cells, consider using Excel’s import tools rather than writing a parser. In Microsoft 365 for Windows, go to Data > Get Data > From File > From Text/CSV to preview and import a text or CSV file. The import interface can identify delimiters and lets you inspect the result before loading it. That is often safer than assuming every comma or tab marks a field boundary.

Paths, permissions, and common errors

A common failure is an incorrect path: a typo, moved file, missing drive, or unavailable network folder can cause the open statement to fail. A path that works for one person may not exist on another person’s computer. For shared workbooks, prefer a location users can actually access, and consider a file picker or a clearly configured folder instead of embedding a personal path in the code.

Other problems include access restrictions, a file that has been removed, or a file in use in a way that prevents the requested access. The error handler in the sample reports the underlying VBA error description, which helps distinguish a missing file from other problems. Avoid ignoring errors silently: doing so can make a macro appear to succeed when it read nothing.

Use FreeFile rather than choosing a file number such as 1 yourself. A number might already be in use elsewhere in the project. Also pair every successful open with a close, including in error-handling code. If the macro will write to a worksheet as well as read a file, remember that Excel’s Undo command cannot reverse changes made by VBA. Save a copy of the workbook before running a macro, especially while developing or adapting code.

Text encoding and special characters

Text files store bytes, and those bytes need an encoding to represent characters. VBA’s traditional Open ... For Input statements use the system’s text conventions and are not a dependable way to select any arbitrary encoding. A file containing accented letters, non-Latin scripts, or symbols may display incorrectly if its encoding does not match what the reading method expects. Plain English text may hide this issue, so test with representative data.

If the file is UTF-8 or another encoding and characters matter, identify its encoding before choosing a method. Excel’s Text/CSV import preview can be useful for an import workflow because it offers controls for interpreting the source. For automated code that must explicitly control encoding, developers commonly use a stream-based method, but its implementation and platform support need to be checked for the target version of Office. Do not assume that changing a filename extension or opening the file in Notepad changes its encoding.

Line endings are another detail. Text files may use different conventions to mark the end of each line, but Line Input # is designed to read text records rather than leave the line break in the returned string. If you need to preserve exact bytes, line endings, or a byte-order mark, a text-line routine is not the right tool; use a method designed for byte-level or explicitly encoded reading.

Reading a text file is not the same as opening a workbook

Use VBA’s Open ... For Input when your macro needs to read plain text as text. It does not create a new Excel workbook and does not apply the spreadsheet import rules for CSV. By contrast, Workbooks.Open opens a workbook file, and Excel can also open many CSV files as worksheets. That may be appropriate when a person wants to view and work with the rows, but it is a different workflow from reading raw lines in VBA.

This distinction matters with delimiters and data types. A CSV value that looks like a date or a long number might be interpreted or reformatted when opened as spreadsheet data. Reading with Line Input # gives the macro a string for each line, leaving interpretation to your code. Conversely, if you want Excel to separate columns and make the data visible in a sheet, using the import or workbook-opening route is usually more convenient than manually splitting every line.

On Windows, VBA runs in desktop Excel with macros enabled and a workbook saved in a macro-capable format such as .xlsm. Excel for the web can display a macro-enabled workbook but does not execute its VBA. Macros can also be blocked by organizational policy or Trust Center settings, so only enable code from a source you trust.

Frequently Asked Questions

How do I read a text file line by line in Excel VBA?
Get a number with FreeFile, open the path with Open filePath For Input As #fileNumber, loop while Not EOF(fileNumber), read with Line Input #fileNumber, lineText, and close the file when done.

Does Open For Input change or delete the text file?
No. For Input opens the file for reading, not writing. Use a separate, deliberate method if you need to modify or create a file, and take care not to overwrite source data unintentionally.

How can I open a text file without hard-coding its path?
Use Application.GetOpenFilename to let the user choose a file. Check whether the returned value is Boolean False, which indicates cancellation, before using it as a path.

Why do accented characters look wrong after reading?
The file’s encoding may not match the assumptions of VBA’s traditional text input statement. Confirm the file encoding and use an import or stream-based method that supports it, rather than treating the issue as a path problem.

Can I use this VBA code in Excel for the web?
No. Excel for the web does not run VBA macros. Run the macro in desktop Excel for Windows or Mac, subject to macro availability and platform-specific path differences.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.