Text to Columns in VBA
 

Excel 2002, WinXP
I am helping an OP with some VBA to manipulate some data. In the process
the code needs to do the Text-To-Columns task. I recorded the following
macro. The selection I chose has 7 pieces of comma separated text so this
macro works. But just on that entry. The actual entries have anything from
zero to ?? pieces of comma separated text. How do I code for a varying
number of comma separated text? Thanks for your time. Otto

Sub Macro2()
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo
_
:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
End Sub

Text to Columns in VBA
 

I see now that the macro will work with any number of text pieces regardless
of how many pieces of text were used in the recording. My question now is:
What is required in the macro and what isn't? Thanks for your time. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I am helping an OP with some VBA to manipulate some data. In the process
the code needs to do the Text-To-Columns task. I recorded the following
macro. The selection I chose has 7 pieces of comma separated text so this
macro works. But just on that entry. The actual entries have anything
from zero to ?? pieces of comma separated text. How do I code for a
varying number of comma separated text? Thanks for your time. Otto

Sub Macro2()
Selection.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
End Sub

Text to Columns in VBA
 

In xl2003, you can't exceed 256 columns when you're parsing the cell. So you
could just build an array with your requirements. But it looks like your first
field should be treated as Text. And the rest are General???

If yes, this may work (untested, uncompiled).

Dim myArray() As Variant
Dim iCtr As Long
Dim maxFields As Long

maxFields = 256 '256 columns maximum

ReDim myArray(1 To maxFields, 1 To 2)

'do the first field special
myArray(1, 1) = 1
myArray(1, 2) = 2 'Text

For iCtr = 2 To maxFields
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 1 'General
Next iCtr

Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=myArray, TrailingMinusNumbers:=True



Otto Moehrbach wrote:

Excel 2002, WinXP
I am helping an OP with some VBA to manipulate some data. In the process
the code needs to do the Text-To-Columns task. I recorded the following
macro. The selection I chose has 7 pieces of comma separated text so this
macro works. But just on that entry. The actual entries have anything from
zero to ?? pieces of comma separated text. How do I code for a varying
number of comma separated text? Thanks for your time. Otto

Sub Macro2()
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo
_
:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
End Sub

--

Dave Peterson

Text to Columns in VBA
 

Dave
Thanks for your response. I played around with what I had (before I saw
your post) and I found that the macro I recorded (7 pieces of text) works
with a selection that consists of any number of pieces of text, be it less
than 7 or more than 7. Now my question is what does the macro have to have
regarding that array and what does it not need? Thanks again for your time.
Otto
"Dave Peterson" wrote in message
...
In xl2003, you can't exceed 256 columns when you're parsing the cell. So
you
could just build an array with your requirements. But it looks like your
first
field should be treated as Text. And the rest are General???

If yes, this may work (untested, uncompiled).

Dim myArray() As Variant
Dim iCtr As Long
Dim maxFields As Long

maxFields = 256 '256 columns maximum

ReDim myArray(1 To maxFields, 1 To 2)

'do the first field special
myArray(1, 1) = 1
myArray(1, 2) = 2 'Text

For iCtr = 2 To maxFields
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 1 'General
Next iCtr

Selection.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=myArray, TrailingMinusNumbers:=True



Otto Moehrbach wrote:

Excel 2002, WinXP
I am helping an OP with some VBA to manipulate some data. In the process
the code needs to do the Text-To-Columns task. I recorded the following
macro. The selection I chose has 7 pieces of comma separated text so
this
macro works. But just on that entry. The actual entries have anything
from
zero to ?? pieces of comma separated text. How do I code for a varying
number of comma separated text? Thanks for your time. Otto

Sub Macro2()
Selection.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo
_
:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5,
1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
End Sub

--

Dave Peterson

Text to Columns in VBA
 

I would imagine that if you vary from the General format, then you'll want to
specify each field.

Otto Moehrbach wrote:

