vba - Can I Get the Source Range Of Excel Clipboard Data?

Question

If the Clipboard contains an Excel Worksheet Range, you can access that Range's Data with the DataObject Object

Can you also find the actual Source Range (ie Worksheet, Row & Column) of that Data?

Alternatively, can you find the Last Copied Range, which is indicated with a Dashed Outline Border (NOT the Selected Range)?

Preferably using Excel 2003 VBA

Answer 1

Not directly, no - the clipboard object seems to only contain the values of the cells (though Excel obviously somehow remembers the border):

Sub testClipborard()

    Dim test As String
    Dim clipboard As MSForms.DataObject
    Set clipboard = New MSForms.DataObject

    clipboard.GetFromClipboard
    test = clipboard.GetText

    MsgBox (test)

End Sub

Note you will need a reference to the Microsoft Forms 2.0 Library to get this to run (and if you don't have values in the cells it will also fail).

---

That being said, you can try something like the following - add this to a module in the VBA editor.

Public NewRange As String 
Public OldRange As String 
Public SaveRange As String 
Public ChangeRange As Boolean 

And use the following in a sheet object

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 

     'save previous selection
    OldRange = NewRange 

     'get current selection
    NewRange = Selection.Address 

     'check if copy mode has been turned off
    If Application.CutCopyMode = False Then 
        ChangeRange = False 
    End If 

     'if copy mode has been turned on, save Old Range
    If Application.CutCopyMode = 1 And ChangeRange = False Then 
         'boolean to hold "SaveRange" address til next copy/paste operation
        ChangeRange = True 
         'Save last clipboard contents range address
        SaveRange = OldRange 
    End If 

End Sub 

It seemingly works, but, it's also probably fairly prone to different bugs as it is attempting to get around the issues with the clipboard. http://www.ozgrid.com/forum/showthread.php?t=66773