0

I have some reports extracted from Oracle in text file. I have pasted the data in excel. Now my whole data is in Column A and I am trying to convert the data in tabular form. The delimiter in one cell in the text file is

--------- ------------- ------------- ---------------------- --------------- --------------------- ---- ------------------ ------------------ ------------------ ------------------

and based on this i have to convert my data. I have written the starting character number of each column from cells B1 to N1. While writing the VBA code , i want the vba to pick length of each column from these cells i.e(B1 to N1).Below is my code. Please help me where am I going wrong?

Sub texttocolumntest3()

    Columns("A:A").Select
    Range("A8").Activate
    Selection.Columns.AutoFit
    Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(Range("B1").Value, 1), Array(Range("c1").Value, 1), Array(Range("d1").Value, 1), Array(Range("e1").Value, 1), Array(Range("f1").Value, 1), _
        Array(Range("g1").Value, 1), Array(Range("h1").Value, 1), Array(Range("i1").Value, 1), Array(Range("j1").Value, 1), Array(Range("k1").Value, 1), Array(Range("l1").Value, 1), _
        Array(Range("m1").Value, 1)), TrailingMinusNumbers:=True
    Cells.Select
    Range("A11").Activate
    Selection.Columns.AutoFit
End Sub
Azma
  • 1
  • 1
  • The first argument of each array element is **NOT** the length of the column, rather [If the source data has fixed-width columns, the first element of each two-element array specifies the starting character position in the column (as an integer; 0 (zero) is the first character).](https://learn.microsoft.com/en-us/office/vba/api/excel.range.texttocolumns) – Ron Rosenfeld Mar 23 '19 at 11:59
  • 1
    Instead of pasting the data, why not importing it? Data->From text/CSV –  Mar 23 '19 at 12:22
  • If Im importing the data, the excel is still asking me to convert the data into tables and the text to column dialogue box opens. So I want to clean the whole data and convert to table through a click by using VBA. – Azma Mar 25 '19 at 05:56

0 Answers0