Table of Contents
Opening a text file using VBA is quite simple. The command to use is the Open statement, which has the following syntax: Open [filename] For [mode] As [#fileNumber]. For example, to open a text file named “Test.txt” for reading, the code would look like this: Open “Test.txt” For Input As #1. Once the file is open, the program can read the data from the file using the Input statement. After the data is read, the program should close the file using the Close statement.
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:UsersbobDesktopMyTextFile.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:UsersbobDesktopMyTextFile.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 .