0

I am new to VBScript. I need to convert text file to Excel file. Been trying for a few days and still fail.

I have thousand of lines in text file but after line "====Succeeded MML Command====" I only need certain data such as NE to be placed in the 1st column, Time in the 2nd column, MML SN in the 3rd column follow by MML Command, Retcode, Explain_info, Cabinet No., Subrack No., Slot No. , TX Channel No., VSWR(0.01) in the next columns.Also been reading code from Extract Data from Text File into Excel this look good but I prefer using vbscript.

Sample text

Output

Target output

Here's what I have:

Dim objFSO
Dim TextFile
Dim TextRead
Dim Line, Line1, Line2, Line3
Dim Count

ExcelFilePath = "D:\Test\output.xlsx"

Set objExcel = CreateObject("Excel.Application")
objExcel.visible = true
Set objWB = objExcel.Workbooks.Open(ExcelFilePath)  
Set SheetObject = objWB.Worksheets("Sheet1") 

Const ForReading = 1 

TextFile = "D:\Test\*.txt"

set objFSO = CreateObject("Scripting.FileSystemObject")

set TextRead = objFSO.OpenTextFile(TextFile,ForReading)

row = 2 
With SheetObject

  .Range(.Columns(1),.Columns(11)).NumberFormat = "@"

  .Cells(1, 1).Value = "eNodeBName"
  .Cells(1, 2).Value = "Time"
  .Cells(1, 3).Value = "MML SN"
  .Cells(1, 4).Value = "MML Command"
  .Cells(1, 5).Value = "Retcode"
  .Cells(1, 6).Value = "Explain_info"
  .Cells(1, 7).Value = "Cabinet No."
  .Cells(1, 8).Value = "Subrack No."
  .Cells(1, 9).Value = "Slot No."
  .Cells(1, 10).Value = "TX Channel No."
  .Cells(1, 11).Value = "VSWR(0.01)"


 End With

  if instr(strText, "Succeeded") > 0 then
   msgbox("Found it!")
  end if
   msgbox("DONE")  

 objWB.Save
 objWB.Close
 objExcel.Quit
Geert Bellekens
  • 12,788
  • 2
  • 23
  • 50
JRashid
  • 23
  • 1
  • 6
  • 1
    What is the question? Also, please do not use screenshots. Also, I am assuming you will want to loop through your textfile, but you are not doing that. – Rno Feb 18 '21 at 16:55
  • Oh sorry my bad. The question is, I have thousand of lines in text file but after line "====Succeeded MML Command====" I only need certain data such as NE to be placed in the 1st column, Time in the 2nd column, MML SN in the 3rd column follow by MML Command, Retcode, Explain_info, Cabinet No., Subrack No., Slot No. , TX Channel No., VSWR(0.01) in the next columns. – JRashid Feb 19 '21 at 01:24
  • I'm very much delayed on the project and will be very happy if some one can assist. Plaese – JRashid Feb 23 '21 at 06:48

0 Answers0