Clip board

[elgringo56]

Here is a real dumb one, probably. Is there a way I can clear the clip board from within a macro?

[brettvba]

try

Sub ClearClipboard()
Application.CopyObjectsWithCells = False
Application.CutCopyMode = False
End Sub

[vac]

Further to my problem, the code previous does work to clear the clipboard, but only when there is oneitem in it. When there are two or more items in the clipboard it wont work no matter how many times you run it.
Im using exl 200 with the clipboard toolbar showing.
Please can anyone help???

[Ivan F Moala]

On 2002-05-27 09:19, vac wrote:
Further to my problem, the code previous does work to clear the clipboard, but only when there is oneitem in it. When there are two or more items in the clipboard it wont work no matter how many times you run it.
Im using exl 200 with the clipboard toolbar showing.
Please can anyone help???

Sub ClearClipboardEl2000()

On Error Resume Next 'incase clipboard IS empty

Application.CommandBars("Clipboard").FindControl(ID:=3634).Execute

End Sub

[Cbrine]

Ivan,
I found this code for clearing the clipboard in XL2000, but it doesn't seem to work in Xl2002. I tried cycling through the controls on the Clipboard commandbar, but none were found?? I found that the clipboard manager is called "Task Pane", but it only has a single text box control. I tried cycling through commandbarbuttons, controls, and objects for Clipboard, and didn't find any? Can you let me know how you identified the ID?

Application.CommandBars("Clipboard").FindControl(ID:=3634).Execute

Thanks

[Tom Urtis]

I also get an error with that FindControl method using 2003.


elgringo56 - -

The clipbopard is a Windows object, not exclusively an Excel object. Excel supports a couple dozen data formats (BIFF4, BIFF5, etc) that might be present on the clipboard which must be addresed for the clipboard to truly be cleared.

Someone / anyone correct me if I am wrong or if your version of Excel fails with the following ClearClipboard macro, but it seems to work for me in all cases (drawing objects, charts, audio files...whatever is on the clipboard).


In a standard module:

Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long

Sub ClearClipboard()
OpenClipboard (0&)
EmptyClipboard
CloseClipboard
End Sub

[Cbrine]

Thanks Tom,
Maybe you can help me with an associated problem, why it is that I'm looking for this. I have a process that has 1 main worksheet with a filter. I then filter the data, based on another list, and copy the data to a new workbook. It will add worksheets to the new workbook until another variable changes, then it will save and close the new workbook, then start the cycle again.
The process slows down measureably by the 10 th worksheet, but then clears up when the workbook is saved and a new one is open. Watching from the task manager, you can see that memory usage going up in steps each time a worksheet is added. Then when the workbook is saved and closed, the memory drops like a ski slope. I believe that the macro running is preventing some type of clean up of memory from occuring or that the close is triggering a clean up at that point. The only problem is, is that when the excel clipboard manager is open, this doesn't seem to happen?? I don't understand why that would occur, but I thought the memory issue might be a build up in the clipboard, but that doesn't seem to be the case. It seems like when the excel clipboard manager is open, that excel deals with clipboard objects differently then when it's closed? I can run with the clipboard object open, but I want to determine how to release the used memory in code, if it's possible.

[Zack Barresse]

Originally Posted by Tom Urtis

...Someone / anyone correct me if I am wrong or if your version of Excel fails with the following ClearClipboard macro, but it seems to work for me in all cases (drawing objects, charts, audio files...whatever is on the clipboard)...

Yup, works for me too. I use this quite often as I use my clipboard tremendously. I haven't found anything it doesn't work on yet.

[Tom Urtis]

Cbrine - - If you post your code it would be easier to identify areas where your memory is being used. My guess (only a guess because I have not sen your code) is that maybe you are setting variables for ranges or worksheets but not setting them back to Nothing, or there is excessive selecting or activating, or you are declaring variables as Variant when other less-burdensome variable types would suffice, or you are temporarily copying & saving more data on the clipboard (formulas or formatting) than necessary, or who knows what else. Post your macro and someone may be able to point out areas where an improvement is possible. It would also help if you give whatever detailed text description you can along with the code, so people can see why you are doing what you are doing and maybe suggest alternative approaches.