Unable to import text file into excel

[bolobaboo]

Hi
I have following text file ..
ComputerName: xxxxxxxxxxxx
Logfile: Security source Security
EventCode: 528
EventType: 4
Type: Audit Success
User: domainname\username
Message: Successful Logon
User Name: username
Domain: domainname
Logon ID: (0x0,0x245D6D8)
Logon Type: 10
Logon Process: User32
Authentication Package: Negotiate
Workstation Name: xxxxxxxxxxx
Logon GUID: {6bf7409a-dc43-e893-6355-dcf937334df5}
Caller User Name: xxxxxxxxxxx
Caller Domain: domainname
Caller Logon ID: (0x0,0x3E7)
Caller Process ID: 4320
Transited Services: -
Source Network Address: x.x.x.x.
Source Port: 46563

ComputerName: xxxxxxxxxxxx
Logfile: Security source Security
EventCode: 528
EventType: 4
Type: Audit Success
User: domainname\username
Message: Successful Logon
User Name: username
Domain: domainname
Logon ID: (0x0,0x245D6D8)
Logon Type: 10
Logon Process: User32
Authentication Package: Negotiate
Workstation Name: xxxxxxxxxxx
Logon GUID: {6bf7409a-dc43-e893-6355-dcf937334df5}
Caller User Name: xxxxxxxxxxx
Caller Domain: domainname
Caller Logon ID: (0x0,0x3E7)
Caller Process ID: 4320
Transited Services: -
Source Network Address: x.x.x.x.
Source Port: 46563

I used following code But they appear in one collumn,Any body can help me on this ? Other thing how to put heading for each column ?

Option Explicit
Dim objUser, strExcelPath, objExcel, objSheet, _
objFSO, objFile, aline, aLines, irow, icol
Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("testing.txt", _
ForReading)
strExcelPath = "c:\anil\sg3.xls"
' Bind to Excel object.
'On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If (Err.Number <> 0) Then
On Error GoTo 0
Wscript.Echo "Excel application not found."
Wscript.Quit
End If
On Error GoTo 0
objExcel.visible = true
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "test"
aLines = split(objFile.ReadAll, vbnewline)
For irow = 1 to Ubound(aLines) + 1
aline = split(aLines(irow-1), vbTab)
for icol = 1 to Ubound(aline) + 1
objSheet.Cells(irow, icol).value = aline(icol-1)
Next ' icol
next ' irow
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
wscript.quit

I want output like following ..
ComputerName Logfile EventCode . . .
xxxxxxxxxxxx Security source Security 528 . . .
.
.

 

[Remou]

Please use code tags.

Try:

Option Explicit
Dim objUser, strExcelPath, objExcel, objSheet, _
objFSO, objFile, aline, l, irow, icol

Const ForReading = 1

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

' strExcelPath = ...
' Bind to Excel object.
'On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If (Err.Number <> 0) Then
    Wscript.Echo "Excel application not found."
    Wscript.Quit
End If

objExcel.Visible = True
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "test"

irow= 1
icol= 1
While Not objFile.AtEndOfStream

    l = objFile.ReadLine
    
    If Trim(l) = "" Then
        irow= irow+ 1
        icol= 1
    Else
        aLine = Split(l, ":")
    
    
        If irow = 1 Then
          objSheet.Cells(1, icol) = aLine(0)
        End If
        
        objSheet.Cells(irow + 1, icol) = aLine(1)
      
        icol = icol + 1
    End If
Wend

objExcel.UserControl = True

http://lessthandot.com

 

[bolobaboo]

Thank you Remou. Whenever i see ARRAY they make me dizzy.
Thank you again.