Pasting Text (xl2000 sr1)

Topic

New Reply

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

Reply | Quote

Replies

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
[Fmr MS MVP - Word]

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote