Text to Columns

[Simon]

I have a macro which parses a comma delimited string, but have just
discovered an issue (XL2000)...

The output from this macro is input for further processing BUT apparently the
default behaviour for Excel is that once the Text to Columns action has been
performed, to continue doing it... problem is, subsequent entries that
contain comma's are being automagickally parsed, with the result that some
data is then being overwritten (because it's parsed into the wrong columns).

is there some way to cancel this once the intial macro has been completed?

TIA

S

 

[okaizawa]

Hi,

how about doing 'TextToColumns' again. for instance,

Sub Init_TextToColumns()
Application.ScreenUpdating = False
With Workbooks.Add(xlWorksheet).Worksheets(1)
.Range("A1").Value = "a"
.Range("A1").TextToColumns Destination:=.Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False
.Parent.Close False
End With
Application.ScreenUpdating = True
End Sub


i know the setting of 'TextToColumns' and 'OpenText' affects pasting
text from clipboard. is there any other thing affected?

 

[Simon]

Hi,

how about doing 'TextToColumns' again. for instance,

Sub Init_TextToColumns()
Application.ScreenUpdating = False
With Workbooks.Add(xlWorksheet).Worksheets(1)
.Range("A1").Value = "a"
.Range("A1").TextToColumns Destination:=.Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False
.Parent.Close False
End With
Application.ScreenUpdating = True
End Sub


i know the setting of 'TextToColumns' and 'OpenText' affects pasting
text from clipboard. is there any other thing affected?

I'll give it a go (I had thought about this but thought that there should be
a better way...)

as far as I'm aware nothing else is affected - subsequent copy and paste is
being done by a bot and with some analysis done on the resulting data...

FWIW, I'm actually a little perplexed at this 'default' behaviour. I can't
for the life of me work out why you would want to continue parsing ad
nauseum... another example of an undocumented feature perhaps??

S

 

[okaizawa]

I'll give it a go (I had thought about this but thought that there should be
a better way...)

sorry, i don't know any way to handle this directly.
i don't think this feature to be very useful. i want some choices.