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

 

[Jaafar Tribak]

Interesting the 129 format! I never knew about it. Does it store the number of cells ?

I use Clipboard Viewer but I will now download InsideClipboard. It gives the size in memory as well.

Thanks for sharing @Dan_W

 

[Dan_W]

Interesting the 129 format! I never knew about it. Does it store the number of cells ?

I use Clipboard Viewer but I will now download InsideClipboard. It gives the size in memory as well.

Thanks for sharing @Dan_W

I had no idea about it either until I went to take a screenshot of the XML Spreadsheet format for my reply above.

I think what got me interested in the Clipboard was some code that you wrote a while back that monitors the clipboard and stores the clipboard text data in a worksheet. I looked to add some additional functionality like images, and then tried to work out what program was active when the user copied to the clipboard. I tried a number of different formulations, but it wasn't until Sancarn and I were looking into making a proper clipboard class for VBA that I found the GetClipboardOwner API, which is very helpful. Goes to show that I could've saved myself some time had I just read the documentation!

I think what is even more interesting is all the work that is happening behind the scenes. I find that when Excel uses the CopyPIcture method, it is in fact storing the image in a number of different formats - including a PNG file with a transparency layer (sometimes), which I found fascinating.

Anyway, this is some ccode I wrote re: finding the clupboard owner. I leave the code here in case anyone needs it. Feedback is encouraged and always appreciated. It finds the program that was the "Clipboard Owner" at the time the data was stored in the clipboard. The API returns a hWnd, and from that point, it is a pretty straightforward trip to finding out the Process ID and then the Application. The following routine calls the longer set of functions/subs below:

Sub TestRoutines()
  Dim TargetHwnd As LongPtr, PID As Long, AppFileName As String
  TargetHwnd = GetClipBoardOwner()
  PID = GetProcessIDFromHwnd(TargetHwnd)
  AppFileName = GetProcessName(PID)
  Debug.Print "hWnd: " & TargetHwnd
  Debug.Print "ProcessId: " & PID
  Debug.Print "App Filename: " & AppFileName
End Sub

And the following should be put in the same module as the TestRoutines sub:

#If VBA7 Then
  Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As LongPtr, ByRef lpdwProcessId As Long) As Long
  Private Declare PtrSafe Function GetClipBoardOwner Lib "user32" Alias "GetClipboardOwner" () As LongPtr
  Private Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As LongPtr
  Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long
  Private Declare PtrSafe Function GetModuleFileNameExA Lib "psapi" (ByVal hProcess As LongPtr, ByVal hModule As LongPtr, ByVal lpFileName As String, ByVal nSize As Long) As Long
#Else
  Private Enum LongPtr
  [_]
  End Enum
  Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As LongPtr, ByRef lpdwProcessId As Long) As Long
  Private Declare Function GetClipBoardOwner Lib "user32" Alias "GetClipboardOwner" () As LongPtr
  Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As LongPtr
  Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long
  Private Declare Function GetModuleFileNameExA Lib "psapi" (ByVal hProcess As LongPtr, ByVal hModule As LongPtr, ByVal lpFileName As String, ByVal nSize As Long) As Long
#End If

Private Const PROCESS_QUERY_INFORMATION As Long = &H400
Private Const PROCESS_VM_READ As Long = &H10

Private Function GetProcessIDFromHwnd(ByVal hwnd As LongPtr) As Long
  Dim ProcessId As Long, ThreadId As Long
  ThreadId = GetWindowThreadProcessId(hwnd, ProcessId)
  If ThreadId <> 0 Then
    GetProcessIDFromHwnd = ProcessId
  End If
End Function

Private Function GetFileNameFromPath(ByVal FilePath As String) As String
  Dim FileParts() As String
  FileParts = Split(FilePath, "\")
  If UBound(FileParts) >= 0 Then
    GetFileNameFromPath = FileParts(UBound(FileParts))
  End If
End Function

Private Function GetProcessName(ByVal ProcessId As Long) As String
  Dim hProcess As LongPtr
  Dim ProcessPath As String
  Dim Length As Long
  On Error GoTo ErrHandler
  ProcessPath = String(260, Chr$(0))
  hProcess = OpenProcess(PROCESS_QUERY_INFORMATION Or PROCESS_VM_READ, 0, ProcessId)
  If hProcess <> 0 Then
    Length = GetModuleFileNameExA(hProcess, 0, ProcessPath, 260)
    If Length > 0 Then
      ProcessPath = Left$(ProcessPath, Length)
      GetProcessName = GetFileNameFromPath(ProcessPath)
    End If
ErrHandler:
    CloseHandle hProcess
  End If
End Function