txt to excel

[benficaman]

Can someone tell me if is possible to transpose the data in a text box to excel form example:

0.0 0.0 0.0 0.0 0.0 0.0 0.0 30.4 0 0 c2t6d0
1.1 0.1 7.5 1.6 0.0 0.0 0.9 6.1 0 0 c0t4d0
1.1 0.1 7.5 1.6 0.0 0.0 0.9 6.0 0 0 c0t5d0

excel:

kw/s wait actv wsvc_t asvc_t %w %b device
0.0 0.0 0.0 0.0 30,4 0 0 c2t6d0
1.6 0.0 0.0 0.9 6,1 0 0 c0t4d0
1.6 0.0 0.0 0.9 6,1 0 0 c0t5d0

[Hack]

Private Sub Command1_Click()
Dim xlapp As New Excel.Application
Dim xlwkbook As New Excel.Workbook
 
Set xlapp = CreateObject("Excel.application")
'Note:  This spreadsheet must exist prior to running this code
Set xlwkbook = xlapp.Workbooks.Open(App.Path & "Book1.xls")
xlapp.Visible = True
 
    With xlapp.ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;c:\windows\temp\virus.txt", Destination:=Range("A1"))
        .Name = "update popup dont show"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(22, 4, 3, 2, 2, 2)
        .Refresh BackgroundQuery:=False
    End With
 
xlwkbook.SaveAs ("Test.xls")
xlwkbook.Close
 
Set xlapp = Nothing
 
End Sub

[benficaman]

Sorry i can get the code to work, an error occurs
in this part of the code
/
With xlapp.ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;c:\windows\temp\virus.txt", Destination:=Range("A1"))
/

"Application-defined or object-defined error"

I don´t know why this error occurs

[phinds]

here's a tutorial project on how to stuff data into an excel sheet. If you're looking for a canned procedure, this isn't it. If you're looking to learn how to do office automation with excel, this is it.