Paste from clipboard to Excel

[kweaver]

I want a macro to paste plain text into the current cell.
What I recorded was:

Sub PasteUnformattedText()
'
' PasteUnformattedText Macro
' Keyboard Shortcut: Ctrl+Shift+V

Selection.PasteSpecial Paste:=xlPasteValues

End Sub

This works fine when the clipboard contains a value from Excel.
But if the clipboard has formatted text from Word, let's say, it fails. What I want to paste is the CF_TEXT format from the Windows clipboard.

 

[Ricky918]

This worked for me

ActiveSheet.PasteSpecial Format:="Text"

 

[kweaver]

If I copy (CTRL+C) from Excel to the clipboard and incorporate that code into a macro, I get PasteSpecial method of Worksheet class failed.
I want the clipboard content, regardless of how it got there, copied into the active cell when I run the macro.
There seems to be a difference if you CTRL+C from Excel versus from Word, for example.

This generated the error:

Sub ToExcel()
ActiveSheet.PasteSpecial Format:="Text"
End Sub

Which probably is the case since there's no target for the paste, I suspect. But, I also think it has something to do with CTRL+C from Excel vs Word, as I said.

 

[Dave Patton]

I believe Paste1 will include the formatting.
N.B. There may be better solutions; I do not use VBA very much.

Sub Paste1()
ActiveSheet.Paste
End Sub
Sub Paste2()
ActiveSheet.PasteSpecial Format:="Text"
End Sub
Sub Paste3()
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
End Sub

 

[Jimmypop]

Try the following:

I am guessing that you would want to paste via a command button?

1. Create a command button on the worksheet where you would like the paste to happen.
2. Put code below in the command button code. Right click button and view code.

Private Sub CommandButton1_Click()
    Dim dFname As DataObject
    Dim strText As String
    On Error Resume Next
    Set dFname = New DataObject
    dFname.GetFromClipboard
    strText = dFname.GetText
    If Trim(strText) = "" Then
        MsgBox "Clipboard is empty...Please select text from source", vbOKOnly, "Message"
    Else
        ActiveSheet.Paste = strText
    End If
End Sub

3. Copy something from word and then select any cell ypou want in excel and then just click the button.

Let me know if this works for you.

 

[Yongle]

The code in post 5 @Jimmypop requires reference to Microsoft Forms Object Library be selected (in VBA editor under Tools \ References)

Annoyingly it is sometimes not listed under available references
- if so hit "Browse" button and you should find "FM20.dll" (version 2.0) in Windows\System32 (hopefully )
- another way is to insert a userform and a reference to Microsoft Forms Object Library is automatically selected

 

[Jimmypop]

Thanks for that one @Yongle did not even think to ask... ? ?