You can access and retrieve the data in the clipboard into a variable in Excel VBA, using the Microsoft Forms library. On top of copy and paste the code below, you must follow the these steps and activate Microsoft Forms 2.0 Object Library for the code to work. You only need to do it once as long as this macro stays in the same file.
- On the Macro editor menu bar on the top, click on “Tools”
- In the menu, click on “References”
- Click the “Microsoft Forms 2.0 Object Library”
- Press OK
Public Clipboard As New MSForms.DataObject Sub readClipboard() 'Tools -> References -> Microsoft Forms 2.0 Object Library 'of you will get a "Compile error: user-defined type not defined" Dim DataObj As New MSForms.DataObject Dim S As String DataObj.GetFromClipboard S = DataObj.GetText Debug.Print S 'print code in the Intermediate box in the Macro editor End Sub