Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Clip board

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try

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

  3. #3
    Board Regular
    Join Date
    May 2002
    Posts
    211
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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???

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Kind Regards,
    Ivan F Moala From the City of Sails

  5. #5
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clip board

    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

  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clip board

    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

  7. #7
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clip board

    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.

  8. #8
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,697
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clip board

    Quote 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.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    My Blog @ ExcelTables.com
    (If you would like comments in any code, please say so.)

  9. #9
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clip board

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •