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.


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


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

Private Sub readTextFile()


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

Dim fileText As String

Dim myTextFile As String

Dim memFile As Integer


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

myTextFile = "F:\temp.txt"

memFile = FreeFile


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

Open myTextFile For Input As #memFile

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



Display the text file results through the Immediate window.

Debug.Print (fileText)


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.