Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
970 views
in Technique[技术] by (71.8m points)

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

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...