How to Read from a Text File in Excel VBA

Written 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 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)

Close

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.

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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