Text to columns -- reset delimiters, etc

[Myrna Larson]

If you have used Data/Text to Columns, Excel remembers what your settings were
for delimiters, etc.

Here's my predicament. I had used the Text to Columns on some data that I had
pasted into a worksheet from a newsgroup message, using the space delimiter.

Then I closed that workbook and opened one of my own workbooks without
restarting Excel in between.

In my workbook, I need to paste some tab-delimited data that I copy to the
clipboard in MS Money. Normally, I just copy the data in Money, switch to
Excel, and press CTRL+V to paste. I don't have to use Text to Columns at this
point.

Tonight, the parsing was all messed up because and XL "remembered" that I had
set the Text to Columns delimiter to a space, and it was using those same
settings to parse the new data pasted from the clipboard.

I fixed the problem by closing Excel, restarting, opening the workbook again,
and pasting.

Is there a way, in code, to re-set Text to Columns back to the defaults, or
just to change the delimiter, other than by opening a text file? I can't find
any references to these settings other than the arguments to the OpenText
method.

Or do I have to restart Excel? Or stop messing around with data from newgroup
messages <g>?

 

[Myrna Larson]

I came up with this. It's called from my Workbook_Open routine. It seems like
a kludge. Does anybody have a better solution?

Sub SetDelimiters()
Dim Rng As Range
Set Rng = Worksheets("Sheet1").Range("Dummy") 'a normally empty cell
With Rng
.Value = "XXX"
.TextToColumns Destination:=Rng, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False
.ClearContents
End With
End Sub