System Clipboard vs Office Clipboard

flowingwindrider

New Member
Joined
Mar 25, 2008
Messages
6
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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. :eek:

Code:
'=============================================================================
'- 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 ---------------------------------------------------------
 

Watch MrExcel Video

Forum statistics

Threads
1,126,999
Messages
5,622,114
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top