VBA Excel code to Clear all in Clipboard

[jduong93]

Hi All,

I'm trying to write a macro to "clear all" in the excel Clipboard.

It's effect should be equivalent to going to the ClipBoard and clicking 'Clear All' (refer to below screenshot)

http://imgur.com/a/7d8M7

The point of this is clear all clipboard data at the end of a very long macro i have designed, which will not prompt the "the picture is too large and will need need to truncated" once closing the workbook.

I have spent hours trying to find a solution but nothing have been successful.

The below is a common solution where you paste this code after "pasting a value." I have tried this, but the data still seems to be put onto the clipboard.
Application.CutCopyMode = False

Is there a way to simply code a "Clear All" command as per the screenshot?

Seems simple, but struggling to find the answer.

Thank you all!

- James

[mehmetcik]

Why not find the last used cell in column A and copy the cell below it?

[jduong93]

I just realised that the clipboard is not part of excel, it is the window's clipboard, hence why I can't code it in excel.

The below is the relative coding., even with the Application.CutCopyMode = False, it still seems to put the copied data onto the clipboard.


Sheets("Pivot").UsedRange.Select
Selection.Copy
Sheets("Final").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

[Logit]

.
..
Does this help ?

http://http://stackoverflow.com/ques...board-with-vba

Saw this on another post, and I have tested it with Word VBA.

'Clearing the Office Clipboard

Dim oData As New DataObject 'object to use the clipboard

oData.SetText text:=Empty 'Clear
oData.PutInClipboard 'take in the clipboard to empty it
Just copy and paste into your code where ever you need to clear the Clipboard.

Another thing I noticed is that when I .Quit a program, say Excel, it keeps asking me if I want to keep the data is the Clipboard. A work around is to clear the clipboard using the above stated code. See below:

'Clearing the Office Clipboard

Dim oData As New DataObject 'object to use the clipboard

oData.SetText text:=Empty 'Clear
oData.PutInClipboard 'take in the clipboard to empty it


'You can also just remove the Alert Messages from the Excel Program while
'the code is running
'Remove alert Messages from the Excel Program when closing
ExcelProgram.DisplayAlerts = False

'Quiting the Excel Application
ExcelProgram.Quit
I used the above example in a VBA code to import data from an Excel File. See here


This functionality is held within the library "Microsoft Forms 2.0 Object Library". To link to that library go to the VBA editor, then Tools, References and pick it out from the list if it's not already ticked.

You can do more funky stuff with a bunch of WinAPI calls, but I generally prefer avoiding those unless absolutely necessary.

Also, don't forget about the DisplayAlerts property, which will suppress dialog boxes - although I'm not sure if it would always produce the desired result.

[LJMetzger]

Hi jduong,

Try the following code:

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis

[Logit]

.
.
.

Just located this ... tested and works :

Please Login or Register  to view this content.

[jduong93]

This worked! thanks so much

[Logit]

Your welcome. Thanks for the add rep.

Merry Christmas !

[E.Lulai F.]

No above option works in Windows 10Pro with Excel 2016.
Does anyone have any idea on how to clear windows clipboard using Excel VBA macro programming?

[bakerman2]

E.Lulai F., welcome to the forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[Baboutz]

Hello everyone,

The Logit's code don't work with me.
So, after a long research, here a code I found wich is working well for me (Windows 10):

Please Login or Register  to view this content.

To call in your macro with :

Please Login or Register  to view this content.

Have a good day,

Baboutz

[Kingrat]

Dear Baboutz,

I have Win10 and Office365.

I get various error messages, if I just copy your text into the VBA area. Is there a certain region within VBA where the information for the function is stored?
Many Thanks!

[FDibbins]

Dear Baboutz,

I have Win10 and Office365.

I get various error messages, if I just copy your text into the VBA area. Is there a certain region within VBA where the information for the function is stored?
Many Thanks!

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread