Visual Basic for Applications:
Reading a text file

How to:

The simplest way to read a text file in VBA is by using the Open statement in combination with the Input or Line Input functions. Here’s how you can do it:

1. Open the file for reading - First, you need to open the file. Ensure the file path is accessible to the application.

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

2. Read the file content - You can read either line-by-line using Line Input or the entire file using Input.

• Reading line-by-line:

Dim fileContent As String
While Not EOF(1) ' EOF = End Of File
    Line Input #1, fileContent
    Debug.Print fileContent ' Outputs the line to the Immediate Window
Wend
Close #1

• Reading the entire file at once:

Dim fileContent As String
Dim fileSize As Long
fileSize = LOF(1) ' LOF = Length Of File
If fileSize > 0 Then
    fileContent = Input(fileSize, #1)
    Debug.Print fileContent
End If
Close #1

3. Sample Output:

Assuming example.txt contains:

Hello,
This is a sample text file.
Enjoy reading!

The output in the Immediate Window would be the entire text or line-by-line based on the method you choose.

Deep Dive

Reading text files in VBA has been a cornerstone of office automation tasks for decades. The methods illustrated, albeit efficient within the VBA ecosystem, might seem archaic compared to modern programming practices which often employ higher-level abstractions or libraries for file operations. For instance, Python uses the open() function within a with statement, providing a cleaner syntax and automatic file handling capabilities.

That being said, when working within the confines of the Microsoft Office environment, VBA provides a direct and native method to manipulate files, which can be crucial for applications that require interoperability with Office products. The simplicity of opening a text file, reading, and processing its contents line-by-line or in its entirety, without the need for external libraries or complex configurations, makes VBA a valuable tool in the Office developer’s toolkit.

While there are better alternatives in modern programming languages for handling files more efficiently and with less code, understanding and utilizing VBA’s capabilities for reading text files can significantly enhance productivity and extend the functionality of Office-based applications.