TextToColumn Split

  • Thread starter Thread starter Stitch45
  • Start date Start date
S

Stitch45

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
 
Back
Top