Clipboard history

[Duncan]

I need to use a macro to copy a number of items to the clipboard. This
is possible interactively (every 'Copy' pushes the history stack along and
a new entry is inserted on the top) however it doesn't appear to be the
default behavior with VB. If I use the Copy or PutInClipboard methods the top
existing clipboard entry is replaced. So multiple Copy's only generate
a single clipboard entry (the last one). This is Excel 2000 if it makes
any difference.

How can I exploit the clipboard history functionality from a macro so that a
number of items can be inserted?

Duncan

 

[Jim Rech]

I don't believe MS has given us any programmatic access to the Office
Clipboard.

 

[Greg Wilson]

I can't say I like it but this is what I came up with.
The approach is to programmatically execute the controls
of the Clipboard toolbar. Note that I could only get it
to work when the Clipboard toolbar is visible. I make it
invisible again at the end which causes a flicker. Also
note that there is appraently an invisible control on the
toolbar. Therefore, the first control that stores data is
the 5th (It looks like it should be the 4th).

Sub TestClipboard()
Dim i As Integer
Application.ScreenUpdating = False
With Application.CommandBars("Clipboard")
.Visible = True
'Copy contents of four cells to clipboard
Range("A1").Copy
Range("A2").Copy
Range("A3").Copy
Range("A4").Copy
On Error Resume Next
'Paste contents from clipboard to other cells
For i = 1 To 4
Cells(i, 10).Select
.Controls(i + 4).Execute
Next
.Controls(4).Execute 'Clear clipboard
.Visible = False
End With
Application.ScreenUpdating = True
End Sub

Regards,
Greg

 

[Duncan]

Thanks, just the job.

Duncan

I can't say I like it but this is what I came up with.
The approach is to programmatically execute the controls
of the Clipboard toolbar. Note that I could only get it
to work when the Clipboard toolbar is visible. I make it
invisible again at the end which causes a flicker. Also
note that there is appraently an invisible control on the
toolbar. Therefore, the first control that stores data is
the 5th (It looks like it should be the 4th).

Sub TestClipboard()
Dim i As Integer
Application.ScreenUpdating = False
With Application.CommandBars("Clipboard")
.Visible = True
'Copy contents of four cells to clipboard
Range("A1").Copy
Range("A2").Copy
Range("A3").Copy
Range("A4").Copy
On Error Resume Next
'Paste contents from clipboard to other cells
For i = 1 To 4
Cells(i, 10).Select
.Controls(i + 4).Execute
Next
.Controls(4).Execute 'Clear clipboard
.Visible = False
End With
Application.ScreenUpdating = True
End Sub

Regards,
Greg