Results 1 to 10 of 10
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    clear clipboard choices (Excel 2003)

    I was searching the Lounge for posts on how to clear the clipboard and found several but none that quite worked. I am not sure what will work for all revs of Excel (at least xl97-2003). I tried the suggestions in <post#=266271>post 266271</post#> and one did work, however the comments suggest it isn't for all versions (or at least it didn't work for the post originator). The Win32 API call (posted by Charlotte) seemed promising but --- and now it gets weird --- when I went to add the call to my VBA module, the editor truncated the text after I hit the Enter key.
    -- as seen in the post:
    Declare Function EmptyClipboard Lib "user32" Alias "EmptyClipboard" () As Long

    -- when I Enter after typing in the above line, the editor immediately changes it to this:
    Declare Function EmptyClipboard Lib "user32" () As Long

    And when I go to use EmptyClipboard, it doesn't error but it doesn't clear it either. The only one of the suggestions in this post that work for me in xl2003 is this:
    <pre> Dim oData As New MSForms.DataObject
    With oData
    .SetText ""
    .PutInClipboard
    End With
    Set oData = Nothing</pre>

    But I'm not sure if it's for different vers of Excel. The old 'application.cutcopymode=false' doesn't clear the clipboard, just removes the marching ants (which in my case aren't marching because the clipboard didn't get populated via Excel but from another program).

    Any ideas as to why the API call fails and what clipboard calls work for what version?

    Deb

  2. #2
    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 clipboard choices (Excel 2003)

    Could there be a letter missing from the "official" function name? Generally, the alias is slightly shortened (e.g., an A might be removed from the end...).

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: clear clipboard choices (Excel 2003)

    Yes, I know what you mean, usually it's something like apiName Alias "apiNameA" but when I tried this:

    Declare Function EmptyClipboard Lib "User32" Alias "EmptyClipboardA" () As Long

    nothing happened (didn't empty the clipboard). I then did a search online to find the declaration and it seems like there is no Alias part after all,
    http://p2p.wrox.com/topic.asp?TOPIC_ID=15747 shows it as

    Declare Function EmptyClipboard Lib "User32" () As Long

    When I just use this in a Module, it doesn't generate any errors, but it also doesn't clear the clipboard (in xl2003 at least). Oh well, I'll skip this little extra feature that I was going to add to my project but it would of been nice to have. I'll leave it as user-beware for this part of my application (where I let the user paste a pre-selected graphic (logo) onto two protected worksheets at my pre-assigned locations - it works ok but I wanted to clear the clipboard afterwards).

    I was able to get the MSForms.DataObject thing to work but would like to know how backward compatible it is (since it didn't work for the person who posted that thread I listed before).

    Thnx,
    Deb

  4. #4
    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 clipboard choices (Excel 2003)

    I think the VBA MSForms library hasn't changed much since Office 97, but it's difficult to test that theory. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    The usual reason people have problems with DataObject is that if they don't have a UserForm in their project, there would not normally be a reference to the MSForms library.

    Since your data was from an application other than Excel, it should not be the special Office clipboard giving your API call problems... hmmm.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: clear clipboard choices (Excel 2003)

    <P ID="edit" class=small>(Edited by JanB on 04-Jan-06 09:34. )</P>In Word 2003, the API EmptyClipboard only works if applied together with 2 others, as follows:

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

    Sub ClearClipboard
    OpenClipboard (0)
    EmptyClipboard
    CloseClipboard
    End Sub

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: clear clipboard choices (Excel 2003)

    Hey this is great, it worked!! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> You're right that I needed the two extra calls to Open/Close the clipboard. Those were missing from my previous tests. I didn't know they were required and it wasn't in the original post I found. Now I have two ways to do it since the MSForms.DataObject works as well since I do have quite a few forms in my Excel application. I'm pretty sure both of these are also more version independent too. I do have copies of xl97, xl2000 I can install to test so will do so later.

    Thnx,
    Deb

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

    Re: clear clipboard choices (Excel 2003)

    Doesn't

    Application.CutCopyMode=False

    do the trick?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: clear clipboard choices (Excel 2003)

    That was the first thing I tried but in this case the clipboard contents came from a non-Excel program (the copy was done from a web page to grap an image used for a logo). I then went to Excel and pasted it on a worksheet and that's when I needed to clear the clipboard. Doing Application.CutCopyMode = False did nothing, at least in xl2003. It does work if the clipboard source was Excel, however.

    Deb

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

    Re: clear clipboard choices (Excel 2003)

    I thought about that later on too.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: clear clipboard choices (Excel 2003)

    It should be easy enough to copy cell A1 from the first sheet of the current workbook, that will make sure that the clipboard source is Excel.

    StuartR

Posting Permissions

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