Transfer clipboard from Task pane clipboard(office?) content to Excel (windows?) clipboard?

T

tskogstrom

Hi,

I run Excel code initiated by worksheet_change events and some of them
kill the Clipboard content, making cut- and paste difficult.

I have however noticed that the clipboard in the Task pane still
contain the cut/copy content. Therefore, I want to add som code to
always restore the clipboard content from taskpane to Excel clipboard.
I will add the code in my worksheet_change event and run restore if
the content differ, I guess.

To start with - have anybody some code to suggest to transfere the
content between them?

Kind regards
Tskogstrom
 
N

NickHK

Working with Windows clipboard is fairly straight forward, using the
DataObject exposed from MSForms. However, you can only work with the text
format. Unless Office2003 and up have added functionality ?

Private Sub CommandButton1_Click()
Dim DatObj As DataObject
Dim TempStr As String

Const TEXTFORMAT As Long = 1

Range("rngCopy").Copy

Set DatObj = New DataObject

With DatObj
.GetFromClipboard
If .GetFormat(TEXTFORMAT) = True Then
TempStr = .GetText
End If
.Clear
.SetText TempStr, TEXTFORMAT
.PutInClipboard
End With

Range("A20").Select
ActiveSheet.Paste

End Sub

There is the Windows API that will give you complete access to the Windows
clipboard in all formats, but that will require a lot more work to function
correctly.

In Excel 2000 only, you can work somewhat with the Office clipboard. Later
version have the clipboard on the Task Pane and is not accessible, AFAIK.
<Excel2K only>
Private Sub CommandButton1_Click()
Dim ClipTBar As CommandBar
Dim i As Long

Const PASTEALL As Long = 2

Set ClipTBar = Application.CommandBars("Clipboard")

With ClipTBar.Controls
For i = 1 To .Count
Debug.Print .Item(i).Caption
Next

.Item(PASTEALL).Execute
End With

End Sub
</Excel2K only>

Other than that maybe Paste somewhere temporarily and copy/cut from there,
if you cannot arrange your code to preserve the Clipboard and you need more
than just text.

NickHK
 
T

tskogstrom

Thanks for answer,
I'll look into it. I want to cover Office 2003, but if possible also
Office 2002 ( I think I already have some issues with 2000, forcing me
to drop it, and of cource 97 isn't of this world to cover).

I guess text cut/paste is most used.

2- However, often users copy/paste a range of cells. That particular
case, I belive I had to cover as well. Maybe I can store the selected
range and if the clipboard turned empty, by code i select the range
again and copy it? I need to check of it is empty, because it might
stall the users fluency othervice, If it take a while to run.
Any idea about that, or should we leave that open, to others to
suggest? Is the clipboard empty if ".GetFormat(TEXTFORMAT) = False"?
or could it be other objects like chartobjects etc?


3 - The third case I belive will make a fuss is when users try to copy
chartobjects and paste it into powerpoint files.

Kind regards
Tskogstrom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top