Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pasting Text (xl2000 sr1)

    I have this handy macro that sums selected cells and puts the total into the clipboard using a shortcut key.:

    Sub SelectionSumCopy()
    Dim MyData As DataObject
    Set MyData = New DataObject
    MyData.SetText Application.WorksheetFunction.Sum(Selection)
    MyData.PutInClipboard
    End Sub

    I can then paste the total wherever I need it.

    The problem relates to a second macro I frequently use that uses the TextToColumns method with the decimal point as a separator. After using the second macro, the decimal point is set as a separator so that when I later paste the results of the SelectionSumCopy macro, it pastes the dollars in one cell, and the cents in the cell next to it.

    Is there some way to "unset" the separator character, or make it paste as a single number, or some other solution?

    Thanks,
    Ken

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    4,978
    Thanks
    2
    Thanked 398 Times in 328 Posts

    Re: Pasting Text (xl2000 sr1)

    Hi Ken,

    The problem may be with a property that's being set in your 'text to columns' macro. If you could post that it might help with finding a solution.

    Bear in mind though that, using the text to columns function in Excel without a macro exhibits the same sort of behaviour. If you paste some data into a worksheet and use the text to columns function with, say, a space separator, then copy and paste in some more data, Excel will helpfully apply the same text to columns function to the new data.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Text (xl2000 sr1)

    Thank you for the response. Seems the retention of TextToColumns delimiter characters is another MS feature that can be a nuisance. To resolve the problem I decided to use the principle that a program should clean up after itself. So in my macro that used the TextToColumns method, added at the end, I had it run a "dummy" run of the method by 1) forcing the selection to a single cell, 2) saving the contents in a variable, 3) putting a space character in the cell, 4) running the TextToColumns method using a space as the delimiter on the single cell, and 5) restoring the contents of the active cell. This resets the delimiter data in Excel so it ignores decimal points previously used.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Pasting Text (xl2000 sr1)

    A slightly more elegant method might be to check what the decimal point separator is set to at the beginning of the macro, run your code as required, and reset the separator back to its original setting at the end, using a variable (set variable = separator at start, set separator = variable at end).

    HTH
    Beryl M


  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Text (xl2000 sr1)

    Indeed a more elegant solution. The problem is that the delimiter is (as far as I can tell, and I'm no expert in VBA) a "sticky" argument, but not an accessible property. If there's a way to do what you suggest, I'd really like to know what it is. Reapplying the TextToColumns method with a different delimiter is the only way I've been able to get around it.

    Thanks,
    Ken

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    4,978
    Thanks
    2
    Thanked 398 Times in 328 Posts

    Re: Pasting Text (xl2000 sr1)

    Hi Ken,

    The delimiter property is accessible (see under TextToColumns in the vba help). Since you would have used 'Other' as you delimiter type, with the decimal point as the delimilter, the expression:
    Selection.TextToColumns Other:=False
    at the end of your Text To Columns macro should kill off Excel's 'helpful' TextToColumns conersion for the next paste.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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