TextToColumns FieldInfo ignored

[as1981]

All,

I am using the following code:


Set xlbInput = xlaExcel.Workbooks.open(strDetailList(2) & "\" & Left(filFiles.Name, Len(filFiles.Name) - 4) & "." & strDetailList(4))

Set xlwInput = xlbInput.Worksheets(1)
Set rngInput = xlwInput.Range("A1")
Log "Before phone reports row loop"
Do While rngInput.value <> "Totals:" And rngInput.Row <= xlbInput.ActiveSheet.UsedRange.rows.Count
If rngInput.value <> "" Then
rngInput.Cells.TextToColumns , dataType:=xlDelimited, TextQualifier:=xlTextQualifierNone, ConsecutiveDelimiter:=False, Tab:=CBool(strValueArray(0)), Semicolon:=CBool(strValueArray(1)), Comma:=CBool(strValueArray(2)), Space:=CBool(strValueArray(3)), Other:=CBool(strValueArray(4)), FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 2), Array(72, 1), Array(73, 1)), TrailingMinusNumbers:=True
End If
Set rngInput = xlwInput.Range("a" & rngInput.Row + 1)
Loop


Column 71 is a telephone number so I set the field type to 2. However it still gets formatted as general. I suspect the fieldinfo is being ignored.

Can anyone see a problem with the statement? The file being opened is a csv file.

Thanks

[Paul_Hossler]

Maybe the data?

If you attach a small sample CSV it will be easier, or at least show what the 71st field looks like

[as1981]

Hello,

Thanks for your help. I've attached an example file. I also forgot to state that the only delimiter that is set to true is commas. Everything else is set to false.

Thanks

[Paul_Hossler]

Might just be easier to load the file, and then format the 71st column the way you want

I've had to read a CSV file line by line using LINE INPUT, SPLIT it at the comma, and do each piece separately. Slower, but it gave me the fine control I needed for something

[as1981]

Thanks for your help. I read it line by line and split it as you suggested and that works.