Dave
Thanks for your response. I played around with what I had (before I saw
your post) and I found that the macro I recorded (7 pieces of text) works
with a selection that consists of any number of pieces of text, be it less
than 7 or more than 7. Now my question is what does the macro have to have
regarding that array and what does it not need? Thanks again for your time.
Otto
"Dave Peterson" wrote in message
...
In xl2003, you can't exceed 256 columns when you're parsing the cell. So
you
could just build an array with your requirements. But it looks like your
first
field should be treated as Text. And the rest are General???

If yes, this may work (untested, uncompiled).

Dim myArray() As Variant
Dim iCtr As Long
Dim maxFields As Long

maxFields = 256 '256 columns maximum

ReDim myArray(1 To maxFields, 1 To 2)

'do the first field special
myArray(1, 1) = 1
myArray(1, 2) = 2 'Text

For iCtr = 2 To maxFields
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 1 'General
Next iCtr

Selection.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=myArray, TrailingMinusNumbers:=True



Otto Moehrbach wrote:

Excel 2002, WinXP
I am helping an OP with some VBA to manipulate some data. In the process
the code needs to do the Text-To-Columns task. I recorded the following
macro. The selection I chose has 7 pieces of comma separated text so
this
macro works. But just on that entry. The actual entries have anything
from
zero to ?? pieces of comma separated text. How do I code for a varying
number of comma separated text? Thanks for your time. Otto

Sub Macro2()
Selection.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo
_
:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5,
1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
End Sub

--

Dave Peterson

--

Dave Peterson

Text to Columns in VBA
 

Better...

You'll want to specify those fields that vary from General.

Otto Moehrbach wrote:

Dave
Thanks for your response. I played around with what I had (before I saw
your post) and I found that the macro I recorded (7 pieces of text) works
with a selection that consists of any number of pieces of text, be it less
than 7 or more than 7. Now my question is what does the macro have to have
regarding that array and what does it not need? Thanks again for your time.
Otto
"Dave Peterson" wrote in message
...
In xl2003, you can't exceed 256 columns when you're parsing the cell. So
you
could just build an array with your requirements. But it looks like your
first
field should be treated as Text. And the rest are General???

If yes, this may work (untested, uncompiled).

Dim myArray() As Variant
Dim iCtr As Long
Dim maxFields As Long

maxFields = 256 '256 columns maximum

ReDim myArray(1 To maxFields, 1 To 2)

'do the first field special
myArray(1, 1) = 1
myArray(1, 2) = 2 'Text

For iCtr = 2 To maxFields
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 1 'General
Next iCtr

Selection.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=myArray, TrailingMinusNumbers:=True



Otto Moehrbach wrote:

Excel 2002, WinXP
I am helping an OP with some VBA to manipulate some data. In the process
the code needs to do the Text-To-Columns task. I recorded the following
macro. The selection I chose has 7 pieces of comma separated text so
this
macro works. But just on that entry. The actual entries have anything
from
zero to ?? pieces of comma separated text. How do I code for a varying
number of comma separated text? Thanks for your time. Otto

Sub Macro2()
Selection.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo
_
:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5,
1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
End Sub

--

Dave Peterson

--

Dave Peterson

Text to Columns in VBA
 

Thanks Dave. Otto
"Dave Peterson" wrote in message
...
Better...

You'll want to specify those fields that vary from General.

Otto Moehrbach wrote:

Dave
Thanks for your response. I played around with what I had (before I
saw
your post) and I found that the macro I recorded (7 pieces of text) works
with a selection that consists of any number of pieces of text, be it
less
than 7 or more than 7. Now my question is what does the macro have to
have
regarding that array and what does it not need? Thanks again for your
time.
Otto
"Dave Peterson" wrote in message
...
In xl2003, you can't exceed 256 columns when you're parsing the cell.
So
you
could just build an array with your requirements. But it looks like
your
first
field should be treated as Text. And the rest are General???

If yes, this may work (untested, uncompiled).

Dim myArray() As Variant
Dim iCtr As Long
Dim maxFields As Long

maxFields = 256 '256 columns maximum

ReDim myArray(1 To maxFields, 1 To 2)

