How to Read from a Text File in Excel VBA

Written by jaime avelar | 13/05/2017

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 file Text 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

file Text = 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.

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.