How to copy the contents of cell without control characters

[aparna]

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]

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]

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:

TextBox1.Text = Range("cell").Value

Do either of these help?!

 

[aparna]

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]

aparna

Try this:

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

 

[aparna]

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]

Is it just the contents of 1 cell? If so, is the cell value the result of a formula or directly entered characters?

 

[AgentSmith]

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]

Yep. It was:

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]

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