How to Open a Text File Using VBA (With Example)

You can use the OpenTextFile method in VBA to open a text file from a specific file path.

Here is one common way to use this method in practice:

Sub ReadTextFile()

Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
    
'specify path to text file
Set MyTextFile = FSO.OpenTextFile("C:\Users\bob\Desktop\MyTextFile.txt", ForReading)
   
'open text file and display contents in cell A1
TxtString = MyTextFile.ReadAll
MyTextFile.Close
ThisWorkbook.Sheets(1).Range("A1").Value = TxtString

End Sub

This particular macro reads the text file called MyTextFile.txt located on the Desktop of my computer and displays the contents of the file in cell A1.

The following example shows how to use this syntax to read a text file in practice.

Example: How to Open a Text File Using VBA

Suppose we have a text file called MyTextFile.txt located on the Desktop that we’d like to read into Excel using VBA.

Here are the contents of the file:

Before using VBA to read this file, we need to first enable Microsoft Scripting Runtime within the VB Editor.

To do so, open the VB Editor, then click Tools, then click References:

In the new window that appears, scroll down until you see Microsoft Scripting Runtime and check the box next to it. Then click OK.

Next, we can create the following macro to read a text file:

Sub ReadTextFile()

Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
    
'specify path to text file
Set MyTextFile = FSO.OpenTextFile("C:\Users\bob\Desktop\MyTextFile.txt", ForReading)
   
'open text file and display contents in cell A1
TxtString = MyTextFile.ReadAll
MyTextFile.Close
ThisWorkbook.Sheets(1).Range("A1").Value = TxtString

End Sub