Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    Wheaton, IL
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    After converting text to columns using either vba or the menus, excel 'remembers' what delimiters were used, so that if you paste in another 'chunk' of data, excel expects that you want the text to be spread out using the same delimiter(s). Where in the object model does it store that information and how can I override it in VBA?

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

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    I don't think you can easily retrieve the current settings, if at all.

    The Range object has a TextToColumns method. It has an argument DataType (xlDelimited or xlFixedWidth), and True/False arguments for Tab, Semicolon, Comma, Space and Other. Type TextToColumns in the Immediate window or in a module and press F1 to get online help about it.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    Hi,

    To override the existing delimiter settings, you'd simply change their values from true to false, or vice versa, as appropriate. For example, to set them all the delimiter options to true (they're false by default, but 'sticky' as you've discovered), you could use something like:

    Sub Test()
    Selection.TextToColumns DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
    Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:="*"
    End Sub

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    New Lounger
    Join Date
    May 2002
    Location
    Wheaton, IL
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    I know how to use the texttocolumns function. I am actually doing this in a macro. The problem is one I have noticed before. Within either an excel session (it may be limited to a workbook or even a worksheet), after a texttocolumns transformation has been performed, Excel remembers what delimiter was used to parse the text. If text is pasted from the clipboard after a texttocolumns transformation, Excel automatically (helpfully?) parses the pasted text using the same delimiter. A workaround for this is to walk thru the wizard, clear the delimiter and then cancel out of the wizard. I am trying to find out where excel saves the delimiters used in the most recent texttocolumns transformation. Even if you cancel out half way thru the wizard, excel remembers what was specified as the delimiter(s). It has to be somewhere in the object model, but I have yet to find it.

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

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    As far as I know, the current settings for TextToColumns are NOT exposed in the object model. There is nothing in the Object Browser that indicates they are.

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    Hi jr,

    Although you can't directly read the parsing settings, you could do so indirectly, with something like:

    Sub Test()
    Dim ParsingType As XlTextParsingType
    Dim TxtQualifier As XlTextQualifier
    Dim ConsecutiveBoolean As Boolean
    Dim TabBoolean As Boolean
    Dim SemicolonBoolean As Boolean
    Dim CommaBoolean As Boolean
    Dim SpaceBoolean As Boolean
    Dim OtherBoolean As Boolean
    Dim OtherCharString As String
    ParsingType = xlDelimited
    TxtQualifier = xlDoubleQuote
    ConsecutiveBoolean = True
    TabBoolean = True
    SemicolonBoolean = True
    CommaBoolean = True
    SpaceBoolean = True
    OtherBoolean = True
    OtherCharString = "*"
    Selection.TextToColumns DataType:=ParsingType, _
    TextQualifier:=TxtQualifier, ConsecutiveDelimiter:=ConsecutiveBoolean, _
    Tab:=TabBoolean, Semicolon:=SemicolonBoolean, Comma:=CommaBoolean, _
    Space:=SpaceBoolean, Other:=OtherBoolean, OtherChar:=OtherCharString
    End Sub

    With this approach, you can both set and interrogate the parsing settings via the corresponding variables.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    How can you retrieve the settings from variables that are local to a procedure? And even if you made the variables global, they wouldn't reflect changes applied manually in Data | Text to Columns.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    It is unfortunate that Excel, unlike Word, does not appear to let you retrieve the properties of a built-in dialog, since if you run Application.Dialogs(xlDialogTextToColumns) it has the previous options already set. Unfortunately, trying to get at dlg.OtherChar and other properties does not seem to work. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> Hopefully I have missed something?
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    I've never been able to make that work in Excel either <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    <hr>How can you retrieve the settings from variables that are local to a procedure<hr>
    Intercept the procedure, including the manual counterpart?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    Sorry, you've lost me.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    If, as I think was implied, the original import was done by VBA, then the values could be stored for later examination; but I don't know how useful this is if the later operations are done manually via the Excel interface (Copy/Paste etc)
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    Sorry to be so obscure, Hans. What I meant was that one could vba to replace the standard Excel Text-to-Columns Wizard. If the replacement was used with global variables, the delimiters could then be exposed to other vba modules as well (though a well-written Wizard might be callable in code - without showing the interface - as well as being capable of manual use).

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    OK, I see. Thanks for the explanation!

  15. #15
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel remembers texttocolumn delimiter (Excel VBA office2k win2k)

    <font color=blue> Revised by author; TextToColumns command simplified (FieldInfo removed) </font color=blue>
    JR
    I think that if you place some comma separated values in cells A1:A10 then run the following code, you will achieve the desired results.
    <pre>Option Explicit

    Sub Macro2()

    Range("A1:A10").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False


    Range("A11") = "AA"
    Range("A11").TextToColumns Destination:=Range("A11"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False

    Range("A1:A10").Copy
    Range("A11").Select
    ActiveCell.PasteSpecial xlPasteAll

    End Sub
    </pre>

    Regards
    Don

Posting Permissions

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