Clearing office clipboard so that copied data is not shared with other office programs

Question

I am kind of stuck with implementing the important feature which requires data to be cleared from office clipboard the moment it is copied.

The intention is not to share the contents with other office programs e.g. word, powerpoint etc. the scenario is that I have some important content in my excel sheet. Once I make a copy of it, it is soon available on office clipboard. if I keep copying the stuff in excel, it keeps collecting in other office programs. However, the windows clipboard would contain only the recent enter which can be cleared using

System.Windows.Forms.Clipboard.clear():

Is there a way out to clear the office clipboard to?

I googled and found out that there may not be a clear cut solution to the program but getting office clipboard window with the help of FindWindowEx(....) should be possible and then message to can be sent in order to clear the contents. It seems that I am not able to get it this way.

can someone tell if they have experienced the same problem?

Paul Zahra Paul Zahra · Accepted Answer · 2014-01-15T12:59:23

This might give you a nudge in the right direction... Taken From: mrexcel.com

Option Explicit

Sub myClr()
  'Put this sub inta a Sheet Module, like: Sheet1.
  Call ClearOfficeClipboard
End Sub

'Put the code from here down into a Standard Module, like Module1.
Private Declare Function FindWindowEx Lib "user32.dll" _
    Alias "FindWindowExA" (ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As Long

Private Declare Function PostMessage Lib "user32.dll" Alias _
    "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, _
    ByVal wParam As Long, ByVal lParam As Long) As Long

Private Const WM_LBUTTONDOWN As Long = &H201&
Private Const WM_LBUTTONUP As Long = &H202&

'creates a long variable out of two words
Private Function MakeLong(ByVal nLoWord As Integer, ByVal nHiWord As Integer) As Long
    MakeLong = nHiWord * 65536 + nLoWord
End Function


Sub ClearOfficeClipboard()
    Dim hMain&, hExcel2&, hWindow&, hParent&
    Static sTask As String
    '****Dim hClip As Long************************'changed by Lary
    Dim octl, bScreenUpdatingIsOn As Boolean
    Static lParameter As Long, bNotFirstVisibleTime As Boolean, hClip As Long, bNotFirstTime As Boolean

If Not (bNotFirstTime) Then
    lParameter = MakeLong(120, 18)
    sTask = Application.CommandBars("Task Pane").NameLocal
    'Handle for XLMAIN
    hMain = Application.hwnd
    bNotFirstTime = True
End If

With Application.CommandBars("Task Pane")

    If Not .Visible Then
        'assume have to force the window if it is not visible, since it appears that
        ' the window class does not remain loaded if you clear a non-visible clipboard


            'determine current status of screenupdating so that this sub does not change it
            bScreenUpdatingIsOn = Application.ScreenUpdating
            If bScreenUpdatingIsOn Then Application.ScreenUpdating = False

            Set octl = Application.CommandBars(1).FindControl(ID:=809, recursive:=True)
            If Not octl Is Nothing Then octl.Execute
            .Visible = False

            'return to screenupdating on if that is what it was in the beginning
            If bScreenUpdatingIsOn Then Application.ScreenUpdating = True

            If hClip = 0 Then
                hParent = hMain: hWindow = 0
                hWindow = FindWindowEx(hParent, hWindow, "MsoWorkPane", vbNullString)
                If hWindow Then
                    hParent = hWindow: hWindow = 0
                    hClip = FindWindowEx(hParent, hWindow, "bosa_sdm_XL9", vbNullString)
                End If
            End If
    Else
        If Not (bNotFirstVisibleTime) Then** 'find hClip if window is visible
            Do
                hExcel2 = FindWindowEx(hMain, hExcel2, "EXCEL2", vbNullString)

                hParent = hExcel2: hWindow = 0
                hWindow = FindWindowEx(hParent, hWindow, "MsoCommandBar", sTask)
                If hWindow Then
                    hParent = hWindow: hWindow = 0
                    hWindow = FindWindowEx(hParent, hWindow, "MsoWorkPane", vbNullString)
                    If hWindow Then
                        hParent = hWindow: hWindow = 0
                        hClip = FindWindowEx(hParent, hWindow, "bosa_sdm_XL9", vbNullString)
                        If hClip > 0 Then
                            Exit Do
                        End If
                    End If
                End If
            Loop While hExcel2 > 0
            bNotFirstVisibleTime = True
        End If
    End If
End With

If hClip = 0 Then
    MsgBox "Cant find Clipboard window"
    Exit Sub
End If
Call PostMessage(hClip, WM_LBUTTONDOWN, 0&, lParameter)
Call PostMessage(hClip, WM_LBUTTONUP, 0&, lParameter)
End Sub

Clearing office clipboard so that copied data is not shared with other office programs

2 Answers