0

I wanted to write a batch file for following scenario:

I have multiple text file like 1.txt,2.txt...n.txt. Now I want to copy the last line from text file and paste it in specified excel sheet colunm like coln B or H.

Can anyone help me?

Thanks

user3004625
  • 31
  • 4
  • possible duplicate of [BAT file to read and copy bottom 16 lines from a text file to another one?](http://stackoverflow.com/questions/1632300/bat-file-to-read-and-copy-bottom-16-lines-from-a-text-file-to-another-one) – Siddharth Rout Jan 17 '14 at 11:28
  • Does it have to be a batch file? It could be easier with a VBS Script, I have no idea how to paste something in an excel file only with batch... – Dr. Pee Jan 17 '14 at 13:04

1 Answers1

0

In VBScript you can do it like this:

  1. Create your excel file

    Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\test.xls")

  2. Go through your text files in a loop, open them and read the last line

    Set n = 10 'the number of your files
For i = 0 To n 

    Const ForReading = 1

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(i & ".txt", ForReading)

    Do Until objFile.AtEndOfStream
        strLine = objFile.ReadLine
    Loop

    objFile.Close

  3. Write the data stored in strLine to your excel file

        objExcel.Application.Visible = True
    objExcel.Workbooks.Add
    objExcel.Cells(i, 1).Value = strLine

Next

  4. Save and close your excel file

    objExcel.ActiveWorkbook.Save "C:\test.xls"
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit

I have not tested this code, it should just show you an idea how it could possibly work.

Dr. Pee
  • 126
  • 4