Data in clipboard

[Formula11]

Is there any way to get information for data in clipboard, before pasting into Excel.
This could be things like:
- First element value
- Size of range if it's a table

Is there any information to get started on this?
Not really sure what the clipboard looks like

 

[Formula11]

Add:
- If data is from Excel?

 

[JEC]

To see what is in your clipboard, you can hit Windows + V (activate first in windows 11)

 

[Formula11]

Thanks JEC, but I need to look at data in Visual Basic.

I found this which is useful for checking first part of the clipboard.
VBA Check Clipboard Text Before Pasting

One of the other tests I need is to see if data in clipboard is from Excel or external program, no luck so far.

 

[JEC]

a clipboard does not store where the data comes from, so I don't think you will be able to see if it is actually from Excel

 

[Formula11]

OK thanks, I was thinking a range address may be stored as a guess, anyway thanks for looking into

 

[Jaafar Tribak]

One of the other tests I need is to see if data in clipboard is from Excel or external program, no luck so far.

Actually, checking the CutCpyMode Property will tell you if the clipboard data is coming from the excel or from outside excel.

You can define a simple Boolean function as follows:

Public Function DoesClipBoardDataComeFromExcel() As Boolean
    DoesClipBoardDataComeFromExcel = CBool(Application.CutCopyMode)
End Function

The above is simple but will only work if the content of the clipboard data is text ... For clipboard data such as images & shapes you will need more elaborated code but it can be done with the windows API.

 

[Formula11]

Thanks again Jaafar, this works

 

[Jaafar Tribak]

For future reference, in case someone is looking for an answer and for the sake of completness while we are on the subject, I am posting here the win32 api code for checking if the clipboard data comes from excel or from an outside program and for working with any clipboard format (not just text) hence overcoming the limitations of the Application.CutCopyMode Property which can't tell if a worksheet object such a shape is in the clipboard or if some text was copied from comments, ... etc .

I have placed the api calling code in the workbook Auto _ Open legacy macro so that the entire code is kept within the separate standard module and so that it takes effect upon opening the workbook .


1- In a Standard Module:

Option Explicit

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    #Else
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    #End If
    Private Declare PtrSafe Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As LongPtr, ByVal hwnd As LongPtr, ByVal Msg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    Private Declare PtrSafe Function CreateWindowEx Lib "user32" Alias "CreateWindowExA" (ByVal dwExStyle As Long, ByVal lpClassName As String, ByVal lpWindowName As String, ByVal dwStyle As Long, ByVal x As Long, ByVal y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal hWndParent As LongPtr, ByVal hMenu As LongPtr, ByVal hInstance As LongPtr, lpParam As Any) As LongPtr
    Private Declare PtrSafe Function DestroyWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function GetClipboardSequenceNumber Lib "user32" () As Long
    Private Declare PtrSafe Function AddClipboardFormatListener Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function RemoveClipboardFormatListener Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function CountClipboardFormats Lib "user32" () As Long
    Private Declare PtrSafe Function SetProp Lib "user32" Alias "SetPropA" (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal hData As LongPtr) As Long
    Private Declare PtrSafe Function GetProp Lib "user32" Alias "GetPropA" (ByVal hwnd As LongPtr, ByVal lpString As String) As LongPtr
    Private Declare PtrSafe Function RemoveProp Lib "user32" Alias "RemovePropA" (ByVal hwnd As LongPtr, ByVal lpString As String) As LongPtr
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
#Else
    Private Enum LongPtr
        [_]
    End Enum
    Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As LongPtr, ByVal hwnd As LongPtr, ByVal Msg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    Private Declare Function CreateWindowEx Lib "user32" Alias "CreateWindowExA" (ByVal dwExStyle As Long, ByVal lpClassName As String, ByVal lpWindowName As String, ByVal dwStyle As Long, ByVal x As Long, ByVal y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal hWndParent As LongPtr, ByVal hMenu As LongPtr, ByVal hInstance As LongPtr, lpParam As Any) As LongPtr
    Private Declare Function DestroyWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare Function GetClipboardSequenceNumber Lib "user32" () As Long
    Private Declare Function AddClipboardFormatListener Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare Function RemoveClipboardFormatListener Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare Function CountClipboardFormats Lib "user32" () As Long
    Private Declare Function SetProp Lib "user32" Alias "SetPropA" (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal hData As LongPtr) As Long
    Private Declare Function GetProp Lib "user32" Alias "GetPropA" (ByVal hwnd As LongPtr, ByVal lpString As String) As LongPtr
    Private Declare Function RemoveProp Lib "user32" Alias "RemovePropA" (ByVal hwnd As LongPtr, ByVal lpString As String) As LongPtr
    Private Declare Function GetActiveWindow Lib "user32" () As LongPtr
