Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting data from the clipboard in VBA

    Hi.

    Trying to write an application which copies data from one file and pastes it into another (XL2000). So far, so easy.

    Unfortunately, each time I close the originating file, I get the message "you have left a large amount of data on the clipboard..." and have to manually click NO to delete it.

    How can I do this automatically, because I don't want it left in as a manual thing to press as the user won't have a clue as to what it means.

    Thanks.

    Stuart

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting data from the clipboard in VBA

    I haven't actually tried this, but I would guess you could use the PutInClipBoard method to with a data object containing an empty string. That should replace the current clipboard contents with an empty string.
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting data from the clipboard in VBA

    I think the 'clipboard message' will not appear if you use

    Application.DisplayAlerts = False

    immediately in front of the ActiveWorkbook.Close statement (or Save, or whatever you use to close your workbook)

    and enable the Excel alerts after you pasted what's on the clipboard, by

    Application.DisplayAlerts = True

    Something like this: (as an example, I just selected a range large enough to make the 'clipboard message ' popping up)

    Sub Test()
    Windows("Sheet5").Activate
    Range("A1:N684").Select
    Application.DisplayAlerts = False
    Selection.Copy
    ActiveWorkbook.Close
    ActiveSheet.Paste
    Application.DisplayAlerts = True
    End Sub

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Deleting data from the clipboard in VBA

    try Application.CutCopyMode = False
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting data from the clipboard in VBA

    Is it possible to copy the data by other means that using the clipboard? I suspect the clipboard is inefficient anyway. It depends on how much you want copied

    eg:
    Range("a1:b20").Value = Range("c1:d20").Value
    Range("a1:b20").Formula = Range("c1:d20").Formula

    will copy values and formulas but not formatting.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Deleting data from the clipboard in VBA

    Hi Stuart,
    I'd agree with Geoff that the clipboard isn't usually the best way to go. However, in case you're stuck with using it (or just for reference) you can clear the clipboard using an API call. Add
    Public Declare Function EmptyClipboard Lib "user32" () As Long
    to the beginning of your module and then at the end of your procedure use something like:
    retval = emptyclipboard()
    where retval is just a temporary variant variable.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting data from the clipboard in VBA

    Rory,

    That's nice.

    It lead me to think though that it could be cleared enough to avoid the message) by:
    cells(1,1).copy
    (or from any blank cell).

    And to add a question.

    Is it (or why isn't it) possible to manipulate the clipboard object as is possible in VB?

    eg, in VB, I can code "Clipboard.Clear", or "ClipBoard.SetText".

    Useful- if it was available.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Deleting data from the clipboard in VBA

    Geoff,
    It was also completely unnecessary as Catharine's Application.cutcopymode = false seems to work just as well!
    As I recall, in VBA the clipboard manipulation is pretty limited without the API (you need dataobjects to do most of it). If I knew why, I guess I'd be worth umpteen billion dollars too..... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting data from the clipboard in VBA

    Thanks to one and all.

    In the end I plumped for Catherine's suggestion, which seems to work a treat. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    Stuart

Posting Permissions

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