.TextToColumns doesn't seem to work reliably.

[gmaxey]

Hi Excel Gurus,

This issue is related to an earlier post "Suggestion for Best Practice"
http://www.vbaexpress.com/forum/showthread.php?65633-Suggestion-for-Best-Practice

I can't seem to be able to make .TextToColumns work consistently. The following code provides an example of the issue. Just paste it in a new workbook:

When you first run it, it performs exactly as I would expect. The text in rows 11, 12 and 13 delimited using a tab is split between columns B and C. No other text in columns 1 - 10 is moved to column C

However if Line 1 is stetted and Line 2 is unstetted and the code is run. It all falls apart.

Hoping someone can help me understand what is happening and show how split only text delimited with a tab in rows (after row 9) between columns B and C.

Thanks.



Sub DEMOISSUE()
Dim lngCC As Long, lngIndex As Long
Dim varCCs, varAttrs, varTitles
Dim oCol

ReDim varCCs(0)
ReDim varAttrs(9)
varAttrs(0) = "123456"
varAttrs(1) = "3"
'Here are three scenarios
1 varAttrs(2) = "My Title" 'Works perfectly. The text delimited in column B with a tab (rows 11, 12 and 13) is split between column B and C
'Stet line 1 and unstet line 2.
2 'varAttrs(2) = "My Title One Two Three" 'Falls apart. Text in rows 3 and 7 are split between columns B, C and D in some seemingly random
'fashion. The text in rows 11, 12 and 13 are not split at all!!
'Set the line above and unstet the next line.
3 'varAttrs(2) = "My Title Three Four Seven" 'Works perfectly. The tab delimited text in 11, 12 and 13 in column B again is split between column B and C ???
varAttrs(3) = "My Tag"
varAttrs(4) = "True"
varAttrs(5) = "True"
varAttrs(6) = "Choose an Item" 'This is a normal condition.
varAttrs(7) = " "
varAttrs(8) = " "
varAttrs(9) = "PHT" & vbTab & "" & vbLf
For lngIndex = 1 To 3
varAttrs(9) = varAttrs(9) & "A" & vbTab & "Alpha" & vbLf
Next lngIndex
varAttrs = Split(Join(varAttrs, vbLf), vbLf)
varCCs(0) = varAttrs
If Not Sheets("Sheet1").Range("A1").Value = "Content control ID" Then
'Clear the sheet and add legend.
Sheets("Sheet1").Cells.ClearContents
varTitles = Split("Content control ID|Type|Title|Tag|Contents cannot be edited|Content control cannot be deleted|Placeholder Text" _
& "|Temporay/BB Gal/Date Format|Multi-Line/BB Cat|List Entries", "|")
Sheets("Sheet1").Cells(1, 1).Resize(UBound(varTitles) + 1).Value = Application.Transpose(varTitles)
Else
'Clear sheet except for legend.
Sheets("Sheet1").UsedRange.Offset(, 2).ClearContents
End If
'Write the CC data to the sheet.
For lngCC = 0 To UBound(varCCs)
'Data is writen to single columns separated by an empty column
With Sheets("Sheet1").Cells(1, 2 + 2 * lngCC).Resize(UBound(varCCs(lngCC)) + 1)
.Value = .Application.Transpose(varCCs(lngCC))
'Split the list entries delimited with vbTab into two columns.
.TextToColumns Tab:=True, Space:=False
End With
Next
Sheets("Sheet1").Columns.AutoFit
'Remove remaining empty columns.
For lngIndex = Sheets("Sheet1").UsedRange.Columns.Count To 1 Step -1
Set oCol = Sheets("Sheet1").UsedRange.Cells(1, lngIndex).EntireColumn
If Application.WorksheetFunction.CountA(oCol) = 0 Then
oCol.Delete
End If
Next lngIndex
lbl_Exit:
Exit Sub
End Sub

[gmaxey]

Okay guys, I think I figured this one out on my own. I set the datatype and it now works.

.TextToColumns DataType:=1, Tab:=True

Still would be interested in understanding why it behaved as it did under the three scenarios.

[Artik]

Why in the second scenario Excel chose xlFixedWidth by default, I don't know that. I can only say that, just like in the Find method, you must set ALL parameters as we expected. Both methods remember the settings from the previous use. Parameters not mentioned in the method call have default values (when you start the application for the first time) or values from the previous use.

Artik

[gmaxey]

Artik,

Thanks. Excel VBA is new and still a bit mysterious to me.

[Paul_Hossler]

I can't seem to be able to make .TextToColumns work consistently. The following code provides an example of the issue. Just paste it in a new workbook:



It works EXACTLY as programmed :)

It might be useful to just make a simple Excel macro and WB with various kinds of test data to verify the macro is dividing the text the way you want

[gmaxey]

Paul,

Yes, I'll eat that crow but still; considering the example lines 2 and 3 in the above, programed wrong as it was, why does it split rows 3 and 7 (and not rows 11-13) in example 2 and split rows (11-13) as I intended in example 3?


