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