Deleting data from the clipboard in VBA

Topic

New Reply

Hi.

Trying to write an application which copies data from one file and pastes it into another (XL2000). So far, so easy.

Unfortunately, each time I close the originating file, I get the message “you have left a large amount of data on the clipboard…” and have to manually click NO to delete it.

How can I do this automatically, because I don’t want it left in as a manual thing to press as the user won’t have a clue as to what it means.

Thanks.

Stuart

Reply | Quote

Replies

I haven’t actually tried this, but I would guess you could use the PutInClipBoard method to with a data object containing an empty string. That should replace the current clipboard contents with an empty string.

Reply | Quote

I think the ‘clipboard message’ will not appear if you use

Application.DisplayAlerts = False

immediately in front of the ActiveWorkbook.Close statement (or Save, or whatever you use to close your workbook)

and enable the Excel alerts after you pasted what’s on the clipboard, by

Application.DisplayAlerts = True

Something like this: (as an example, I just selected a range large enough to make the ‘clipboard message ‘ popping up)

Sub Test()
Windows(“Sheet5”).Activate
Range(“A1:N684”).Select
Application.DisplayAlerts = False
Selection.Copy
ActiveWorkbook.Close
ActiveSheet.Paste
Application.DisplayAlerts = True
End Sub

Reply | Quote

try Application.CutCopyMode = False

Reply | Quote

Is it possible to copy the data by other means that using the clipboard? I suspect the clipboard is inefficient anyway. It depends on how much you want copied

eg:
Range(“a1:b20”).Value = Range(“c1:d20”).Value
Range(“a1:b20”).Formula = Range(“c1:d20”).Formula

will copy values and formulas but not formatting.

Reply | Quote

Hi Stuart,
I’d agree with Geoff that the clipboard isn’t usually the best way to go. However, in case you’re stuck with using it (or just for reference) you can clear the clipboard using an API call. Add
Public Declare Function EmptyClipboard Lib “user32” () As Long
to the beginning of your module and then at the end of your procedure use something like:
retval = emptyclipboard()
where retval is just a temporary variant variable.
Hope that helps.

Reply | Quote

Rory,

That’s nice.

It lead me to think though that it could be cleared enough to avoid the message) by:
cells(1,1).copy
(or from any blank cell).

And to add a question.

Is it (or why isn’t it) possible to manipulate the clipboard object as is possible in VB?

eg, in VB, I can code “Clipboard.Clear”, or “ClipBoard.SetText”.

Useful- if it was available.

Reply | Quote

Geoff,
It was also completely unnecessary as Catharine’s Application.cutcopymode = false seems to work just as well!
As I recall, in VBA the clipboard manipulation is pretty limited without the API (you need dataobjects to do most of it). If I knew why, I guess I’d be worth umpteen billion dollars too…..

Reply | Quote

Thanks to one and all.

In the end I plumped for Catherine’s suggestion, which seems to work a treat.

Stuart

Reply | Quote