Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    resetting TextToColumns delimiters (Excel 2003, 2007)

    Aloha,

    When I use Excel's Text To Columns parsing function, whether manually or via code with TextToColumns, the delimiters I choose have an effect on any subsequent opening of textfiles and pasting of data. AArrrggghh! <img src=/S/aflame.gif border=0 alt=aflame width=16 height=16>

    Is there an easy way to reset the delimiters to the default of Tab=True and all others=False, or do I have to run back through the Text To Columns function to reset it? In a macro, do I have to use the TextToColumns function again?

    Mahalo,

    John Jacobson

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: resetting TextToColumns delimiters (Excel 2003, 2007)

    There is no "reset" method, so the workarounds you mention are the only ones. Here is a macro you can use. It temporarily fills an empty cell with a dummy value, calls TextToColumns for that cell with the default settings, then clears the cell again:
    <code>
    Sub ResetTextToColumns()
    Dim oCell As Range
    On Error Resume Next
    ' Find an empty cell
    Set oCell = ActiveSheet.Cells _
    .SpecialCells(xlCellTypeBlanks).Cells(1, 1)
    ' Enter a dummy value
    oCell.Value = "aaa"
    ' Call TextToColumns with the default settings
    oCell.TextToColumns Destination:=oCell, _
    DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, _
    Other:=False, FieldInfo:=Array(1, 1), _
    TrailingMinusNumbers:=True
    ' Clear the cell
    oCell.Clear
    End Sub</code>

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: resetting TextToColumns delimiters (Excel 2003, 2007)

    Thanks Hans, good info.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •