VBA – Read Text File into String

This tutorial will demonstrate how to read a text file into a string in VBA.

We can either read the text file line by line or as an entire file.

Read an Entire Text File into a String

Text in a text file is usually made up of several lines, separated by delimiters. These can be a comma (“,”), a comma with space (“, “), a semicolon (“;”), a  semicolon with space (“; “),  a space (“ “),  a tab (vbTab) or in rare cases any other character such as a tilde (~). The lines are normally separated by a line break (vbCRLF).

The easiest way to read an entire text file in VBA is to run the following code which will place the entire contents of the text file into a string variable.  This procedure uses the VBA Open statement and the VBA FreeFile function.

Sub ReadFile()
  Dim iTxtFile As Integer
  Dim strFile As String
  Dim strFileText As String
  strFile = "C:\Test\TestFile.txt"
  iTxtFile = FreeFile
  Open strFile For Input As FreeFile
  strFileText = Input(LOF(iTxtFile), iTxtFile)
  Close iTxtFile
End Sub

We can also read a text file in VBA by using the FileSystemObject. In the code below we have used late binding with the File System Object.

Sub ReadTextFile() 
  Dim strText As String 
  Dim FSO  as Object 
  Dim TSO As Object 
  Set FSO = CreateObject("Scripting.FileSystemObject") 
  Set TSO = FSO.OpenTextFile("C:\Test\TestFile.txt") 
  strText = TSO.ReadAll
  TSO.Close 
  Set TSO = Nothing
  Set FSO = Nothing
End Sub

We can also use early binding in the code and declare the File System Object by creating a reference in our Excel project to the File System Object.

Sub ReadTextFile() 
  Dim strText As String 
  Dim FSO as New FileSystemObject
  Dim TSO As Object 
  Set FSO = CreateObject("Scripting.FileSystemObject") 
  Set TSO = FSO.OpenTextFile("C:\Test\TestFile.txt") 
  strText = TSO.ReadAll 
  TSO.Close 
  Set TSO = Nothing
  Set FSO = Nothing
End Sub

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!