Any method to write the Text file into the Excel sheet in VBA

[divya_agarwal]

Any easiest method to write the text file into the Excel sheet.

 

[Krishnakumar]

Hi Divya,

Try something like..

Sub kTest()
Dim txt As String, FileFolder   As String, fs As Object
Dim w(), i  As Long, c  As Long, n  As Long, x, FileName As String
FileFolder = "C:\Test\" 'change to suit
FileName = "Test.txt" 'change to suit
Set fs = CreateObject("Scripting.FileSystemObject")
txt = fs.opentextfile(FileFolder & FileName).readall
x = Split(txt, vbCrLf)
ReDim w(1 To UBound(x) + 1, 1 To UBound(Split(x(0), vbTab)) + 1)
For i = 0 To UBound(x)
    n = n + 1
    For c = 0 To UBound(Split(x(i), vbTab))
        w(n, c + 1) = Split(x(i), vbTab)(c)
    Next
Next
With Range("a1")
    .Resize(n, UBound(w, 2)).Value = w
End With
End Sub