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
3
  • 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. Commented Feb 18, 2021 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. Commented Feb 19, 2021 at 1:24
  • I'm very much delayed on the project and will be very happy if some one can assist. Plaese Commented Feb 23, 2021 at 6:48

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.