TextToColumn Split
 

Below is part of a macro which splits a fixed width file into the required
columns, ColSplit being the variable for the split positions. When this is
imported into Excel how can I make all the fields be Text formatted as it is
formatting the columns as General at the moment and leading zeros are being
dropped.

Many thanks

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(ColSplit(1), 1),
Array(ColSplit(2), 1), Array(ColSplit(3), 1), Array(ColSplit(4), 1), _
Array(ColSplit(5), 1), Array(ColSplit(6), 1), Array(ColSplit(7), 1),
Array(ColSplit(8), 1), Array(ColSplit(9), 1), Array(ColSplit(10), 1), _
Array(ColSplit(11), 1), Array(ColSplit(12), 1))

TextToColumn Split
 

Stitch,

You are specifying the data type incorrectly.

Change all of the 1s in your statement in the Array(?, 1) to 2s (Array(?, 2).

1 tells Excel to treat the split as general format, 2 is text (actually, it is the constant
xlTextFormat, which is 2).

The valid values for the second value of the Array (XlColumnDataType) can be one of these
XlColumnDataType constants:
xlGeneralFormat. General
xlTextFormat. Text
xlMDYFormat. MDY Date
xlDMYFormat. DMY Date
xlYMDFormat. YMD Date
xlMYDFormat. MYD Date
xlDYMFormat. DYM Date
xlYDMFormat. YDM Date
xlEMDFormat. EMD Date
xlSkipColumn. Skip Column

These defined constants are values between 1 and 10.

You could write your code as

Array(?, 1) as Array(?, xlGeneralFormat)

and (Array(?, 2) as (Array(?, xlTextFormat).

HTH,
Bernie
MS Excel MVP


"Stitch45" wrote in message
.uk...
Below is part of a macro which splits a fixed width file into the required columns, ColSplit being
the variable for the split positions. When this is imported into Excel how can I make all the
fields be Text formatted as it is formatting the columns as General at the moment and leading
zeros are being dropped.

Many thanks

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(ColSplit(1), 1), Array(ColSplit(2), 1),
Array(ColSplit(3), 1), Array(ColSplit(4), 1), _
Array(ColSplit(5), 1), Array(ColSplit(6), 1), Array(ColSplit(7), 1), Array(ColSplit(8), 1),
Array(ColSplit(9), 1), Array(ColSplit(10), 1), _
Array(ColSplit(11), 1), Array(ColSplit(12), 1))

TextToColumn Split
 

Thank you for your detailed explanation. Appreciate the help.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Stitch,

You are specifying the data type incorrectly.

Change all of the 1s in your statement in the Array(?, 1) to 2s (Array(?,
2).

1 tells Excel to treat the split as general format, 2 is text (actually,
it is the constant xlTextFormat, which is 2).

The valid values for the second value of the Array (XlColumnDataType) can
be one of these XlColumnDataType constants:
xlGeneralFormat. General
xlTextFormat. Text
xlMDYFormat. MDY Date
xlDMYFormat. DMY Date
xlYMDFormat. YMD Date
xlMYDFormat. MYD Date
xlDYMFormat. DYM Date
xlYDMFormat. YDM Date
xlEMDFormat. EMD Date
xlSkipColumn. Skip Column

These defined constants are values between 1 and 10.

You could write your code as

Array(?, 1) as Array(?, xlGeneralFormat)

and (Array(?, 2) as (Array(?, xlTextFormat).

HTH,
Bernie
MS Excel MVP


"Stitch45" wrote in message
.uk...
Below is part of a macro which splits a fixed width file into the
required columns, ColSplit being the variable for the split positions.
When this is imported into Excel how can I make all the fields be Text
formatted as it is formatting the columns as General at the moment and
leading zeros are being dropped.

Many thanks

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 1), Array(ColSplit(1), 1),
Array(ColSplit(2), 1), Array(ColSplit(3), 1), Array(ColSplit(4), 1), _
Array(ColSplit(5), 1), Array(ColSplit(6), 1), Array(ColSplit(7),
1), Array(ColSplit(8), 1), Array(ColSplit(9), 1), Array(ColSplit(10), 1),
_
Array(ColSplit(11), 1), Array(ColSplit(12), 1))