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
Once we run this macro, the contents of the text file called MyTextFile.txt will be displayed in cell A1:
Notice that the contents of cell A1 match the contents from the text file.
Note: You can find the complete documentation for the OpenTextFile method here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
How to Create Folders Using VBA
How to Delete Folders Using VBA
How to Delete Files Using VBA