How to Open Text Files in Excel VBA: Complete Guide to Reading, Loading, and Processing Text Data

Working with text files is an essential part of Excel automation. Many systems export data as .txt, .csv, or log files, and companies frequently rely on text-based formats for integrations, data transfer, and archiving. With Excel VBA, you can automatically open, read, parse, and process text files, allowing you to build practical tools for data transformation, reporting, and RPA workflows.

This complete guide explains how to open text files using Excel VBA, how to read them line by line, how to load them into worksheets, and how to apply real-world techniques to automate business operations.

---

✅ Understanding How to Open Text Files in VBA (Core Methods and Use Cases)

・How VBA handles text files

Excel VBA provides multiple ways to work with text files:

• Workbooks.Open – opens text files in Excel format
• Open… Input / Output / Append – low-level file access
• FileSystemObject (FSO) – object-based file handling
• TextStream – reading or writing line-by-line
• QueryTables – structured import into Excel

Each method is useful depending on the task.

・Common business scenarios

Text files appear in many workflows:

• Exported logs
• ERP system outputs
• CSV or TSV files
• System monitoring outputs
• API or integration dumps
• Machine-generated data
• Data for UiPath or RPA pipelines

Understanding how to read text files is essential for automation.

---

✅ Opening a Text File Directly in Excel Using Workbooks.Open

・Basic syntax (Syntax: Open text file with Workbooks.Open)

Workbooks.Open "C:\Data\Sample.txt"

Excel automatically imports the file using default delimiters.

・Specifying file format and delimiters

Workbooks.Open Filename:="C:\Data\report.txt", _
Format:=6 ' 6 = semicolon delimiter or depends on system

・When to use this method

• Quick loading of structured text
• CSV-like files
• Files meant for table import
• Fast preview before processing

Excel’s automatic logic may reformat numbers or dates, so use carefully.

---

✅ Opening and Reading Text Files Line by Line (Open … Input)

・Basic syntax for low-level text reading

Open "C:\Data\log.txt" For Input As #1

・Reading all lines

Dim line As String
Open "C:\Data\log.txt" For Input As #1
Do Until EOF(1)
Line Input #1, line
Debug.Print line
Loop
Close #1

・When to use line-by-line reading

• Log processing
• JSON-like lines
• System event data
• Custom parsing
• Dynamic file structures

This method gives maximum control.

---

✅ Using FileSystemObject (FSO) for Powerful File Handling

・Enable FSO

Early binding:

Dim fso As New FileSystemObject

Late binding (no reference needed):

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

・Reading a text file

Dim ts As Object
Set ts = fso.OpenTextFile("C:\Data\log.txt", 1)  '1 = ForReading
Do Until ts.AtEndOfStream
Debug.Print ts.ReadLine
Loop
ts.Close

・Advantages of FSO

• Structured file objects
• Better error handling
• Supports file creation and deletion
• Can check if files exist
• Works well in RPA workflows

・Check if a file exists

If fso.FileExists("C:\Data\log.txt") Then

・Get file metadata

fso.GetFile("C:\Data\log.txt").DateCreated

---

✅ Loading Text Files into Excel Using QueryTables

・Importing text with control over delimiters

With ActiveSheet.QueryTables.Add( _
Connection:="TEXT;C:\Data\sales.txt", _
Destination:=Range("A1"))
.TextFileCommaDelimiter = True
.TextFileParseType = xlDelimited
.Refresh BackgroundQuery:=False
End With

・Supported delimiters

• Comma
• Tab
• Semicolon
• Space
• Custom characters

・When QueryTables are best

• CSV or TSV files
• Structured column-based formats
• Importing into tables
• Large datasets
• Consistent formatting needs

This method avoids Excel’s automatic guessing.

---

✅ Step-by-Step Guide: Common Ways to Open and Read Text Files

---

・Step 1: Choose the method

• For simple import → Workbooks.Open
• For custom parsing → Open… Input
• For advanced operations → FSO
• For structured data → QueryTables

---

・Step 2: Read content

Example using Input:

Line Input #1, textLine

Example using FSO:

ts.ReadLine

---

・Step 3: Process the data

Examples:

