Problem with the method "TextToColumns" ... snif ...

[Rodie]

Hi all,

My first post ... My problem is that the method "TextToColumns" works well on Excel-VBA 2003, but not on 2000 !

The error specified is : "Execution Error '1004' - Error defined by the application or the object"

My code is :

Columns("A:A").Select
  Selection.TextToColumns Destination:=Range("A1"),   DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
  Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=":", _
  FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Moreover, I didn't succeed in finding what TextQualifier, FieldInfo and TrailingMinusNumbers mean here. Are they necessary ?

Thanks for your help. Best regards.
Rodie

 

[PHV]

Have you tried to press the F1 key when the cursor is inside TextToColumns ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[byundt]

The TrailingMinusNumbers refers to a method of indicating negative numbers using a - after the number 123-
This feature is not available in Excel 2000.

The TextQualifier is how the input data indicates that the information is text (not number or date). In Excel 2000, the preferred keyword is xlTextQualifierDoubleQuote. I don't know if it will accept your xlDoubleQuote keyword.

The FieldInfo says that you have two columns of information, both in General format.

Here is your code corrected for the keywords that Excel 2000 doesn't possess.

Columns("A:A").Select
  Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
  TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
  Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=":", _
  FieldInfo:=Array(Array(1, 1), Array(2, 1))