defining TextToColumns with variable
 

I am trying to use TextToColumns but I want to define one of the
parameter entirely using a variable.

For example:

Selection.TextToColumns Destination:=Range("A5"),
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)), _
TrailingMinusNumbers:=True

replaced with something like:

varX = "Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))"
Selection.TextToColumns Destination:=Range("A5"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=varX, _
TrailingMinusNumbers:=True

but I cannot get this to work. Any ideas? Anything like eval() in
VBScript?

Thanks.

Edwards

defining TextToColumns with variable
 

Why not just

varX = Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))

without the quotes?

Tim

"Edward" wrote in message oups.com...
I am trying to use TextToColumns but I want to define one of the
parameter entirely using a variable.

For example:

Selection.TextToColumns Destination:=Range("A5"),
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)), _
TrailingMinusNumbers:=True

replaced with something like:

varX = "Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))"
Selection.TextToColumns Destination:=Range("A5"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=varX, _
TrailingMinusNumbers:=True

but I cannot get this to work. Any ideas? Anything like eval() in
VBScript?

Thanks.

Edwards

defining TextToColumns with variable
 

Tim Williams wrote:
Why not just

varX = Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))

without the quotes?

Tim

"Edward" wrote in message oups.com...
I am trying to use TextToColumns but I want to define one of the
parameter entirely using a variable.

For example:

Selection.TextToColumns Destination:=Range("A5"),
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)), _
TrailingMinusNumbers:=True

replaced with something like:

varX = "Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))"
Selection.TextToColumns Destination:=Range("A5"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=varX, _
TrailingMinusNumbers:=True

but I cannot get this to work. Any ideas? Anything like eval() in
VBScript?

Thanks.

Edwards


Thanks Tim! That did the trick.

Edward