Text to Columns in VBA

[Otto Moehrbach]

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

 

[Otto Moehrbach]

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

 

[Dave Peterson]

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]

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]

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

 

[Dave Peterson]

Better...

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

 

[Otto Moehrbach]

Thanks Dave. Otto

Better...

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

 

[Alan Beban]

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

 

[Dave Peterson]

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.

Thanks Dave. Otto

 

[Otto Moehrbach]

Thanks Alan. That goes in my HowTo file. Otto

The following seems to work:

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

Alan Beban

 

[Alan Beban]

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