vba TextToColumns FieldInfo not working

[gazingdown]

Hi

I want to set one of the columns as text (9th column, i.e. the I column) rather than general via vba code.

The code I used sets the FieldInfo to = Array(9, xlTextFormat)

However, vba seems to be converting them to date formats NOT text format.

anyone help?

actual code below

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(9, xlTextFormat)

 

[Andrew Poulsom]

It worked when I tried it.

What's the value of the xlTextFormat constant in the Object Browser. For me (Excel 2000) it's 2.

What does the data in column I look like?

 

[gazingdown]

Here are the 1st few rows of the workbook, comma separated.

Column 9 is coming out with dates where it can rather than text. I want to see 8-13 not 13/8/01 or -5 etc.

"EXPPUBISSU","EXPSEQISSUE","EXPDONOR","EXPCYCLE","EXPGAP","EXPMEDIA","EXPCAMPAGN","EXPPAYTYPE","EXPISSUES","EXPSUBZONE","EXPGRSSEXP","EXPNETEXP","EXPNETEXPP","EXPATTRTN","EXPATTRTNP","EXPRENEW","EXPGRSRATE","EXPNETRATE","EXPRNPAID","EXPRNPAIDP","EXPRNWAIT","EXPRNWAITP","EXPRNREJ","EXPRNREJP","EXPRESTART","EXPRESTARTP","EXPUNSMAN","EXPUNSMANP","EXPUNSAUT","EXPUNSAUTP","EXPUNSSYS","EXPUNSSYSP","EXPSNTRN1","EXPDDPCAN","EXPDDPCANP","EXPSNTRN2","EXPSNTRN3","EXPSNTRN4","EXPSNTRN5","EXPSNTRN6","EXPSNTRN7","EXPSNTRN8","EXPSNTRN9","EXPBAKRN1","EXPBAKRN2","EXPBAKRN3","EXPBAKRN4","EXPBAKRN5","EXPBAKRN6","EXPBAKRN7","EXPBAKRN8","EXPBAKRN9","EXPBAPRN1","EXPBAPRN2","EXPBAPRN3","EXPBAPRN4","EXPBAPRN5","EXPBAPRN6","EXPBAPRN7","EXPBAPRN8","EXPBAPRN9","EXPBAPRN1P","EXPBAPRN2P","EXPBAPRN3P","EXPBAPRN4P","EXPBAPRN5P","EXPBAPRN6P","EXPBAPRN7P","EXPBAPRN8P","EXPBAPRN9P","EXPACTPAID","EXPACTPADP","EXPACTGRC","EXPACTGRCP","EXPACTLAP","EXPACTLAPP","EXPACTCNR","EXPACTCNRP","EXPACTCAN","EXPACTCANP","EXPCANPND","EXPCANPNDP","EXPFUTURE","EXPFUTUREP","EXPLAPSED","EXPLAPSEDP","EXPCANNOR","EXPCANNORP","EXPCANIMM","EXPCANIMMP","EXPPREVLAP","EXPPRVLAPP","EXPPREV","EXPPREVP","EXPNEWEXP","EXPNEWEXPP","EXPTRANSF","EXPTRANSFP","EXPCANDD1","EXPCANDD1P","EXPCANDD2","EXPCANDD2P","EXPCONSUS","EXPCONSUSP","EXPDEFAULT","EXPDEFAULTP"
68,"779","MERGED","NEW","0","B","1","102","8-13","Zone 001","1","0",".00","1","100.00","0",".00",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0","0",".00","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0",".00",".00",".00",".00",".00",".00",".00",".00",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","1","100.00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00"
68,"779","MERGED","NEW","0","B","1","103","8-13","Zone 001","1","0",".00","1","100.00","0",".00",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0","0",".00","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0",".00",".00",".00",".00",".00",".00",".00",".00",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","1","100.00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00"
68,"779","MERGED","NEW","0","C","1","102","14-25","Zone 001","2","2","100.00","0",".00","2","100.00","100.00","2","100.00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","2","0",".00","2","2","0","0","0","0","0","0","0","2","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0",".00",".00",".00",".00",".00",".00",".00",".00",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","2","100.00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00"
68,"779","MERGED","NEW","0","J","1","102","8-13","Zone 001","1","0",".00","1","100.00","0",".00",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0","0",".00","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0",".00",".00",".00",".00",".00",".00",".00",".00",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","1","100.00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00","0",".00"

 

[Gates Is Antichrist]

I'm afraid I may have bad news. Take a look at my similar frustration in
http://www.mrexcel.com/board2/viewtopic.php?t=130209

In particular, note tusharm's remark "Changing the format after the fact is too late." and another reliable user concurred.

If things simply are as they were concluded in that thread, then this was and is a colossal f/u by Excel developers. I don't know ANY automated workaround.

OTOH my issue was with importing, and might not apply here.

 

[Andrew Poulsom]

It doesn't work if you just specify that column in the FieldInfo argumnent, but it worked for me if I specified all the columns up to that column:

Sub Test()
    Range("A1:A5").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, 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, 2))
End Sub

 

[gazingdown]

Does it still parse the remaining columns from 10 onwards?

I've another thread running that encounters a problem should you want to do all the columns!!! Do you get the same?

 

[Andrew Poulsom]

Does it still parse the remaining columns from 10 onwards?

Yup. Try it and see.