System Clipboard vs Office Clipboard

[flowingwindrider]

Greetings!

I am attempting to use the office clipboard to switch 2 lines of data in excel 2007. I need to copy the values only, and leave the formatting alone. Now, I can copy the two lines of data just fine to the office clipboard, but when I use paste special the program accesses the system clipboard instead of the office clipboard. Thus, only my last copied item can be special pasted; the first can only be pasted from the office clipboard with the formatting and all. Does anyone know how to override this so that I can select what I wish to copy from the office clipboard and "paste special" it in my spreadsheet?

Thanks in advance for your help.

 

[BrianB]

As usual we can do it with code. There are 2 subroutines here, TEST_1 and TEST_2, both using the same initialise routine to put some test data into an empty worksheet. Just copy/paste everything into a code module and run.

TEST_1
Uses the DataObject. I have only ever used this when having to copy to and from Excel and an external application (ie. non-Office such as Oracle or SAP)

TEST_2
Also as usual, *if we can avoid copy/paste* we can get more control over the process. TEST_2 shows a simpler, more boring, method of doing the same job.

'=============================================================================
'- TEST_1 : EXAMPLE USE OF DATAOBJECT TO MANIPULATE THE MS OFFICE CLIPBOARD
'- This is not extensive
'- I find this more useful when copy/pasting to & from EXTERNAL applications
'=============================================================================
'- Add reference via VB Editor Tools/References(usually in \windows\system32\)
'- MS Office VBA. "Forms 2.0 Object Library" file name FM20.dll
'-----------------------------------------------------------------------------
'- TEST_2 BELOW IS A MORE BORING METHOD OF DOING THE SAME JOB
'-----------------------------------------------------------------------------
'- Brian Baulsom March 2008
'=============================================================================
'- TEST_1
'- DataObject works like the Clipboard - for text only.
'- "Format" is just an item identifier - string or number
'- The DataObject will store one piece of text for 'ordinary' text format
'- and one piece of text for each different format. If text is copied that
'- matches the format of existing stored text, the latter is replaced.
'- like an array or object collection
'=============================================================================
'- DATA OBJECT METHODS
'- Clear              ' clears DataObject completely
'- GetFormat          ' checks if a user defined format exists
'- PutInClipBoard     ' Data TO clipboard : enables use of "Paste"
'- GetFromClipBoard   ' Data(eg.from Copy etc.) FROM clipboard to DataObject
'- GetText            ' retrieves string according to UD format
'- SetText      'copies using specified format (standard text or user defined)
'- StartDrag          ' control drag & drop (eg.see form Textbox mouse events)
'=============================================================================
'- common variables to both test routines
Dim Range1 As Range         ' sheet range 1
Dim Range2 As Range         ' sheet range 2
Dim Range3 As Range         ' Destination for Copy/GetFromClipBoard
Dim Range4 As Range         ' Destination for Copy/Split(GetFromClipBoard)
Dim ws As Worksheet
Dim CellCount As Integer    ' num cells in range
Dim c As Integer
'=============================================================================
'- EXCHANGE 2 ROWS OF DATA USING DATAOBJECT
'- We can copy text in the same format if we give different format references
'- This example assumes Range1 and Range 2 are the same size.
'- Uses 2 data objects to make the code easier. Could use 1.
'=============================================================================
Sub TEST_1()
    Dim DataObject1 As DataObject
    Dim DataObject2 As DataObject
    Dim DataObject3 As DataObject
    Dim String3 As Variant              ' array to parse the *copied* data
    '------------------------------------------------------------------------
    INITIALISE          ' sub routine
    '------------------------------------------------------------------------
    '- DATA TO THE 2 DATA OBJECTS
    Set DataObject1 = New DataObject
    Set DataObject2 = New DataObject
    For c = 1 To CellCount
        DataObject1.SetText Range1.Cells(c).Value, c
        DataObject2.SetText Range2.Cells(c).Value, c
    Next
    '=========================================================================
    '- DATA FROM OBJECTS TO CELLS
    For c = 1 To CellCount
        '-------------------------------------------------------------------
        Range1.Cells(c).Value = DataObject2.GetText(c)
        '-------------------------------------------------------------------
        '- THIS DOES A CHECK FIRST
        If DataObject2.GetFormat(c) = True Then
            Range2.Cells(c).Value = DataObject1.GetText(c)
        End If
        '-------------------------------------------------------------------
    Next
    '=========================================================================
    '- DATA FROM CLIPBOARD TO CELLS
    Set DataObject3 = New DataObject
    Range1.Copy
    '- SEE THE MESS THIS MAKES
    DataObject3.GetFromClipboard
    Range3.Value = DataObject3.GetText(1)
    '-------------------------------------------------------------------------
    '- USING SPLIT - zero base array
    '- Tab character 9 is the cell delimiter set by Excel
    '- Clean() gets rid of final Tab at the end of the string
    String3 = Split(DataObject3.GetText(1), vbTab)
    For c = 1 To CellCount
        Range4.Cells(c).Value = Application.WorksheetFunction.Clean(String3(c - 1))
    Next
    '=========================================================================
    MsgBox ("Data changed")
    '-------------------------------------------------------------------------
    Set DataObject1 = Nothing
    Set DataObject2 = Nothing
    Set DataObject3 = Nothing
End Sub
'========== END OF TEMP_1 ====================================================
'
 
'=============================================================================
'- TEST_2 - THE BORING METHOD  ;-)
'- As usual we try to use methods other than Copy/Paste
'=============================================================================
Sub TEST_2()
    Dim TempText As String
    '------------------------------------------------------------------------
    INITIALISE          ' sub routine
    '------------------------------------------------------------------------
    For c = 1 To CellCount
        TempText = Range1.Cells(c).Value
        Range1.Cells(c).Value = Range2.Cells(c).Value
        Range2.Cells(c).Value = TempText
    Next
   MsgBox ("Data changed")
End Sub
'========== END OF TEMP_2 ====================================================
'
'=============================================================================
'- CALLED FROM TEST1 AND TEST2 TO PUT SOME SAMPLE DATA INTO THE WORKSHEET
'=============================================================================
Private Sub INITIALISE()
    Set ws = ActiveSheet
    Set Range1 = ws.Range("A1:E1")
    Set Range2 = ws.Range("A3:E3")
    Set Range3 = ws.Range("A6:E6")
    Set Range4 = ws.Range("A7:E7")
    CellCount = Range1.Cells.Count
    '-------------------------------------------------------------------------
    '- put some data
    For c = 1 To CellCount
        Range1.Cells(c).Value = Range1.Cells(c).Address
        Range2.Cells(c).Value = Range2.Cells(c).Address
        Range3.Cells(c).Value = ""
        Range4.Cells(c).Value = ""
    Next
    '-------------------------------------------------------------------------
    MsgBox ("Original data entered.")
    '-------------------------------------------------------------------------
End Sub
'-------- end of sub ---------------------------------------------------------