Check content of clipboard before pasting

Question

Is it possible to check content of clipboard before pasting it in Excel VBA

I have this today:

Sheets.Add After:=Sheets(Sheets.Count)   ' Create new sheet
ActiveSheet.Paste                        ' Paste from Clipboard
IsMultiLevel = (InStr(Range("A1"), "Multi-Level") > 0) ' Determine type of report
If Not IsMultiLevel Then
    MsgBox ("ERROR in Clipboard Data!!")
    End
Else
    ActiveSheet.Delete
End If

Bu I whish to check data before adding new sheet, then I dont need to delete it.. I want somthing like this

IsMultiLevel = (InStr([CLIPBOARD], "Multi-Level") > 0) ' Determine type of report
If Not IsMultiLevel Then
    MsgBox ("ERROR in Clipboard Data!!")
    End
End If
Sheets.Add After:=Sheets(Sheets.Count)   ' Create new sheet
ActiveSheet.Paste                        ' Paste from Clipboard

score 5 · Accepted Answer · answered Sep 12 '11 at 07:14

Coming from Excel 2003 I must say it is possible to examine the clipboard content by making use of the MSForms.DataObject. You first have to create a reference (VBA window tools / reference) to the Microsoft Forms 2.0 Object library (usually found at ...\system32\FM20.DLL).

Then you can read the clipboard into a text variable:

Dim BufObj As MSForms.DataObject, BufTxt as String

Set BufObj = New MSForms.DataObject
BufObj.GetFromClipboard
BufTxt = Buf.GetText

The buffer text will remain untouched (at least in Win XP/SP3, MS Office 2003 SP 3) and available for further use, i.e. the GetFromClipboard won't destroy the clipboard buffer. The thing to consider here is that the clipboard content is available "as text" so any graphic will be stored in a raw text mode. Also the buffer size needs to be considered, as a variable length string in Excel can hold not more than ca. 2^31 characters (but IMHO this should be enough for 90% of all needs).

score 5 · Answer 2 · answered Sep 12 '11 at 13:23

Thanks a lot Guys..! You are amazing! I just wanted to share my solution with you.

Function GetClipboardText(nChars As Integer) As String
    Dim BufObj As MSForms.DataObject
    Set BufObj = New MSForms.DataObject
    BufObj.GetFromClipboard
    GetClipboardText = Left(BufObj.GetText, nChars) ' Get only first nChars
End Function

Sub CreateOverviewSheet()
' Determine type of report in Clipboard
    IsMultiLevel = (InStr(GetClipboardText(100), "Multi-Level") > 0)
    IsConsolidated = (InStr(GetClipboardText(100), "Consolidated") > 0)
    If Not IsMultiLevel Or IsConsolidated Then
        MsgBox ("ERROR in Clipboard Data!!")
        End
    End If
    Sheets.Add After:=Sheets(Sheets.Count)   ' Create new sheet
    ActiveSheet.Paste                        ' Paste from Clipboard
    .
    .  and so on...
    .
End Sub

Great answer! Note: This needs a reference to `Microsoft Forms 2.0 Object Library`. If you don't see it come up see [here](https://stackoverflow.com/a/35610430/2529619). — ChaimG, Feb 28 '22 at 18:10

score 1 · Answer 3 · answered Sep 10 '11 at 23:41

1

Please understand that there is no way to look at the clipboard contents (aside from examining the list of available formats) without disturbing other apps. If the app that placed the data on the clipboard is using Delayed Rendering, you'll trigger an event that cannot be undone (the app will be forced to produce the data, and will expect that the user has pasted the data). This will also have implications for clipboard synchronization across network connections, such as with Remote Desktop. And any of my users (ClipMate) will be quite annoyed with you, and will probably add you to the "wall of shame" of apps that destructively pre-view clipboard data: http://www.thornsoft.com/faq/index.php?action=artikel&cat=9&id=79

answered Sep 10 '11 at 23:41

Chris Thornton

15,620
5
37
62

I don't agree, I am getting the Clipboard and use it in Excel VBA applications since long - see my answer – MikeD Sep 12 '11 at 07:14
It should be possible to paste the clipboard virtually into a variable/buffer somehow... :S – Aziz Sep 12 '11 at 07:45
1

-1 for "some users" / "ClipMate" / "wall of shame" ... this has nothing to do with the question and is misleading ... and the link which is probably an answer to a non-asked question and maybe more an ad than an answer. – MikeD Sep 13 '11 at 08:46
1

@Chris, I know I'm late to the party, but I disagree with some of the commenters, as a solution should **not** break other apps. I haven't used ClipMate, but have used other clipboard apps that broke Excel's clipboard usage. However, reading your link, I'm curious if fully reading the clipboard contents into an appropriate variable, then either 1) using the _variable_ to _manually_ "Paste" the data _instead_ of using the clipboard **or** 2) ignoring/clearing the variable would still be considered a _destructive_ preview and cause the problems you described? – GlennFromIowa Jun 24 '16 at 15:54
1

"reading the clipboard contents into an appropriate variable" is effectively a "Paste", as far as the windows clipboard API is concerned. – Chris Thornton Jun 29 '16 at 17:15

Check content of clipboard before pasting

3 Answers3

Linked