BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
The easiest way to work with text files in VBA is using TextStream objects - this blog explains how they work.
- Reading and Writing Text Files using TextStreams
- Creating a Text File and Writing Lines
- Reading in Lines from a Text File (this blog)
This blog is part of a complete Excel VBA tutorial, but we also run classroom training courses in VBA.
Posted by Andy Brown on 13 January 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Reading in Lines from a Text File
Sample Code to Loop Over Lines
Having created a text file (as per the previous part of this blog), here's some code to read it in. The trick is to keep reading lines until we reach the end of the text stream:
Sub ReadFile()
'again, we need this strange thing to exist so that ...
Dim fso As New FileSystemObject
'the file we're going to read from
Dim ts As TextStream
'... we can open a text file with reference to it
Set ts = fso.OpenTextFile("C:\Wise Owl\info.txt", ForReading)
'keep reading in lines till no more
Dim ThisLine As String
Dim i As Integer
i = 0
Do Until ts.AtEndOfStream
ThisLine = ts.ReadLine
i = i + 1
Debug.Print "Line " & i, ThisLine
Loop
'close down the file
ts.Close
End Sub
Here's what this would show in the immediate window:
The routine prints out the lines to the immediate window
More on the OpenTextFile Method
When you choose to open a text file, you can do it in one of 3 ways:
The 3 possible ways to open a text stream
In our example we wanted to read from the file, but you can also use the OpenTextFile method to open a file to write more lines to it (using ForAppending).
- Reading and Writing Text Files using TextStreams
- Creating a Text File and Writing Lines
- Reading in Lines from a Text File (this blog)