Error while Converting text file to excel

[SubhashG]

Hi,

I have one text file which have 4-5 columns.

Since I am new to VBA I am recording most of the codes.

Text file (report from oracle) columns are separated by spaces.

In some lines at column no. 4 or 5 if text is more than a limit the text going to next line which is otherwise blank.

When I am converting it to columns, text in second line related to 5 or 6th (extra text) columns coming to column 1.

a 11 b 222 c 33333 d 444444 e 55555
444
e 11 f 222 g 33333 h 44444 I 5555
5555
like the above example, when I am converting it to excel the second line text coming to column 1 (444 or 5555).

Please suggest.

thanks,
Subhash

 

[ThauThème]

Hi Subhash,

The code below dispatch all the numbers in column A in several columns beginning in column C according to the number of space (sorry for my frenchy english)...

Sub Macro1()
Dim O As Worksheet
Dim TC As Variant
Dim SN As Byte
Dim Max As Byte
Dim TL() As Variant
Dim I As Integer
Dim J As Byte
Dim K As Integer

Set O = Sheets("Sheet1")
TC = O.Range("A1").CurrentRegion
For I = 1 To UBound(TC, 1)
    SN = UBound(Split(TC(I, 1), " "))
    If SN > Max Then Max = SN
Next I
K = 1
For I = 1 To UBound(TC, 1)
    ReDim Preserve TL(1 To Max, 1 To K)
    SN = UBound(Split(TC(I, 1), " "))
    For J = 0 To SN - 1
        TL(J + 1, K) = Split(TC(I, 1), " ")(J)
    Next J
    K = K + 1
Next I
If K = 1 Then Exit Sub
O.Range("C1").Resize(UBound(TL, 2), UBound(TL, 1)) = Application.Transpose(TL)
End Sub