2 'varAttrs(2) = "My Title One Two Three" 'Falls apart. Text in rows 3 and 7 are split between columns B, C and D in some seemingly random
'fashion. The text in rows 11, 12 and 13 are not split at all!!
'Set the line above and unstet the next line.
3 'varAttrs(2) = "My Title Three Four Seven" 'Works perfectly. The tab delimited text in 11, 12 and 13 in column B again is split between column B and C ???

[Paul_Hossler]

I think you were just too terse with specifying the parameters for .TextToColumns. Artik may have been right that some parameters were persistent

I ran Case 2, but stopped it before the TextToColumns, and then recorded a macro to do the split.

I generalized the macro and integrated it




Option Explicit
Sub DEMOISSUE()
Dim lngCC As Long, lngIndex As Long
Dim varCCs, varAttrs, varTitles
Dim oCol

ReDim varCCs(0)
ReDim varAttrs(9)

varAttrs(0) = "123456"
varAttrs(1) = "3"

'Here are three scenarios
'Case 1
' varAttrs(2) = "My Title" 'Works perfectly. The text delimited in column B with a tab (rows 11, 12 and 13) is split between column B and C

'Stet line 1 and unstet line 2.
'Case 2
varAttrs(2) = "My Title One Two Three" 'Falls apart. Text in rows 3 and 7 are split between columns B, C and D in some seemingly random
'fashion. The text in rows 11, 12 and 13 are not split at all!!
'Set the line above and unstet the next line.

'Case 3
' varAttrs(2) = "My Title Three Four Seven" 'Works perfectly. The tab delimited text in 11, 12 and 13 in column B again is split between column B and C ???

varAttrs(3) = "My Tag"
varAttrs(4) = "True"
varAttrs(5) = "True"
varAttrs(6) = "Choose an Item" 'This is a normal condition.
varAttrs(7) = " "
varAttrs(8) = " "
varAttrs(9) = "PHT" & vbTab & "" & vbLf

For lngIndex = 1 To 3
varAttrs(9) = varAttrs(9) & "A" & vbTab & "Alpha" & vbLf
Next lngIndex

'PHH added <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
varAttrs(9) = Left(varAttrs(9), Len(varAttrs(9)) - 1)



varAttrs = Split(Join(varAttrs, vbLf), vbLf)

varCCs(0) = varAttrs

If Not Sheets("Sheet1").Range("A1").Value = "Content control ID" Then
'Clear the sheet and add legend.
Sheets("Sheet1").Cells.ClearContents
varTitles = Split("Content control ID|Type|Title|Tag|Contents cannot be edited|Content control cannot be deleted|Placeholder Text" _
& "|Temporay/BB Gal/Date Format|Multi-Line/BB Cat|List Entries", "|")
Sheets("Sheet1").Cells(1, 1).Resize(UBound(varTitles) + 1).Value = Application.Transpose(varTitles)

Else
'PHH - Clear sheet except for legend.<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Sheets("Sheet1").UsedRange.Offset(, 1).ClearContents
End If

'Write the CC data to the sheet.
For lngCC = 0 To UBound(varCCs)

'original --------------------------------------------------------------------------
' With Sheets("Sheet1").Cells(1, 2 + 2 * lngCC).Resize(UBound(varCCs(lngCC)) + 1)
'
' MsgBox .Address
'
' .Value = .Application.Transpose(varCCs(lngCC))
' 'Split the list entries delimited with vbTab into two columns.
' .TextToColumns Tab:=True, Space:=False
' End With

'modified ----------------- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
With Sheets("Sheet1")
.Cells(1, 2 + 2 * lngCC).Resize(UBound(varCCs(lngCC)) + 1).Value = .Application.Transpose(varCCs(lngCC))
'PHH changed - Split the list entries delimited with vbTab into two columns.
.Columns(2 + 2 * lngCC).TextToColumns DataType:=xlDelimited, Tab:=True
End With
Next

Sheets("Sheet1").Columns.AutoFit

'Remove remaining empty columns.
For lngIndex = Sheets("Sheet1").UsedRange.Columns.Count To 1 Step -1
Set oCol = Sheets("Sheet1").UsedRange.Cells(1, lngIndex).EntireColumn

If Application.WorksheetFunction.CountA(oCol) = 0 Then
oCol.Delete
End If
Next lngIndex
lbl_Exit:
Exit Sub
End Sub





Edit:


Sheets("Sheet1").UsedRange.Offset(, 1).ClearContents


Edit 2:

I think the most impactful change was explicitly setting DataType. The default seems to be xlFixed


.Columns(2 + 2 * lngCC).TextToColumns DataType:=xlDelimited, Tab:=True

Edit 3: Forgot to include screen shot - this is 'default' TextToColumn of your data to show have the fixed length splits

24800

[Paul_Hossler]

Sorry for the 3 updates -- was having a bad day

[gmaxey]

Paul,

Yes it think DataType:=xlDelimited

was key.

Still a little confounded that when it was "wrong" it still seemed right with case 3 but not case 2 but all mysteries can't be fathomed by all people.

Thanks.