How to print all the Lines of a Text File in Excel sheet using VB Script

Write a script in VB to write all the lines of a Text File into an excel spreadsheet using FileSystemObject

VB Script to write all the lines of a text file in to a Excel spread sheet. There are two ways to write all the lines by opening an existing Text File and the second one to create a Text File and write some lines and then writing them in the Excel Sheet.

Method 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible=True

objExcel.workbooks.Add()

Const ForReading =1

Set FSO = CreateObject("Scripting.FileSystemObject")
Set file = FSO.OpenTextFile("F:\TestFolder\Text1.txt", 1)

x=1

Do Until File.AtEndOfStream
Line = File.ReadLine
objExcel.Cells(x,1) = Line

x=x+1
Loop

objFile.Close

Method 2

'Script to create and write few lines in a Text file
'and write them in the excel sheet

'Create an object variable to interact with the File System Object
Set Fso = CreateObject("Scripting.FileSystemObject")
Set xlapp = CreateObject("Excel.Application")

'Create a text files using CreateTextFile method
Set fil = Fso.CreateTextFile("F:\TestFolder\Text1.txt")
Set wbook = xlapp.Workbooks.Add
set wsheet = xlapp.worksheets(1)

'Write few lines into the first text file
For i = 1 to 100
fil.WriteLine("This is Line "&i)
Next

Set fil = nothing

'Open first text file in read mode and write in excel sheet
Set fil1 = fso.OpenTextFile("F:\TestFolder\Text1.txt",1, False)

X=1
Do Until fil1.AtEndOfStream
strLine = fil1.ReadLine
xlapp.Cells(x,1) = strLine
X=x+1
Loop

wbook.SaveAs "F:\TestFolder\Test.xls"

set wbook=nothing
xlapp.quit
set wsheet = nothing
Set fil = nothing
Set fil1=Nothing
Set fil2 = nothing