'do the first field special
myArray(1, 1) = 1
myArray(1, 2) = 2 'Text

For iCtr = 2 To maxFields
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 1 'General
Next iCtr

Selection.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=myArray, TrailingMinusNumbers:=True



Otto Moehrbach wrote:

Excel 2002, WinXP
I am helping an OP with some VBA to manipulate some data. In the
process
the code needs to do the Text-To-Columns task. I recorded the
following
macro. The selection I chose has 7 pieces of comma separated text so
this
macro works. But just on that entry. The actual entries have
anything
from
zero to ?? pieces of comma separated text. How do I code for a
varying
number of comma separated text? Thanks for your time. Otto

Sub Macro2()
Selection.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo
_
:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5,
1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
End Sub

--

Dave Peterson

--

Dave Peterson

Text to Columns in VBA
 

Otto Moehrbach wrote:
I see now that the macro will work with any number of text pieces regardless
of how many pieces of text were used in the recording. My question now is:
What is required in the macro and what isn't? Thanks for your time.

The following seems to work:

Range("A1:A4").Select
Selection.TextToColumns Destination:=Range("B1"), _
DataType:=xlDelimited, Comma:=True

Alan Beban

Text to Columns in VBA
 

I'm taking back my last answer.

Remember that excel likes to help by remembering the last options you chose. If
I want to make sure that each field is General (or whatever), I'd specify each
field the way I want. I wouldn't leave it up to what I thought were excel's
defaults.



Otto Moehrbach wrote:

Thanks Dave. Otto
"Dave Peterson" wrote in message
...
Better...

You'll want to specify those fields that vary from General.

Otto Moehrbach wrote:

Dave
Thanks for your response. I played around with what I had (before I
saw
your post) and I found that the macro I recorded (7 pieces of text) works
with a selection that consists of any number of pieces of text, be it
less
than 7 or more than 7. Now my question is what does the macro have to
have
regarding that array and what does it not need? Thanks again for your
time.
Otto
"Dave Peterson" wrote in message
...
In xl2003, you can't exceed 256 columns when you're parsing the cell.
So
you
could just build an array with your requirements. But it looks like
your
first
field should be treated as Text. And the rest are General???

If yes, this may work (untested, uncompiled).

Dim myArray() As Variant
Dim iCtr As Long
Dim maxFields As Long

maxFields = 256 '256 columns maximum

ReDim myArray(1 To maxFields, 1 To 2)

'do the first field special
myArray(1, 1) = 1
myArray(1, 2) = 2 'Text

For iCtr = 2 To maxFields
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 1 'General
Next iCtr

Selection.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=myArray, TrailingMinusNumbers:=True



Otto Moehrbach wrote:

Excel 2002, WinXP
I am helping an OP with some VBA to manipulate some data. In the
process
the code needs to do the Text-To-Columns task. I recorded the
following
macro. The selection I chose has 7 pieces of comma separated text so
this
macro works. But just on that entry. The actual entries have
anything
from
zero to ?? pieces of comma separated text. How do I code for a
varying
number of comma separated text? Thanks for your time. Otto

Sub Macro2()
Selection.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo
_
:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5,
1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
End Sub

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Text to Columns in VBA
 

Thanks Alan. That goes in my HowTo file. Otto
"Alan Beban" <unavailable wrote in message
...
Otto Moehrbach wrote:
I see now that the macro will work with any number of text pieces
regardless of how many pieces of text were used in the recording. My
question now is: What is required in the macro and what isn't? Thanks
for your time.

The following seems to work:

Range("A1:A4").Select
Selection.TextToColumns Destination:=Range("B1"), _
DataType:=xlDelimited, Comma:=True

Alan Beban

Text to Columns in VBA
 

Then you might as well tidy it up to eliminate the unnecessary selection :-)

Range("A1:A4").TextToColumns Destination:=Range("B1"), _
DataType:=xlDelimited, Comma:=True

Alan Beban

Otto Moehrbach wrote:
Thanks Alan. That goes in my HowTo file. Otto