Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
HA 12345 6789 10111213
HB Spoon fork torch
Here is what I have for a vsbscript code, but I am stuck somewhere:Option Explicit
Dim oFSO, sFile, oFile, sText, sXLS, oOut
sFile = "C:\Sample\test.txt"
sXLS = "C:\Sample\testouput.xlsx"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oOut = oFSO.CreateTextFile(sXLS, True)
If oFSO.FileExists(sFile) Then
Set oFile = oFSO.OpenTextFile(sFile, 1)
Do While Not oFile.AtEndOfStream
sText = oFile.ReadLine
If (sText) = "HA" Then
oOut.WriteLine (oSheet.Cells(Row, 1).Value)
End If
if Trim(sText) = "HB" Then
oOut.WriteLine (oSheet.Cells(Row, 2).Value)
End If
Exit Do
Loop
oFile.Close
End if
sText = oFile.ReadLine
If left(sText,2) = "HA" Then
Next part, you do not want to use "oOut.WriteLine (oSheet.Cells(Row, 1).Value)" to write into an Excel sheet. You cannot treat a XLSX file just as a text file, because it needs to be a XML file with proper style sheets aso. Usually you open the Excel application with your XLS(X) file opened, and then write directly into the application. That would look like this:Option Explicit
Dim excel, sFile, sXLS, oFSO, sText, row
sFile = "C:\Sample\test.txt"
sXLS = "C:\Sample\testouput.xlsx"
Set excel = WScript.CreateObject ("Excel.Application")
excel.Visible = false
Set oFSO = CreateObject("Scripting.FileSystemObject")
row = 1
If oFSO.FileExists(sFile) Then
Set wbs = excel.Workbooks.Open(sXLS)
Set ws = wbs.WorkSheets(1)
Set oFile = oFSO.OpenTextFile(sFile, 1)
Do While Not oFile.AtEndOfStream
sText = oFile.ReadLine
If Left(sText, 2) = "HA" then
ws.Range("A" & row).Value = sText
End If
If Left(sText, 2) = "HB" then
ws.Range("B" & row).Value = sText
End If
Loop
oFile.Close
Application.DisplayAlerts = False
wbs.Save
wbs.Close 0
Application.DisplayAlerts = true
End If
excel.quit
set excel = nothing
Option Explicit
Dim excel, sFile, sXLS, oFSO, sText, row
sFile = "C:\Sample\test.txt"
sXLS = "C:\Sample\testoutput.xlsx"
Set excel = WScript.CreateObject ("Excel.Application")
excel.Visible = false
Set oFSO = CreateObject("Scripting.FileSystemObject")
row = 2
If oFSO.FileExists(sFile) Then
Set wbs = excel.Workbooks.Open(sXLS)
Set ws = wbs.WorkSheets(1)
Set oFile = oFSO.OpenTextFile(sFile, 1)
Do While Not oFile.AtEndOfStream
sText = oFile.ReadLine
ws.Range("A" & row).Value = getField(sText, 4)
ws.Range("B" & row).Value = getField(sText, 10)
ws.Range("C" & row).Value = getField(sText, 18)
ws.Range("D" & row).Value = getField(sText, 2)
ws.Range("E" & row).Value = getField(sText, 1)
ws.Range("F" & row).Value = getField(sText, 18)
ws.Range("G" & row).Value = getField(sText, 2)
ws.Range("H" & row).Value = getField(sText, 1)
ws.Range("I" & row).Value = getField(sText, 10)
ws.Range("J" & row).Value = getField(sText, 10)
row = row+1
Loop
oFile.Close
Application.DisplayAlerts = False
wbs.Save
wbs.Close 0
Application.DisplayAlerts = true
End If
excel.quit
set excel = nothing
Function getField(ByRef line As String, ByVal length As Integer) as String
getField = Left(line, length)
line = Mid$(line, length + 1)
End Function
Option Explicit
Dim sFile, sXLS, excel, oFSO, oFile, wbs, ws, sText, row
sFile = "C:\Sample\input.txt"
sXLS = "C:\Sample\testoutput.xlsx"
Set excel = WScript.CreateObject ("Excel.Application")
excel.Visible = false
Set oFSO = CreateObject("Scripting.FileSystemObject")
row = 2
If oFSO.FileExists(sFile) Then
Set wbs = excel.Workbooks.Open(sXLS)
Set ws = wbs.WorkSheets(1)
Set oFile = oFSO.OpenTextFile(sFile, 1)
Do While Not oFile.AtEndOfStream
sText = oFile.ReadLine
ws.Range("A" & row).Value = getField(sText, 4)
ws.Range("B" & row).Value = getField(sText, 10)
ws.Range("C" & row).Value = getField(sText, 10)
ws.Range("D" & row).Value = getField(sText, 18)
ws.Range("E" & row).Value = getField(sText, 2)
ws.Range("F" & row).Value = getField(sText, 1)
ws.Range("G" & row).Value = getField(sText, 18)
ws.Range("H" & row).Value = getField(sText, 2)
ws.Range("I" & row).Value = getField(sText, 1)
ws.Range("J" & row).Value = getField(sText, 10)
ws.Range("K" & row).Value = getField(sText, 10)
row = row+1
Loop
oFile.Close
excel.DisplayAlerts = False
wbs.Save
wbs.Close 0
excel.DisplayAlerts = true
End If
excel.quit
set excel = nothing
Function getField(ByRef line, ByVal length)
getField = Left(line, length)
line = Mid(line, length + 1)
End Function