Paste clipboard content to a certain cell (VBA)

[David2]

I'd like to create a macro that would paste clipboard content from notepad (text) into cell A1.

I've found some examples online however mostly for the other way around (cell to clipboard) and I can't seem to turn it the right way.

Thank you.

 

[VBA Geek]

Sub test()
Dim B As Object
Set B = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
B.GetFromClipboard
MsgBox B.GetText
End Sub

but if the txt file is saved on your drive then you can just read from it which is better instead of opening, copying the content and then using the clipboard

 

[David2]

Hello VBA Geek,

If I create a macro with the above content, I get a pop-up window that displays the clipboard content? It doesn't paste anything in the actual cell.

I'd like to Ctrl+C in notepad, then run macro in Excel and have A1 Cell filled with the clipboard content.

The TXT is not saved therefore I'd prefer to deal with clipboard.

 

[wideboydixon]

Public Sub ClipboardToA1()

Dim dataObj As MSForms.DataObject

' Set up the data object
Set dataObj = New MSForms.DataObject

' Error handler
On Error GoTo PasteFailed

' Get the data from the clipboard
dataObj.GetFromClipboard

' Get the clipboard contents
Range("A1").Value = dataObj.GetText(1)

' Label here to quit
PasteFailed:

End Sub

You'll need to add a reference to "Microsoft Forms 2.0 Object Library" from the Tools/References... menu.

WBD

 

[VBA Geek]

yes, of course you need to change

MsgBox B.GetText

to whatever suits your needs, so for example

range("A1").value = b.gettext

 

[wideboydixon]

Without adding the reference and using the CLSID provided by VBA Geek above:

Public Sub ClipboardToA1()

ClipboardToCell Range("A1")

End Sub
Private Sub ClipboardToCell(targetCell As Range)

Dim dataObj As Object

' Error handler
On Error GoTo PasteFailed

' Set up the data object
Set dataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

' Get the data from the clipboard
dataObj.GetFromClipboard

' Get the clipboard contents
targetCell.Value = dataObj.GetText(1)

' Label here to quit
PasteFailed:

End Sub

Also made it a little more reusable.

WBD

 

[David2]

wideboydixon,

This works however it seems I've made a mistake in regards to my data. When I choose A1 cell (one click, not da ouble click) and paste the data manually (Ctrl+V) into Excel the data itself spreads over a wide range of columns.

With the current solution, pasting directly into A1, data is of course distorted.

Could I set up a macro in a way that pasted clipboard content would "start" at A1 and then automatically use up as many columns as needed? Or do I have to make it a fixed range?

VBA Geek,

OK I'll change that and test however I'd still need to fix the range as mentioned above.


Thanks to you both.

 

[wideboydixon]

Ha! Not quite what you originally asked for. It becomes a bit simpler in this case:

Public Sub PasteInA1()

Range("A1").Select
ActiveSheet.Paste

End Sub

WBD

 

[David2]

Exactly what I wanted! Thanks!

 

[David2]

Ha! Not quite what you originally asked for. It becomes a bit simpler in this case:

Public Sub PasteInA1()

Range("A1").Select
ActiveSheet.Paste

End Sub

WBD

In need of some troubleshooting

Using the above code, I sometimes get a 1004 error, " Cannot change part of a merged cell ", debug pointing at "ActiveSheet.Paste" row.

I'm using this macro via command button so I am not selecting any cells.

Any ideas what's wrong? This only happens on some computers but I wasn't able to pinpoint a particular reason.