0

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 ?

Community
  • 1
  • 1
Prashanth K
  • 121
  • 2
  • 3
  • 10
  • You need to read from a file, then write to excel [Read from a file in vba][1] [Write to a cell in vba][2] [1]: http://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba [2]: http://stackoverflow.com/questions/257229/vba-excel-macro-writing-an-input-integer-into-a-cell – Eric Jun 05 '13 at 21:16

1 Answers1

0

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()
E.V.I.L.
  • 2,120
  • 13
  • 14