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

Solution 1

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).

Solution 2

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

Solution 3

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