#End If


Public Function DoesClipBoardDataComeFromExcel() As Boolean
    If CountClipboardFormats Then
        DoesClipBoardDataComeFromExcel = CBool(GetProp(Application.hwnd, "DataSource"))
    End If
End Function


'_______________________________________ PRIVATE ROUTINES __________________________________________________

Private Sub Auto_Open()
    Call CreateClipWindow
End Sub

Private Sub Auto_Close()
    Call CleanUp
End Sub


Private Sub CreateClipWindow()
    Const HWND_MESSAGE = -3&
    Dim hClip As LongPtr
    If GetProp(Application.hwnd, "Clip") = 0 Then
        hClip = CreateWindowEx(0&, "Static", vbNullString, 0&, 0&, 0&, 0&, 0&, HWND_MESSAGE, 0, 0, ByVal 0&)
        Call SetProp(Application.hwnd, "Clip", hClip)
        Call AddClipboardFormatListener(hClip)
        Call SubClassClipBoardWatcherWindow(hClip)
    End If
End Sub

Private Sub CleanUp()
    Call RemoveClipboardFormatListener(GetProp(Application.hwnd, "Clip"))
    Call SubClassClipBoardWatcherWindow(GetProp(Application.hwnd, "Clip"), False)
    Call DestroyWindow(GetProp(Application.hwnd, "Clip"))
    Call RemoveProp(Application.hwnd, "Clip")
    Call RemoveProp(Application.hwnd, "DataSource")
End Sub
 
Private Sub SubClassClipBoardWatcherWindow( _
    ByVal hwnd As LongPtr, _
    Optional ByVal bSubclass As Boolean = True _
)
    Const GWL_WNDPROC = (-4&)
    If bSubclass Then
        If GetProp(Application.hwnd, "PrevProcAddr") = 0 Then
            Call SetProp(Application.hwnd, "PrevProcAddr", _
            SetWindowLong(hwnd, GWL_WNDPROC, AddressOf ClipBoardWindowCallback))
        End If
    Else
        If GetProp(Application.hwnd, "PrevProcAddr") Then
            Call SetWindowLong(hwnd, GWL_WNDPROC, GetProp(Application.hwnd, "PrevProcAddr"))
            Call RemoveProp(Application.hwnd, "PrevProcAddr")
        End If
    End If
End Sub

Private Function ClipBoardWindowCallback( _
    ByVal hwnd As LongPtr, _
    ByVal uMsg As Long, _
    ByVal wParam As LongPtr, _
    ByVal lParam As LongPtr _
) As LongPtr

    Const WM_CLIPBOARDUPDATE = &H31D
    Static lPrevSerial As Long
 
    Call SubClassClipBoardWatcherWindow(hwnd, False)
    If uMsg = WM_CLIPBOARDUPDATE Then
        If lPrevSerial <> GetClipboardSequenceNumber Then
            Call SetProp(Application.hwnd, "DataSource", IIf(GetActiveWindow = Application.hwnd, -1, 0))
            lPrevSerial = GetClipboardSequenceNumber
        End If
    End If
    Call SubClassClipBoardWatcherWindow(hwnd, True)
    ClipBoardWindowCallback = CallWindowProc(GetProp(Application.hwnd, "PrevProcAddr"), hwnd, uMsg, wParam, lParam)
End Function

2- Usage Example:

Sub Test()
    If DoesClipBoardDataComeFromExcel Then
        ActiveSheet.Paste
    Else
        MsgBox "You can't paste data coming from outside excel!", vbExclamation
    End If
End Sub

 

[Dan_W]

If you look into it, there is quite of lot of data and detail being saved everytime we use the clipboard. When you copy a range to the clipboard, for example, the data is saved in a variety of different formats, include one called "XML Spreadsheet". This converts the range and the data it contains, into an XML string:

Interestingly, there also appears to be a clipboard format which records the size of the copied area:

The program InsideClipboard is a helpful program for working out what is going on in the spreadsheet, and @Jaafar Tribak has published a number of helpful projects involving the clipboard which should give you a start on how to access it.

Hope that helps.