Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  • Thread Tools
  1. 3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Clear the clipboard (Excel 2000)

    I use VBA code to copy a pivot table and then past it using the paste special 'values' option. Even although I use Application.CutCopyMode = False,
    the clipboard is not cleared. Because there are 20 pivot tables that must be copied and pasted, the clipboard becomes overloaded.
    Is there any other way to clear the Windows clipboard totally from within the macros?
    Can anyone out there help me, please.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the clipboard (Excel 2000)

    What makes you think the clipboard becomes overloaded?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. 3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the clipboard (Excel 2000)

    I get an error that tells me 'You cannot paste any more items to the clipboard' or some such text. Also when I open the Clipboard Toolbar, it shows me all these items that have been pasted. I want them cleared!

  5. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Clear the clipboard (Excel 2000)

    Unfortunately, the Office clipboard is not exposed in the Excel or Office object model (still isn't in Excel 2002, don't know about 2003), so you can't manipulate it in VBA directly. I found something in the MS newsgroups that may work for Excel 2000; it doesn't work in Excel 2002:

    Sub ClearClipboard()
    On Error Resume Next
    Application.CommandBars("Clipboard").Controls("Cle ar All").Execute
    End Sub

    This attempts to press the Clear All button on the Clipboard task pane. (But in Excel 2002, the buttons on this task pane don't count as command bar controls)

  6. 3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the clipboard (Excel 2000)

    Part of the problem is resolved - I don't get the error during the run time anymore. Thanks for this.
    When I try to copy another piece of information to the clipboard, I get the warning message that the clipboard can only contain a max of 12 items and asks me if I want to continue and throw away the first item, with Yes and No buttons. It only appears once and then you can carry on copying and pasting.
    So in fact the 'execute' command does not actually remove everything from the clipboard in 2000 either.

  7. Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the clipboard (Excel 2000)

    There is a registry setting that will disable the Office Clipboard altogether in both Office 2000 and Office XP. This is mentioned in both of Woody's books and is also available here. If you need to set this up on other machines as well, you will need to use the Scripting Runtime. HTH
    Gre

  8. 3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the clipboard (Excel 2000)

    This could work if the file was only used on one machine, or where you can control who is to get and use the file.
    Unfortunately I do not have control over who will use the file.
    Pity, I was hoping there would be some way of manipulating the clipboard from within Excel or Word!
    Thanks all.

  9. Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the clipboard (Excel 2000)

    Something that MIGHT work is using:

    Application.DisplayAlerts = False 'turns off any warning

    You will have to reset this to True at the end of your code. Otherwise, all warnings will remain turned off.

    I have not tested this, and the Office Clipboard interface may not actually operate at the "Excel" level - making this irrelevant. HTH
    Gre

  10. Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Clear the clipboard (Excel 2000)

    I was going to suggest temporarily disabling the Clipboard CommandBar, but now I seem to have permanently disabled mine, so perhaps it's not such a good idea for wide deployment. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    <pre>?commandbars("clipboard").Enabled
    False

    commandbars("clipboard").Enabled=True

    ?commandbars("clipboard").Enabled
    False</pre>

    (Display puzzled expression here.)

  11. Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the clipboard (Excel 2000)

    Strange. Works fine for me (Xl2000).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Clear the clipboard (Excel 2000)

    I was unable to revive it in either Excel 2002 or Word 2002. Seems I've killed it in a very serious way. Not that I'm complaining. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  13. 3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the clipboard (Excel 2000)

    Sorry you have lost your clippy - although you don't seem too sad!!!!
    I have run the macro with the clipboard code at the top and bottom, but the copy data of the pivot table seems to be so stubbon. It still appears on the clipboard after the macro has run!!
    It is not really a train smash, although I am rather surprised that it does not work as it does on your computer.
    I rest my case. Thanks for all the assistance.

  14. Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Clear the clipboard (Excel 2000)

    WinAPI to the rescue.

    Declare Function EmptyClipboard Lib "user32" Alias "EmptyClipboard" () As Long
    Charlotte

  15. 3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the clipboard (Excel 2000)

    I popped this function right at the top of the module and ...... IT WORKED.
    Thank you a thousand times.
    The clipboard is clear - no more error message - and I can copy somethings immediately after running the macro and it does not give me an error either.

  16. Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Clear the clipboard (Excel 2000)

    The functionality within Office for stuff like this is just a wrapper over the API calls. Sometimes we try too hard to avoid the API when that's really the *easy* way to do it. Not in every case, of course, but sometimes ... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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