How to Read from a Text File in Excel VBA

by Jaime Avelar

Reading a text file is something a computer programmer must know how to do when developing computer programs. In VBA you can use the "Input #" statement to open a text file and read its contents. The data read from the file is stored in memory for you to manipulate as you wish. Using VBA to read a text file is useful when you need to transfer large amounts of data to an Excel spreadsheet.

1

Click the "Developer" tab, click "Visual Basic" and click the "Insert" menu. SElect "Module" to insert a new code module.

2

Start by creating a sub-procedure using the following code:

Private Sub readTextFile()

3

Create three variables you will use to read the text file:

Dim fileText As String

Dim myTextFile As String

Dim memFile As Integer

4

Define the path and file name of the text file you want to read:

myTextFile = "F:\temp.txt"

memFile = FreeFile

5

Open the file, read its contents and close the file:

Open myTextFile For Input As #memFile

fileText = Input$(LOF(1), 1)

Close

6

Display the text file results through the Immediate window.

Debug.Print (fileText)

7

End the sub-procedure by typing "End Sub" (without quotes). Press F5 to run the procedure and read the file.

About the Author

Jaime Avelar is a professional writer whose programming articles appear on various websites. He has been a software programmer since 2000. Avelar holds a Master of Science in information systems from the University of Texas at Arlington.