Thread: How to copy the selected text in a cell to the clipboard with VBA

Hi All;

For reasons relating to my efforts to write VBA code to paste an HTML table from a variable to a worksheet, I need to copy the text within a cell (but not the entire cell) to the clipboard. I find that if I do that manually using <CTRL> C and use code such as:

 Activesheet.paste :=Destination = Range("A5")

The HTML table gets pasted as a table with each cell in its proper worksheet cell. Copying and pasting the entire cell, only gets the HTML text reproduced.

So I have tried all I can think of to copy the HTML text to the clipboard. Here are some of my futile attempts, after having selected the text which I wish to copy from within the cell using:

Range("A1").activate  'that's where the text is
sendkeys "{F2}"  'places the insertion point at the end of the text in the cell
sendkeys "+{UP 500} 'there are CRLF characters in the text - continued Up arrows gets past them.
sendkeys "+{HOME"} - gets the selection to the beginning of the last line of text

Then, trying to copy the selected text ...

• Using Sendkeys to emulate <CTRL> C - nothing happens, no copying
• Using Range("A1").copy in several variations - "call rejected by callee" error message
• Cant easily call and work with the clipboard because Office 2007 has hidden the Forms2_0.dll reference (which used to contain the Clipboard Object Library) where I can't find it.
• Tried Selection.copy - same "call rejected by callee" error message as above

Any help with a new approach (or a working solution ) would be appreciated.
Thanks,

Stosh

Mayhap it would be a good idea if you uploaded a mock workbook with the HTML table for the forum to look at. That way it will be easier to understand what exactly you want the code to do.

You might try a dataobject - something like:

Dim objdata as msforms.dataobject
Set objdata = new msforms.dataobject
Objdata.settext activecell.value
Objdata.putinclipboard

To set the forms reference easily, just insert a userform. You can late bind it, but I can't remember the Guid offhand and I'm on an iPad currentl.