Have you ever needed to paste clipboard content to an excel sheet. Content in the clipboard may have copied from a web page, software, word file or text file etc. And content may be in any form. It may be a text, table, image and so forth. So in this post I will teach you how to do this using VBA.
Method 1
In this method, first you need to add reference to Microsoft Forms 2.0 Object Library. To do that go to VBA editor and click Tools menu and then click on References.
Then put a tick to Microsoft Forms 2.0 Object Library. However you will notice that Excel application automatically add reference to that library if you add form to your project.
Then add this code to a module. In this code, clipboard content is assigned to the SText variable. So at the end you can paste it to the place where you need. In this example content is pasted to the B2 cell of the activesheet.
|
Sub PasteToExcelFromClipboard() Dim DataObj As MSForms.DataObject Set DataObj = New MSForms.DataObject DataObj.GetFromClipboard SText = DataObj.GetText(1) ActiveSheet.Range("B2").Value = SText End Sub |
However this method will not work if you have an image in the clipboard. So if you want to deal with images then you can use this second method.
Method 2
Actually in this method we are using a very simple theory. We know that Ctrl + v is the shortcut keys to paste anything to the Excel sheet. So instead of doing this manually we can give that command through VBA like this.
|
Sub PasteToExcelFromClipboard_SendKeyMethod() activesheet. Range("B2").Select SendKeys "^v" End Sub |
First we select the B2 cell of the active sheet. Then we give the paste command using SendKeys method. Using this method you can even paste images, shapes and tables from the clipboard too.