none
How do I access programmatically the address of the copied selection from the clipboard in Excel RRS feed

  • Question

  • <disclaimer> I already posted this question in the VSTO forum, an admin referred me to this forum </disclaimer>

    All, I searched for hours on the web, without luck, and finally decided that I need your help.

    Here's what I want to achieve:

    The user copies a cell (or a range), say A3, and - when she hits a button - I need to get access to the address of the cell (to create a link) programmatically.

    Accessing the clipboard in text format is easy:

    string clip;
    
    if (Clipboard.ContainsText()) clip = Clipboard.GetText();

    I also found that it is possible to access the clipboard in different formats, like this

    var dataObj = Clipboard.GetDataObject();
    var format = DataFormats.CommaSeparatedValue;
    
    if (dataObj != null && dataObj.GetDataPresent(format))
    {
        var csvData = dataObj.GetData(format);
        //...
    }

    but I couldn't for the life of me find which format contains the link and how to get it. (I cycled through all formats offered by Clipboard.GetDataObject().GetFormats(), but some returned inscrutable streams I couldn't make sense of.

    Background info:

    A. The Link must be there, because I can use "paste link" which creates an absolute reference

    B. I'm using Excel 2010 and VS2010 - C# under Win7

    C. The code runs in a custom task pane

    There seems to be an option to access the clipboard as "XML worksheet", sample code below.

    However, the resulting document seems to represent a worksheet without any reference to the original source, including the address where it was copied from.

    var dataObject = Clipboard.GetDataObject();
    var mstream = (MemoryStream)dataObject.GetData("XML Spreadsheet");
    mstream.SetLength(mstream.Length - 1);
    var rdr = new System.IO.StreamReader(mstream);
    var xml = XElement.Load(mstream);
    Obviously, as a "workaround" I can use
    Application.ActiveCell.Address
    to get the address. But this is not the ideal thing. The functionality I am working on shall ultimately work with a lot of other applications, not just Excel alone. From a usability point of view, the clipboard would be the single uniform mode supported in all these apps.

    Furthermore, the "ActiveCell" is specific to the ActiveWorksheet. With this "workaround" I can never refer to cells not at the current worksheet, which is a serious limitation.


    Any help most appreciated!


    Wednesday, October 22, 2014 7:59 AM

Answers

  • So,

    and thanks for everybody who managed to read till this point. I finally figured it out. My solution is still awkward, as I can't get my head around the actual structure of the stream from the clipboard, but I do find what I am looking for:

                protected override void WndProc(ref Message m)
                {
                    const int WM_PASTE = 0x0302;
                    var enc = new System.Text.UTF7Encoding();
                    string buffer, rangeAddress;
    
                    if (m.Msg == WM_PASTE)
                    {
                        if (Clipboard.ContainsText())
                        {
                            string clip = Clipboard.GetText();
                            var dataObject = Clipboard.GetDataObject();
                            var mstream = (MemoryStream)dataObject.GetData("Link Source", true);
                            if(mstream == null) return;
                            var rdr = new System.IO.StreamReader(mstream, enc, true);
                            buffer = rdr.ReadToEnd();
                            buffer = StripWeirdChars(buffer);
                            int IndexExcl = buffer.IndexOf("!");
                            if (IndexExcl >= 0)
                            {
                                rangeAddress = buffer.Substring(IndexExcl + 1, buffer.Length - IndexExcl - 4);
                                // do whatever you want to do with it, e.g.:Globals.ThisAddIn.Application.ActiveCell.Value = rangeAddress;
                                return;
                            }
                        }
                    }
                    base.WndProc(ref m);
                }
            }

    The key here is obviously the particular format for the GetData: "Link Source".

    Reading the resulting stream produces a string with a lot of weird characters, but also the name of the sheet and coordinates of the copied range. I strip the weird chars using a straightforward

                public static string StripWeirdChars(string source)
                {
                    string res = "";
                    foreach (char c in source) if ((int)c >= 32) res += c;
                    return res;
                }

    There still are some odd chars I can't make sense of, but the good news is that after the first exclamation mark you'll find the address of the range (with some trailing rubbish of fixed length). This works even when the range was copied from another worksheet and even if this worksheet has odd chars (like German umlauts) in its name.

    There certainly is a much neater solution out there (thanks @ Fred for pointing this out)

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

    www. codeproject.com/Articles/149009/Getting-the-Excel-Range-object-from-the-Clipboard

    but the code I found there is not complete (the "obvious" parts are left out) and I couldn't get the thing to work due to my incompetence and missing experience with IStream to begin with.

    Any help in using this to get a neat solution is appreciated, but I am content with what I have for the time being.

    Thanks, guys.

    • Marked as answer by drkhskkrs Saturday, October 25, 2014 8:53 AM
    Saturday, October 25, 2014 8:53 AM

All replies

  • Hello drkhskkrs,

    Please check this article written by Alexey Merson:

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

    It could return a range address as “A2:A5” if I copied an area in excel as below:

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 23, 2014 8:23 AM
    Moderator
  • So,

    and thanks for everybody who managed to read till this point. I finally figured it out. My solution is still awkward, as I can't get my head around the actual structure of the stream from the clipboard, but I do find what I am looking for:

                protected override void WndProc(ref Message m)
                {
                    const int WM_PASTE = 0x0302;
                    var enc = new System.Text.UTF7Encoding();
                    string buffer, rangeAddress;
    
                    if (m.Msg == WM_PASTE)
                    {
                        if (Clipboard.ContainsText())
                        {
                            string clip = Clipboard.GetText();
                            var dataObject = Clipboard.GetDataObject();
                            var mstream = (MemoryStream)dataObject.GetData("Link Source", true);
                            if(mstream == null) return;
                            var rdr = new System.IO.StreamReader(mstream, enc, true);
                            buffer = rdr.ReadToEnd();
                            buffer = StripWeirdChars(buffer);
                            int IndexExcl = buffer.IndexOf("!");
                            if (IndexExcl >= 0)
                            {
                                rangeAddress = buffer.Substring(IndexExcl + 1, buffer.Length - IndexExcl - 4);
                                // do whatever you want to do with it, e.g.:Globals.ThisAddIn.Application.ActiveCell.Value = rangeAddress;
                                return;
                            }
                        }
                    }
                    base.WndProc(ref m);
                }
            }

    The key here is obviously the particular format for the GetData: "Link Source".

    Reading the resulting stream produces a string with a lot of weird characters, but also the name of the sheet and coordinates of the copied range. I strip the weird chars using a straightforward

                public static string StripWeirdChars(string source)
                {
                    string res = "";
                    foreach (char c in source) if ((int)c >= 32) res += c;
                    return res;
                }

    There still are some odd chars I can't make sense of, but the good news is that after the first exclamation mark you'll find the address of the range (with some trailing rubbish of fixed length). This works even when the range was copied from another worksheet and even if this worksheet has odd chars (like German umlauts) in its name.

    There certainly is a much neater solution out there (thanks @ Fred for pointing this out)

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

    www. codeproject.com/Articles/149009/Getting-the-Excel-Range-object-from-the-Clipboard

    but the code I found there is not complete (the "obvious" parts are left out) and I couldn't get the thing to work due to my incompetence and missing experience with IStream to begin with.

    Any help in using this to get a neat solution is appreciated, but I am content with what I have for the time being.

    Thanks, guys.

    • Marked as answer by drkhskkrs Saturday, October 25, 2014 8:53 AM
    Saturday, October 25, 2014 8:53 AM