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))

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" <stitch45@NO_SPAM.blueyonder.co.uk> wrote in message
news:[email protected]...
> 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))
>

Thank you for your detailed explanation. Appreciate the help.


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:eLaS%[email protected]...
> 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" <stitch45@NO_SPAM.blueyonder.co.uk> wrote in message
> news:[email protected]...
>> 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))
>>
>
>