:  excel stuff :  office clipboard

Office Clipboard API

The office clipboard has a good deal of functional documentation, but no API, or operational documentation - I wanted to figure it out, so here's what I've worked out. It may be mildly inaccurate, it may be wildly wrong, but it fits empirically.

Microsoft's overview of the office clipboard.
A TechRepublic overview of how to use it.

How does it collect?

As mentioned in the MSDN article, "You can copy items while using any program that provides copy and cut functionality, but you can only paste items into Word, Excel, PowerPoint, Microsoft Access, or Microsoft Outlook." - in order to do this, Office attaches itself to the Clipboard viewer chain, with SetClipboardViewer. Thus, it is notified of any clipboard changes (and can hence receive all items copied in any application, but only paste from the office clipboard while in an office app.).

The first office process which starts registers this, with its WndProc as the target. This means any time the contents of the windows clipboard changes, the office process gets a WM_DRAWCLIPBOARD message.

The binaries in the office suite forward these clipboard changes to MSO.DLL (a shared office component), which manages a common list of clipped items in shared memory. (If the office clipboard isn't enabled, it ignores this.) The shared clipboard is protected by a mutex, (which I believe is 'MU_ACB08' (not sure, TBD) ). Accesses to this mutex use a 5000ms timeout.

Office (inside MSO.DLL) then calls OleGetClipboard to retrieve the IDataObject from the clipboard.

If enabled, the tooltip detailling how many items are collected is presented. The limit of 24 items is hardcoded in office, both in terms of an absolute number, and because the buffer that collects this information isn't resizable.

Where does it collect?

Office takes the IDataObject pulled from the windows clipboard, and if it can be written to a stream, it's captured in %LOCALAPPDATA%\msohtml\ - Office has a list of clipboard formats it's happy supporting, and if your dataitem doesn't implement any of them, you get "Item not collected, format not supported by office clipboard." (several custom formats are checked for, as well as 1 (CF_TEXT),2 (CF_BITMAP) & D (UNICODETEXT) )

Adding an API

Since MSO.DLL manages the interesting functionality for the clipboard, it's possible to call into MSO directly to perform some manipulation of the clipboard.

MSOClipHelper provides two objects/interfaces, which can be used to manipulate the office clipboard. Note again, these work for me, and I've tested on several versions of office. But if you're not using a version I've coded for, they won't work.

Example Usage

Public Sub test()
    Dim clp As MSOClipHelperLib.OfficeClipHelperFactory
    Set clp = New MSOClipHelperLib.OfficeClipHelperFactory
    Dim clpi As MSOClipHelperLib.OfficeClipHelper
    Set clpi = clp.OfficeClipHelper
    Debug.Print clpi.Description
    Debug.Print clpi.NumClipItems
    Call clpi.Clear
End Sub

Here, we create an OfficeClipHelper using the OfficeClipHelperFactory - the factory does a one off determination of how to call MSO.



The output in the immediate window is from

We then clear the office clipboard.

Download MSOClipHelper

As you might have guessed from the above, I make no guarantees as to the efficacy of this code. Use at your own risk.


Last updated 01/2010