Welcome to Excel Avon
Read text file using Excel VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
In today’s post, we will explain you how to read text file using excel VBA, although you can read text file manually, but in VBA, we can open or write or read text file whenever you need. Read text file means we have any data in text file or notepad file, and we want to read text file in an excel. Therefore, there are two methods: the FileSystemObject attribute of VBA and the Open and Write methods in VBA.
We usually copy data from textfile and paste it into excel file. We rely on text files because they are very easy to work with because of their lightweight and simple methods. By using VBA coding, we can automate the task of reading data from text file to excel file. This article will show you how to read or copy data from txt file to excel file using VBA code.
As you can see in the image below that we will read text file in excel.
Let us now understand How to Read text file using Excel VBA.
How to Read text File using Excel VBA
Well, you must know how to read text file manually in excel but how to make it in VBA, we will learn in this article. Well it is easy to make it in VBE also, let’s understand. So, we have to go like last time, first go to the Developer Tab then click on the option of Visual Basic as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
We will now activate Microsoft Script Runtime Library, to activate go to Tools option and then References and on that we select Microsoft Script Runtime Library and click OK.
Once the module is inserted, and the library is activated, we’ll write a subroutine to Read Text File.
Sub ReadTextFile() End Sub
Declaring variables which are using in the entire project for TextStream, Filesystem Object, worksheet, File Dialog, String, and Long.
Sub ReadTextFile() Dim txtFile As TextStream Dim FSO As New FileSystemObject Dim WS As Worksheet Dim FD As FileDialog Dim FilePath As String Dim fTxt As String Dim lr As Long End Sub
Now we will set WS as ActiveSeet.
Sub ReadTextFile() Dim txtFile As TextStream Dim FSO As New FileSystemObject Dim WS As Worksheet Dim FD As FileDialog Dim FilePath As String Dim fTxt As String Dim lr As Long Set Ws = ActiveSheet End Sub
Now I will Set FileDiolog as FilePicker, because we have to Choose the Files.
Sub ReadTextFile() Dim txtFile As TextStream Dim FSO As New FileSystemObject Dim WS As Worksheet Dim FD As FileDialog Dim FilePath As String Dim fTxt As String Dim lr As Long Set WS = ActiveSheet Set FD = Application.FileDialog(msoFileDialogFilePicker) End Sub
Now we will use with statement with FileDialog and here we will set File MultiSelection to false then write button name and title name of popup window.
Sub ReadTextFile()
Dim txtFile As TextStream
Dim FSO As New FileSystemObject
Dim WS As Worksheet
Dim FD As FileDialog
Dim FilePath As String
Dim fTxt As String
Dim lr As Long
Set WS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.ButtonName = "Choose"
.Title = "Choose a text file"
End With
End Sub
Now we have to clear all the filters then we will add filter to pick file like we need txt files so we will add it in filter.
Sub ReadTextFile()
Dim txtFile As TextStream
Dim FSO As New FileSystemObject
Dim WS As Worksheet
Dim FD As FileDialog
Dim FilePath As String
Dim fTxt As String
Dim lr As Long
Set WS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.ButtonName = "Choose"
.Title = "Choose a text file"
.Filters.Clear
.Filters.Add "Text File", "*.txt"
End With
End Sub
Create condition with if statement for show, creating another condition with if statement (If the number of select item is more than 0 even 1).
Sub ReadTextFile()
Dim txtFile As TextStream
Dim FSO As New FileSystemObject
Dim WS As Worksheet
Dim FD As FileDialog
Dim FilePath As String
Dim fTxt As String
Dim lr As Long
Set WS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.ButtonName = "Choose"
.Title = "Choose a text file"
.Filters.Clear
.Filters.Add "Text File", "*.txt"
If .Show = True Then
If .SelectedItems.Count > 0 Then
End If
End If
End With
End Sub
Now create another condition, If the number of Select Item in the FilePath is 1.
Sub ReadTextFile()
Dim txtFile As TextStream
Dim FSO As New FileSystemObject
Dim WS As Worksheet
Dim FD As FileDialog
Dim FilePath As String
Dim fTxt As String
Dim lr As Long
Set WS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.ButtonName = "Choose"
.Title = "Choose a text file"
.Filters.Clear
.Filters.Add "Text File", "*.txt"
If .Show = True Then
If .SelectedItems.Count > 0 Then
FilePath = .SelectedItems(1)
End If
End If
End With
End Sub
Now create another condition, If the FilePath is not Blank. then we will set txtFile, opens a specified file and returns a TextStream object.
Sub ReadTextFile()
Dim txtFile As TextStream
Dim FSO As New FileSystemObject
Dim WS As Worksheet
Dim FD As FileDialog
Dim FilePath As String
Dim fTxt As String
Dim lr As Long
Set WS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.ButtonName = "Choose"
.Title = "Choose a text file"
.Filters.Clear
.Filters.Add "Text File", "*.txt"
If .Show = True Then
If .SelectedItems.Count > 0 Then
FilePath = .SelectedItems(1)
End If
End If
End With
If FilePath <> "" Then
Set txtFile = FSO.OpenTextFile(FilePath)
End If
End Sub
Use readall to read text file, it will read your file line-by-line. With this, close statement will be used to close the file.
Sub ReadTextFile()
Dim txtFile As TextStream
Dim FSO As New FileSystemObject
Dim WS As Worksheet
Dim FD As FileDialog
Dim FilePath As String
Dim fTxt As String
Dim lr As Long
Set WS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.ButtonName = "Choose"
.Title = "Choose a text file"
.Filters.Clear
.Filters.Add "Text File", "*.txt"
If .Show = True Then
If .SelectedItems.Count > 0 Then
FilePath = .SelectedItems(1)
End If
End If
End With
If FilePath <> "" Then
Set txtFile = FSO.OpenTextFile(FilePath)
fTxt = txtFile.ReadAll
txtFile.Close
End If
End Sub
Now we will give the range of worksheet where the file has to be opened.
Sub ReadTextFile()
Dim txtFile As TextStream
Dim FSO As New FileSystemObject
Dim WS As Worksheet
Dim FD As FileDialog
Dim FilePath As String
Dim fTxt As String
Dim lr As Long
Set WS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.ButtonName = "Choose"
.Title = "Choose a text file"
.Filters.Clear
.Filters.Add "Text File", "*.txt"
If .Show = True Then
If .SelectedItems.Count > 0 Then
FilePath = .SelectedItems(1)
End If
End If
End With
If FilePath <> "" Then
Set txtFile = FSO.OpenTextFile(FilePath)
fTxt = txtFile.ReadAll
txtFile.Close
ws.Range("A2").Value = Ftxt
End If
End Sub
Now we will run the code
As soon as the code is run, we will get a popup window in which you have to select the text file, you can select any, we have selected the sample text file, you can also see the title name, button name and Filters.
After selecting the file, you have to come back to the worksheet, here you will read text file on the A2 range which is the text of the text file as you can see in the image below, we are showing you to see.
Now we will go back to VBE and change the style of reading, now we will write on a separate row for each line. ReadLine read an entire line from a file up to the newline character.
Sub ReadTextFile()
Dim txtFile As TextStream
Dim FSO As New FileSystemObject
Dim WS As Worksheet
Dim FD As FileDialog
Dim FilePath As String
Dim fTxt As String
Dim lr As Long
Set WS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.ButtonName = "Choose"
.Title = "Choose a text file"
.Filters.Clear
.Filters.Add "Text File", "*.txt"
If .Show = True Then
If .SelectedItems.Count > 0 Then
FilePath = .SelectedItems(1)
End If
End If
End With
If FilePath <> "" Then
Set txtFile = FSO.OpenTextFile(FilePath)
fTxt = txtFile.ReadLine
txtFile.Close
End If
End Sub
Define the location of the last row, Give the range of text files to be opened
Sub ReadTextFile()
Dim txtFile As TextStream
Dim FSO As New FileSystemObject
Dim WS As Worksheet
Dim FD As FileDialog
Dim FilePath As String
Dim fTxt As String
Dim lr As Long
Set WS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.ButtonName = "Choose"
.Title = "Choose a text file"
.Filters.Clear
.Filters.Add "Text File", "*.txt"
If .Show = True Then
If .SelectedItems.Count > 0 Then
FilePath = .SelectedItems(1)
End If
End If
End With
If FilePath <> "" Then
Set txtFile = FSO.OpenTextFile(FilePath)
fTxt = txtFile.ReadLine
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & lr).Value = fTxt
txtFile.Close
End If
End Sub
Now we will run the code
As soon as the code is run, we will get a popup window in which you have to select the text file, you can select any, whatever you want to read text file we have selected the sample text file.
After selecting the file, you have to come back to the worksheet, here you will read text file on the A2 range which is the text of the text file as you can see in the image below.
Let’s Go back to VBE, here we will be using Do until Loop. AtEndofLine, Moves or extends the ending character position of a range.
Sub ReadTextFile()
Dim txtFile As TextStream
Dim FSO As New FileSystemObject
Dim WS As Worksheet
Dim FD As FileDialog
Dim FilePath As String
Dim fTxt As String
Dim lr As Long
Set WS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.ButtonName = "Choose"
.Title = "Choose a text file"
.Filters.Clear
.Filters.Add "Text File", "*.txt"
If .Show = True Then
If .SelectedItems.Count > 0 Then
FilePath = .SelectedItems(1)
End If
End If
End With
If FilePath <> "" Then
Set txtFile = FSO.OpenTextFile(FilePath)
Do Until txtFile.AtEndOfLine
fTxt = txtFile.ReadLine
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
Sheet1.Range("A" & lr).Value = fTxt
fTxt = txtFile.ReadLine
Loop
txtFile.Close
End If
End Sub
Now we will run the code
As soon as the code is run, we will get a popup window in which you have to select the text file, you can select any, we have selected the sample text file.
After selecting the file, you have to come back to the worksheet, here you will read text file on the A2 range which is the text of the text file as you can see in the image below. ReadLine, Reads an entire line from a TextStream file and returns the resulting string.
So, I hope you have understood How to Read text file using Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about How to Read text file using Excel VBA