• Split lines
• Convert values
• Detect errors
• Build arrays
• Insert into Excel

---

・Step 4: Close the file

Close #1

or

ts.Close

---

✅ Practical VBA Examples for Text File Handling

---

・Example 1: Read a text file into Excel row-by-row

Sub LoadTextToSheet()
Dim line As String
Dim r As Long: r = 1
Open "C:\Data\input.txt" For Input As #1
Do Until EOF(1)
Line Input #1, line
Cells(r, 1).Value = line
r = r + 1
Loop
Close #1
End Sub

---

・Example 2: Read a comma-separated text file

Sub LoadCSVText()
Dim line As String
Dim arr As Variant
Dim r As Long: r = 1
Open "C:\Data\sales.txt" For Input As #1
Do Until EOF(1)
Line Input #1, line
arr = Split(line, ",")
Range("A" & r).Resize(1, UBound(arr) + 1).Value = arr
r = r + 1
Loop
Close #1
End Sub

---

・Example 3: Read text file and filter lines

If InStr(line, "ERROR") > 0 Then

---

・Example 4: Read text and build a report

If Left(line, 5) = "USER:" Then

---

・Example 5: Preprocessing text for UiPath robots

line = Replace(line, vbTab, ",")

UiPath workflows prefer clean, standardized fields.

---

✅ Writing to Text Files (Output / Append)

・Creating a new file

Open "C:\Data\output.txt" For Output As #1
Print #1, "Report generated: " & Now
Close #1

・Appending to an existing file

Open "C:\Data\log.txt" For Append As #1
Print #1, "New entry"
Close #1

Useful for automation logs.

---

✅ Reading Extremely Large Text Files

・Avoid loading the entire file into memory

Always process line-by-line.

・Disable screen updating for speed

Application.ScreenUpdating = False

・Avoid selecting cells

Write directly.

・FSO is often faster for large logs

FSO streams are efficient for large text processing.

---

✅ Error Handling for Text File Operations

・File does not exist

If Dir("C:\Data\log.txt") = "" Then

・File locked by another process

Especially common with shared drives.

On Error GoTo Locked
Set ts = fso.OpenTextFile(path, 1)
Exit Sub
Locked:
MsgBox "File is in use."

・Handling unexpected line formats

Check for malformed entries.

・Handling empty files

EOF catches this.

・Network issues

Retry logic may be needed for RPA scenarios.

---

✅ Best Practices for Text File Automation in VBA

・Always validate file existence

Avoid runtime errors.

・Use full file paths

Relative paths can fail depending on working directory.

・Use error handling

Text files often change unexpectedly.

・Optimize performance

Avoid long loops with screen updating enabled.

・Format data immediately after import

Normalize fields and delimiters.

・Coordinate with RPA robots

If UiPath or Power Automate will process the file next:

• Remove merged cells
• Remove formatting inconsistencies
• Convert text to stable formats

・Keep your macros flexible

File names may vary, so consider wildcards or folder scanning.

---

✅ Real Business Use Cases for Text File Handling

・Importing logs from monitoring systems

Servers and applications often produce .txt outputs.

・Loading CSV or TSV files into Excel tables

Frequent in finance, accounting, and operations.

・Parsing sensor or machine log files

Manufacturing and IoT devices export text data.

・Processing daily exports from web apps

Many SaaS tools export raw .txt.

・RPA preprocessing

UiPath robots rely on clean text formats for next steps.

・Bulk text file consolidation

For auditing or compliance reports.

---

✅ Summary：Mastering Text File Handling Enables Powerful and Flexible Excel VBA Automation

• Use Workbooks.Open for quick imports.
• Use Open… Input for line-level control.
• Use FileSystemObject for advanced, object-based file handling.
• Use QueryTables for structured, delimiter-controlled imports.
• Read text files line-by-line for speed and reliability.
• Apply error handling to manage missing or locked files.
• Normalize data for downstream automation, including RPA tools.
• Build flexible systems that adapt to changing file formats and business workflows.

By mastering these text file techniques, you unlock the ability to automate data ingestion, reporting, log analysis, and integration pipelines — transforming Excel VBA into a powerful tool capable of handling real-world enterprise automation.