Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TextToColumn when destination to other sheet | Excel Discussion (Misc queries) | |||
Split up | New Users to Excel | |||
How do I remove split a split window? | New Users to Excel | |||
Un-Split | Excel Discussion (Misc queries) | |||
Split a Value | Excel Discussion (Misc queries) |