Can't clear office clipboard with VBA

Question

I am using a function to copy a bunch of pictures out of an access database and store them on to the disk. However, this function uses the office clipboard and the clipboard fills up after about 150 records and crashes the program. Here is how I am clearing the clipboard

Private Declare Function apiOpenClipboard Lib "user32" Alias "OpenClipboard" (ByVal hwnd As Long) As Long
Private Declare Function apiEmptyClipboard Lib "user32" Alias "EmptyClipboard" () As Long
Private Declare Function apiCloseClipboard Lib "user32" Alias "CloseClipboard" () As Long

Sub EmptyClipboard()
    Call apiOpenClipboard(0&)
    Call apiEmptyClipboard
    Call apiCloseClipboard
End Sub

Anyone know how to more effectively clear the clipboard

Depending on what version, this might work: Application.CutCopyMode = False — PinkElephantsOnParade, Jan 21 '13 at 14:07
Thanks, I've tried that and it didn't seem to help any. I am using office 2010 — DasPete, Jan 21 '13 at 14:11
You've flagged your question as `ms-access-2007`, might want to update the tag for better/proper visibility — LittleBobbyTables - Au Revoir, Jan 21 '13 at 14:12
Thanks @LittleBobbyTables, clearly I haven't had enough coffee yet — DasPete, Jan 21 '13 at 14:13
Did you see http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/fb6d1863-01ee-48f3-be26-b50b97b36aca/? It says you can't do this, except perhaps with sendkeys. I wonder if you copied something small to the clipboard every now and then would that clear it down? — Fionnuala, Jan 21 '13 at 14:33

score 1 · Answer 1 · answered Jan 21 '13 at 14:47

1

The functions you are using refer to the windows clipboard. This is different to the Office Clipboard

The only code I've found to clear that clipboard is Application.CommandBars("Clipboard").Controls(4).Execute, but as I have the office clipboard disabled (and, apparently, no way of enabling it), I can't see if that is the actual solution

answered Jan 21 '13 at 14:47

SeanC

15,695
5
45
66

Thanks I gave that a try, but access didn't seem to like it much, "Subscript out of range" I have found that if I keep the clipboard panel open I can click the "Clear All" button while my program is looping through the records. This seems to be the best solution. It is not ideal, but it works. Thanks everyone for your help! – DasPete Jan 21 '13 at 14:55

score 0 · Answer 2 · edited Jul 21 '16 at 10:38

0

I have read elsewhere that this will do what you need:

Option Explicit 

Public Sub ClearClipBoard() 
    Dim oData   As New DataObject 'object to use the clipboard

    oData.SetText Text:=Empty 'Clear
    oData.PutInClipboard 'take in the clipboard to empty it
End Sub

edited Jul 21 '16 at 10:38

rene

41,474
78
114
152

answered Feb 27 '15 at 10:33

JV.

3,193
1
20
21

This is for the Windows clipboard too, not for the Office clipboard. – Andre Jul 21 '16 at 11:25

EvR · Answer 3 · 2020-11-21T19:00:11.853

Try: (old thread, I know ;-) )

    #If VBA7 Then
    Private Declare PtrSafe Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, _
    ByVal iChildStart As Long, ByVal cChildren As Long, _
    ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
    Public Const myVBA7 As Long = 1
#Else
    Private Declare Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, _
                                                              ByVal iChildStart As Long, ByVal cChildren As Long, _
                                                              ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
    Public Const myVBA7 As Long = 0
#End If

Public Sub ClearOfficeClipBoard()
    Dim cmnB, IsVis As Boolean, j As Long, Arr As Variant
    Arr = Array(4, 7, 2, 0)                      '4 and 2 for 32 bit, 7 and 0 for 64 bit
    Set cmnB = Application.CommandBars("Office Clipboard")
    IsVis = cmnB.Visible
    If Not IsVis Then
        cmnB.Visible = True
        DoEvents
    End If

    For j = 1 To Arr(0 + myVBA7)
        AccessibleChildren cmnB, Choose(j, 0, 3, 0, 3, 0, 3, 1), 1, cmnB, 1
    Next
        
    cmnB.accDoDefaultAction CLng(Arr(2 + myVBA7))

    Application.CommandBars("Office Clipboard").Visible = IsVis

End Sub

Can't clear office clipboard with VBA

3 Answers3

Linked