In this post I'll explain how to read data from text file using a macro in excel. Below you will find that we have a list of people and their profession in a text file. And let's assume that we want to move this data to excel in tabular form.
Assuming
- we have a text file as shown in the image below and it's file path is "C:/input.txt"
- fields in each record are separated by a tab
Steps:
1. Open vb editor in excel and insert a new module.
2. Copy this piece of code there
Sub readText()
Dim myFile As String
myFile = "C:/users/kamalg/Desktop/input.txt"
Open myFile For Input As #1
i = 1
Do Until EOF(1)
Line Input #1, textline
Cells(i, 1).Value = Split(textline, vbTab)(0)
Cells(i, 2).Value = Split(textline, vbTab)(1)
i = i + 1
Loop
Close #1
End Sub
3. Run the macro. And you'll see output like this:
4. You can further add a button to run this sub procedure. Go to Developer tab and insert a button.
5. Then assign your code to this button.
6. Now you can trigger this macro using this button.
