Can I Get the Source Range Of Excel Clipboard Data?

by

This code is being used in Excel 2019 64 bit to get the range of the cells on the clipboard as opposed to the contents of the cells.

fGetClipRange returns a range object for the Excel range that is cut or copied onto the clipboard, including book and sheet. It reads it directly from the clipboard using the “Link” format, and requires the ID number for this format. The ID associated with the registered formats can change, so fGetFormatId finds the current format ID from a format name. Use Application.CutCopyMode to determine whether the cells were cut or copied.

This site was useful for working with the clipboard in VBA: https://social.msdn.microsoft.com/Forums/office/en-US/ee9e0d28-0f1e-467f-8d1d-1a86b2db2878/a-clipboard-object-for-vba-including-microsoft-word?forum=worddev

Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As Long
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal lngFormat As Long) As LongPtr
Private Declare PtrSafe Function EnumClipboardFormats Lib "user32" (ByVal wFormat As Long) As Long
Private Declare PtrSafe Function GetClipboardFormatNameA Lib "user32" (ByVal wFormat As Long, ByVal lpString As String, ByVal nMaxCount As Long) As Long

'2020-02-11 get excel copy or cut range from clipboard
Function fGetClipRange() As Range
Dim strGetClipRange As String    'return range
Dim lptClipData As LongPtr  'pointer to clipboard data
Dim strClipData As String   'clipboard data
Dim intOffset As Integer    'for parsing clipboard data
Dim lngRangeLink As Long  'clipboard format
Const intMaxSize As Integer = 256   'limit for r1c1 to a1 conversion
    lngRangeLink = fGetFormatId("Link") 'we need the id number for link format
    If OpenClipboard(0&) = 0 Then GoTo conDone  'could not open clipboard
    lptClipData = GetClipboardData(lngRangeLink)    'pointer to clipboard data
    If IsNull(lptClipData) Then GoTo conDone    'could not allocate memory
    lptClipData = GlobalLock(lptClipData)   'lock clipboard memory so we can reference
    If IsNull(lptClipData) Then GoTo conDone    'could not lock clipboard memory
    intOffset = 0   'start parsing data
    strClipData = Space$(intMaxSize)    'initialize string
    Call lstrcpy(strClipData, lptClipData + intOffset)  'copy pointer to string
    If strClipData = Space$(intMaxSize) Then GoTo conDone   'not excel range on clipboard
    strClipData = Mid(strClipData, 1, InStr(1, strClipData, Chr$(0), 0) - 1)    'trim null character
    If strClipData <> "Excel" Then GoTo conDone     'not excel range on clipboard
    intOffset = intOffset + 1 + Len(strClipData)    'can't retrieve string past null character
    strClipData = Space$(intMaxSize)    'reset string
    Call lstrcpy(strClipData, lptClipData + intOffset)  'book and sheet next
    strClipData = Mid(strClipData, 1, InStr(1, strClipData, Chr$(0), 0) - 1)
    strGetClipRange = "'" & strClipData & "'!"  'get book and sheet
    intOffset = intOffset + 1 + Len(strClipData)    'next offset
    strClipData = Space$(intMaxSize)    'initialize string
    Call lstrcpy(strClipData, lptClipData + intOffset)  'range next
    strClipData = Mid(strClipData, 1, InStr(1, strClipData, Chr$(0), 0) - 1)
    strGetClipRange = strGetClipRange & strClipData 'add range
    strGetClipRange = Application.ConvertFormula(strGetClipRange, xlR1C1, xlA1)
    Set fGetClipRange = Range(strGetClipRange)  'range needs a1 style
conDone:
    Call GlobalUnlock(lptClipData)
    Call CloseClipboard
End Function

'2020-02-11 clipboard format id number changes so get it from format name
Function fGetFormatId(strFormatName As String) As Long
Dim lngFormatId As Long
Dim strFormatRet As String
Dim intLength As Integer
    If OpenClipboard(0&) = 0 Then Exit Function   'could not open clipboard
    intLength = Len(strFormatName) + 3  'we only need a couple extra to make sure there isn't more
    lngFormatId = 0 'start at zero
    Do
        strFormatRet = Space(intLength) 'initialize string
        GetClipboardFormatNameA lngFormatId, strFormatRet, intLength    'get the name for the id
        strFormatRet = Trim(strFormatRet)   'trim spaces
        If strFormatRet <> "" Then  'if something is left
            strFormatRet = Left(strFormatRet, Len(strFormatRet) - 1)    'get rid of terminal character
            If strFormatRet = strFormatName Then    'if it matches our name
                fGetFormatId = lngFormatId  'this is the id number
                Exit Do 'done
            End If
        End If
        lngFormatId = EnumClipboardFormats(lngFormatId) 'get the next used id number
    Loop Until lngFormatId = 0  'back at zero after last id number
    Call CloseClipboard 'close clipboard
End Function

More Related Contents:

  1. Getting error Procedure too large in VBA Macros (Excel)
  2. Workbooks.Open Method in VBA
  3. VBA – Run Time Error 1004 ‘Application Defined or Object Defined Error’
  4. Consolidating worksheets into one
  5. Get text from clipboard using GetText – avoid error on empty clipboard
  6. How can I create text files with special characters in their filenames
  7. VBA hash string
  8. Why Use Integer Instead of Long?
  9. Loop through files in a folder using VBA?
  10. Wait for Shell to finish, then format cells – synchronously execute a command
  11. How can Excel VBA open file using default application
  12. Excel VBA code to copy a specific string to clipboard
  13. Run same excel macro on multiple excel files
  14. how to detect whether VBA excel found something?
  15. VBA : save a file with UTF-8 without BOM
  16. Excel VBA “Method ‘Document’ of object ‘IWebBrowser2’ failed”
  17. Determine if cell contains data validation
  18. VBA – Range to jpg picture
  19. Making VBA Form TextBox accept Numbers only (including +, – and .)
  20. Continue For loop
  21. FileDialog doesn’t work
  22. How to refer to Excel objects in Access VBA?
  23. Can Advanced Filter criteria be in the VBA rather than a range?
  24. In Excel VBA, what is the way to check if the web page is fully loaded?
  25. Excel Useform: How to hide application but have icon in the taskbar
  26. Reading Web Pages using Excel VBA
  27. How to get the procedure or function name at runtime?
  28. Copy an array reference in VBA
  29. What does the To and Step mean in VBA?
  30. Query my excel worksheet with VBA

Leave a Comment