EXCEL Automation and TextToColumn

[BazRat]

Hope someone here can help. I am writing a VB app to automate formatting of an exported excel raw data document from an application. All is going well until I need to do a TextToColumn format on a column to change the date format from text format to date format e.g. 11.02.2012 to 11/02/2012. Here is a snippet of the code that I am using:[XCODE]With xlsApp
.Selection.TextToColumns( _
Destination:=.Range("F:F"), _
DataType:=Excel.XlTextParsingType.xlDelimited, _
TextQualifier:=Excel.XlTextQualifier.xlTextQualifierDoubleQuote, _
ConsecutiveDelimiter:=False, _
TAB:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Excel.XlColumnDataType.xlDMYFormat, _
TrailingMinusNumbers:=False)
EndWith
[/XCODE]The Excel VBA equivalent is "Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo :=Array(1, 4), TrailingMinusNumbers:=True" which works perfectly. For some reason the "FieldInfo" in the VB.NET code is being ignored. I have done some experiments with the FieldInfo removed on different data (comma separated data) and this works perfectly. As soon as the FieldInfo is added the code fails to make any changes to my formatting. Can anyone point out what I might be doing wrong?

 

[22-degrees]

You need to specify the column number when you are using FieldInfo

that segment should look like so in your case:

FieldInfo:={6, Excel.XlColumnDataType.xlDMYFormat}

in this case "6" is your column number "F".

 

[BazRat]

You need to specify the column number when you are using FieldInfo

that segment should look like so in your case:

FieldInfo:={6, Excel.XlColumnDataType.xlDMYFormat}

in this case "6" is your column number "F".

That worked..... excellent... many thanks for your help.