Thu 18 Sep 2008, 15:53Delegate Ferdous said...
Ferdous has attended:
Excel VBA Intro Intermediate course
Text to column
What is the correct syntaxt for applying text to column on Visual Basic?
Fri 26 Sep 2008, 12:19Trainer Laura said...
RE: Text to column
Hi Ferdous
Thanks for the question.
The easiest way to confirm this syntax is to record it but its not the most clear code.
A simple example of dividing text into 3 columns with commas seperating each item looked like:
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo :=Array(Array(1, 1), Array(2, 2), Array(3, 1)), _
TrailingMinusNumbers:=True
The most confusing part of it is the FieldInfo. It requires an array to be built with pairs of values for each column. The first value is the column number the second the data type.
Data Types are
1 = xlTextFormat. Text
2 = xlMDYFormat. MDY Date
3 = xlDMYFormat. DMY Date
4 = xlYMDFormat. YMD Date
5 = xlMYDFormat. MYD Date
7 = xlDYMFormat. DYM Date
8 = xlYDMFormat. YDM Date
9 = xlSkipColumn. Skip Column
I hope that helps. Let me know how you get on.
Laura GB
