Click here to Skip to main content
13,346,843 members (24,781 online)
Click here to Skip to main content
Add your own
alternative version


10 bookmarked
Posted 25 Jan 2011

Getting the Excel Range object from the Clipboard through the IStream interface

, 25 Jan 2011
Rate this:
Please Sign up or sign in to vote.
This article demonstrates how to get the Excel Range object from the Clipboard using the CF_LINKSOURCE Clipboard format.


When you copy selected cells from Excel to Clipboard, you have a couple of data formats in it. If you need to paste just data, then even CF_CSV may satisfy. But what to do if you want to access the Range object representing the copied cells? Use CF_LINKSOURCE!


In our company, we're now working on a project named Converter that imports data from Excel to our applications. The user has to drag or copy/paste some cell range from Excel to Converter and setup a data transfer scheme (from which cell to which destination data will be transferred; whether the first data row contains fieldnames, etc.). Finally, the user gets the scheme that could be used to transfer data from as many Excel Workbooks as needed (of course, Workbooks must have the same layout). Creating such a scheme requires some info about the Range layout, cell names, etc., so you need the Range object to get it. This article describes how we can do this by using the IStream interface from the CF_LINKSOURCE Clipboard format.

Main algorithm

The main algorithm is as simple as the GetRange method from the sample code:

public static Range GetRange(IDataObject dataObject)
    IStream iStream = IStreamFromDataObject(dataObject); 
    IMoniker compositeMoniker = IMonikerFromIStream(iStream); 
    return RangeFromCompositeMoniker(compositeMoniker); 

Don't get confused with IDataObject - it's a System.Runtime.InteropServices.ComTypes.IDataObject rather than System.Windows.Forms.IDataObject.

Inside look

Getting IMoniker

IStreamFromDataObject is simple and I'll skip it. To get IMoniker from IStream, we need the P/Invoked function OleLoadFromStream from ole32.dll.

[DllImport("ole32.dll", CharSet = CharSet.Unicode, ExactSpelling = true)]
public static extern HRESULT OleLoadFromStream( 
                        IStream pStm, 
                        [In] ref Guid riid, 
                        [MarshalAs(UnmanagedType.IUnknown)] out object ppvObj);

HRESULT is a struct that had been taken from

You pass the stream from the previous step to OleLoadFromStream and... get an error! It takes some time to figure out that you need to rewind a stream! OK, let's do that.

iStream.Seek(0, 0, IntPtr.Zero);

And voila! We get the object from OleLoadFromStream that successfully casts to IMoniker.

Getting Range

If you take a look at the CLSID of the moniker, you'll see that it's a composite moniker. If we lived in a perfect world, moniker.BindToObject() would give us the Range object. But in real life, monikers from Microsoft Office can be bound only to the file object (Workbook in the case of Excel), so we need to split the composite moniker and do some work for Excel.

private static List<IMoniker> SplitCompositeMoniker(IMoniker compositeMoniker)
    if (compositeMoniker == null)
        throw new ArgumentNullException("compositeMoniker", 
                  "compositeMoniker is null.");

    List<IMoniker> monikerList = new List<IMoniker>();

    IEnumMoniker enumMoniker;
    compositeMoniker.Enum(true, out enumMoniker);
    if (enumMoniker != null)
        IMoniker[] monikerArray = new IMoniker[1];
        IntPtr fetched = new IntPtr();
        HRESULT res;
        while (res = enumMoniker.Next(1, monikerArray, fetched))
        return monikerList;
        throw new ApplicationException("IMoniker is not composite");

Now we get the List with the file moniker and item moniker. To bind to the Workbook, we need to just call IMoniker.BindToObject:

IBindCtx bindctx;
if (!ole32.CreateBindCtx(0, out bindctx) || bindctx == null)
    throw new ApplicationException("Can't create bindctx");
object obj;
Guid workbookGuid = Marshal.GenerateGuidForType(typeof(Workbook));
monikers[0].BindToObject(bindctx, null, ref workbookGuid, out obj);
Workbook workbook = obj as Workbook;

But a call to the item moniker's BindToObject gets us an error (practically, we can do a successful call to BindToObject with the IID of IUnknown but the returning object would actually be the Workbook object, sad but true.) Game over? Not so fast. We still can get the display name from the item moniker with IMoniker.GetDisplayName(). For Excel Range, it will be something like "!blahblahblah!R1C1:R3C3" where blahblahblah is the sheet name and R1C1:R3C3 identifies the range inside the sheet. I wrote a helper class that parses DisplayName and gets the Range from the Workbook object. The only interesting moments are:

  1. The user can copy whole rows or whole columns that are identified as "Rx:Ry" and "Cx:Cy", respectively.
  2. You must convert R1C1 names to the current Excel Reference Style (mostly A1) to get the Range.

The helper can be used as follows:

ExcelItemMonikerHelper helper = new ExcelItemMonikerHelper(monikers[1], bindctx);
Range range = helper.GetRange(workbook);

So finally, we get Range as if the item moniker's BindToObject really works :)


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Alexey Merson
Software Developer
Russian Federation Russian Federation
No Biography provided

You may also be interested in...


Comments and Discussions

AnswerDuplicate Pin
Clifford Nelson3-Jul-13 13:02
memberClifford Nelson3-Jul-13 13:02 
GeneralRe: Duplicate Pin
Alexey Merson3-Jul-13 20:54
memberAlexey Merson3-Jul-13 20:54 
AnswerRe: Duplicate Pin
Clifford Nelson5-Jul-13 10:22
memberClifford Nelson5-Jul-13 10:22 
GeneralRe: Duplicate Pin
Alexey Merson5-Jul-13 10:54
memberAlexey Merson5-Jul-13 10:54 
AnswerRe: Duplicate Pin
Clifford Nelson5-Jul-13 11:16
memberClifford Nelson5-Jul-13 11:16 
If you are not responsible for the posting, and it was originally yours then there is no problem, but somebody has then plagerized you. If it was illegally copied, you probably want to respond.
QuestionLanguage independent item moniker Pin
gaditya22-Mar-13 17:35
membergaditya22-Mar-13 17:35 
AnswerRe: Language independent item moniker Pin
Alexey Merson25-Mar-13 8:44
memberAlexey Merson25-Mar-13 8:44 
GeneralRe: Language independent item moniker Pin
gaditya26-Mar-13 11:33
membergaditya26-Mar-13 11:33 
GeneralShould be filed as a Tip/Trick Pin
Slacker00725-Jan-11 23:09
memberSlacker00725-Jan-11 23:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.180111.1 | Last Updated 25 Jan 2011
Article Copyright 2011 by Alexey Merson
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid