Copy each line in a test file to particular cell i

Question

Generally there are lot of vba codes to copy a content in a text file to excel sheet. I am looking forward for vba code which will copy each line in a text file to different cells in an excel sheet. I am not finding a good reference for this condition.. Can anyone please come up with some reference to the above question ?

Answer 1

You can always right a PowerShell script to read a text file and put data into an Excel file.

$Excel = New-Object -ComObject Excel.Application 
$Excel.Visible = $False
$Workbook = $Excel.Workbooks.Open("C:\file.xlsx")

$data = Get-Content 'C:\textfile.txt'

$i = $j = 1
foreach ($row in $data){
    $Excel.Cells.Item($i,$j).Value() = $row
    $i += 1
    $j = $i
}

$Excel.Quit()

$Null = & {
[Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook)
}

[GC]::Collect()