How to copy the contents of cell without control characters

aparna

New Member
Joined
Nov 4, 2005
Messages
16
Every time I copy the contents of an excel cell it copies the text box and some formating characters alongwith the contents.

Is there anyway I can copy the contents of a cell as pure text either through a macro or VBA.
 

AgentSmith

Well-known Member
Joined
Mar 8, 2004
Messages
575
In VBA the "Copy" command copies the cell itself, so you will get all the formatting and so on with it. However, you can use "PasteSpecial" with the parameter "xlValues" to simply paste the value, or "xlFormulas" to paste the formula as plain text.
 

AgentSmith

Well-known Member
Joined
Mar 8, 2004
Messages
575
Hang on a mo. I've just reread your query and it sounds like you want to take the value from a cell and drop it into a TextBox control on a UserForm. Is that correct? Here's how I'd do that:
Code:
TextBox1.Text = Range("cell").Value
Do either of these help?!
 

aparna

New Member
Joined
Nov 4, 2005
Messages
16
Thanks for your reply Mike. What I want to actually do is - copy the contents of the cell and drop it into an application like Word, without any control or special characters.
aparna
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,763
Office Version
365
Platform
Windows
aparna

Try this:

1. In Excel, copy the cell(s)
2. In Word, use Edit|Paste Special...|Unformatted Text
 

aparna

New Member
Joined
Nov 4, 2005
Messages
16
Thanks Peter, just wondering if there is anyway i can copy this as unformatted text so that i could drop it into an application that does not have paste-special features - like SAP or Oracle.
Aparna
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,763
Office Version
365
Platform
Windows
Is it just the contents of 1 cell? If so, is the cell value the result of a formula or directly entered characters?
 

AgentSmith

Well-known Member
Joined
Mar 8, 2004
Messages
575
I can't quite remember the code, but I used a "DataObject" before to do much the same thing. Just read the value from your cell and write it to the DataObject, then copy that.

I think it was "DataObject" anyway ...
 

AgentSmith

Well-known Member
Joined
Mar 8, 2004
Messages
575
Yep. It was:
Code:
Public Sub SetClipboardText(ByVal pText As String)
'Allows the user to put some text into the clipboard
    Dim d As DataObject
    
    Set d = New DataObject
    d.SetText pText
    
    d.PutInClipboard
End Sub
 

aparna

New Member
Joined
Nov 4, 2005
Messages
16
Hi Mike,
Thanks so much for the code... but I am not able to run this. How do I run this . I have pasted this as VBA code in the current sheet. But I am not able to see this either as a macro or a VBA application.
aparna
 

Forum statistics

Threads
1,078,013
Messages
5,337,728
Members
399,167
Latest member
Bellzebub

Some videos you may like

This Week's Hot Topics

Top