Read a Text File with Excel VBA: Methods and Examples

Yes. In Excel for Microsoft 365 on Windows, VBA can read a plain-text file with the Open and Line Input # statements, then place each line in a worksheet. For UTF-8 text, use an ADODB.Stream instead; the examples below show both approaches and explain when to choose each.

Choose a method for the file

The right method depends mainly on the file’s encoding and structure. For a simple text file that contains one record per line, VBA’s built-in file statements are compact and need no additional reference. They are a good starting point for log files, plain lists, and other files that use the computer’s usual text encoding.

Use a stream when the file is UTF-8 or when you need to read the entire file into one string. This is useful for accented characters and other text that may not survive a read using the computer’s default code page. If the file is actually a CSV or tab-delimited table, you may want Excel’s text-import features rather than treating each line as one worksheet value.

One line per cell: use Open ... For Input with Line Input #.
UTF-8 or whole-file text: use ADODB.Stream and set its character set explicitly.
Delimited columns: import or parse the data with the delimiter in mind instead of assuming each line is a complete record.

Read a file one line at a time

This macro asks you to choose a text file and writes its lines to column A of a worksheet named Sheet1. Create or open a macro-enabled workbook, press Alt+F11 on Windows, choose Insert > Module in the Visual Basic Editor, and paste the code into the module. Change the worksheet name if your workbook uses another one.

Sub ImportTextLines()
    Dim filePath As Variant
    Dim fileNo As Integer
    Dim lineText As String
    Dim rowNum As Long
    Dim errorText As String

    filePath = Application.GetOpenFilename("Text Files (*.txt),*.txt")
    If VarType(filePath) = vbBoolean Then Exit Sub

    fileNo = FreeFile
    On Error GoTo ReadError
    Open CStr(filePath) For Input As #fileNo

    rowNum = 1
    Do While Not EOF(fileNo)
        Line Input #fileNo, lineText
        ThisWorkbook.Worksheets("Sheet1").Cells(rowNum, 1).Value = lineText
        rowNum = rowNum + 1
    Loop

    Close #fileNo
    Exit Sub

ReadError:
    errorText = Err.Description
    On Error Resume Next
    Close #fileNo
    MsgBox "Could not read the file: " & errorText, vbExclamation
End Sub

Application.GetOpenFilename displays a file picker; it does not open the file itself. If you cancel the picker, the returned value is Boolean, so the macro exits. FreeFile supplies an available file number, and EOF lets the loop stop at the end of the file. Line Input # reads a line without its line-ending characters. An empty line is still read as an empty string.

Run the macro from the Visual Basic Editor with the cursor inside it and press F5, or assign it to a button. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. The example writes into existing cells and does not clear old values below the new import, so an earlier, longer import can leave stale rows behind.

Read UTF-8 text with ADODB.Stream

The built-in Open statement is not a reliable way to specify UTF-8. If a file contains characters such as é, ñ, or non-Latin scripts, the characters may be misread when the file’s encoding differs from the system’s default. On Windows desktop Excel, late binding lets VBA create an ADO stream without adding a reference in the Visual Basic Editor.

Sub ReadUtf8Text()
    Dim filePath As Variant
    Dim stream As Object
    Dim textContent As String
    Dim errorText As String

    filePath = Application.GetOpenFilename("Text Files (*.txt),*.txt")
    If VarType(filePath) = vbBoolean Then Exit Sub

    On Error GoTo ReadError
    Set stream = CreateObject("ADODB.Stream")
    With stream
        .Type = 2
        .Charset = "utf-8"
        .Open
        .LoadFromFile CStr(filePath)
        textContent = .ReadText(-1)
        .Close
    End With

    ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = textContent
    Exit Sub

ReadError:
    errorText = Err.Description
    On Error Resume Next
    If Not stream Is Nothing Then
        If stream.State <> 0 Then stream.Close
    End If
    MsgBox "Could not read the file: " & errorText, vbExclamation
End Sub

Here, .Type = 2 selects text mode, .Charset = "utf-8" specifies the expected encoding, and .ReadText(-1) reads all available text. This version puts the whole file into cell A1, including its line breaks. A cell can hold at most 32,767 characters, so this is not suitable for putting a very large file into one cell. To distribute UTF-8 content line by line, read the returned string and split it at line breaks, taking care to handle the file’s line-ending style.

Put the text into worksheet cells

Writing one line at a time is easy to understand, but it can be slow for a file with many thousands of lines because VBA communicates with the worksheet repeatedly. For larger imports, a common improvement is to collect lines in an array and write the array to a range in one operation. That requires knowing or counting the lines first and sizing the array carefully; for a modest file, the simple loop is often easier to maintain.

Choose the destination deliberately. ThisWorkbook means the workbook containing the macro, while ActiveWorkbook means whichever workbook is currently active. Using ThisWorkbook avoids sending data to a different workbook if the user switches windows. Likewise, an explicit worksheet name is safer than relying on the active sheet, which can change while a macro runs.

If you want every run to replace the previous import, clear the intended destination range before writing. Do not clear the entire worksheet unless that is truly what you want; it could remove unrelated data or formulas. A safer design uses a dedicated import sheet or a known output range, and handles the case where the new file has fewer rows than the previous one.

Understand lines, tabs, and CSV files

Line Input # reads a whole line as one string. It does not split a line at commas or tabs. For example, a line such as Rosa,North,42 will land in a single cell if the line-reading example is used unchanged. A tab-delimited line behaves the same way: it remains one string unless your code or an import feature separates the fields.

For a small, simple tab-separated file, VBA’s Split function can divide a line into fields. For CSV, however, splitting every line at commas is not generally safe: a quoted field can contain a comma, and CSV files can also use quotation marks to represent quotes within a field. Use Excel’s text-import tools or a CSV-aware parser when the file follows CSV quoting rules. This distinction matters because a file extension alone does not guarantee that every row can be divided by a basic delimiter split.

Also consider what the text represents. If values such as account numbers can start with zero, writing them as numbers may remove that zero or cause Excel to interpret them as numeric data. Import them as text when preserving the exact characters is important. Dates and large numeric strings can also be automatically interpreted or displayed differently from the source text.

Encoding, line endings, and file paths

Encoding describes how characters are represented in the file. UTF-8 is common, but files may also use other encodings. Setting a stream’s character set to UTF-8 is appropriate only when the file is actually UTF-8; choosing the wrong character set can produce incorrect characters too. If the source system controls the file, confirm its encoding there rather than guessing from how the text looks in a basic editor.

Windows text files commonly use carriage-return and line-feed characters together, while other sources may use different line endings. Line Input # handles normal text lines without requiring you to remove those endings yourself. When splitting a whole-file string manually, account for the line endings present; otherwise, a carriage-return character may remain at the end of a value. Empty lines and a final line without a line ending are additional cases worth checking with a representative sample.

The file picker avoids hard-coding a path, which is convenient when users choose different files. If a macro instead uses a fixed path, make sure the path is valid for the user’s computer and that the account running Excel can access it. Network drives, permissions, moved files, and mistyped folder names are frequent reasons an import fails.

Common errors and safe macro use

A “file not found” or access error usually points to a wrong path, missing file, or insufficient permission. An error naming a worksheet often means the sheet name in the code does not exactly match a tab in the workbook. The example includes a basic error handler so that it attempts to close the file and displays a message rather than leaving the problem unexplained.

Keep the workbook saved as an Excel macro-enabled workbook (.xlsm) if it needs to retain VBA code. Excel’s macro security settings may block macros from files obtained from the internet or other untrusted locations. Do not enable macros in an unfamiliar workbook just to read its text file; VBA can perform actions beyond importing data. Review the code and follow your organization’s security guidance.

Excel for the web cannot run VBA macros. Excel for Mac supports VBA, but Windows-specific components and file paths can differ; in particular, do not assume that the Windows ADODB.Stream example will work on Mac. The native file statements are a more portable starting point, though paths and dialog behavior still need to be appropriate for the platform. Test platform-specific code on the version where it will be used.

When an Excel import feature is a better fit

VBA is useful when the import is part of a repeatable workbook process, needs custom checks, or must run alongside other automation. For a one-time import, a built-in text-import option can be simpler and easier to inspect. In desktop Excel for Windows, you can use Data > From Text/CSV to preview a file and choose how its columns and data types are interpreted. The available options can vary with the Excel version and file type.

Power Query is another option for repeatable file preparation, especially when you need to clean, split, filter, or reshape rows before loading them into a worksheet. It avoids writing VBA for many common transformations and offers a refreshable query. Choose VBA when procedural control or integration with other macros matters; choose an import or query workflow when transparent, repeatable data transformation is the priority.

Frequently Asked Questions

Can Excel VBA read a TXT file?
Yes. Use Open ... For Input and Line Input # for straightforward line-by-line reading, or an appropriate text stream when you need to specify an encoding such as UTF-8.

How do I read a file without choosing it in a dialog?
Replace the file-picker assignment with a quoted full path, such as a path stored in a variable. Check that the path exists and that Excel has permission to read it; a fixed path may need to change on another computer.

Why are accented characters garbled?
The file may use an encoding different from the one assumed by the read method. Confirm the file’s encoding and use a stream configured for that encoding. Setting UTF-8 blindly will not fix a file saved in a different character set.

Does Line Input split comma-separated values into columns?
No. It returns the entire line as one string. A delimiter-aware import or parser is needed to separate fields, and proper CSV parsing must account for quoted commas.

Can I run the macro in Excel for the web?
No. Excel for the web can open workbooks containing VBA, but it does not execute VBA macros. Use desktop Excel to run the code, or consider a non-VBA workflow such as Power Query where it meets the need.

Can the macro be undone with Ctrl+Z?
No. Excel’s undo history does not reverse changes made by a VBA macro. Save a separate copy before running import code, especially when it writes to or clears worksheet cells.

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.