none
EXCEL: How do I access programmatically the address of the copied selection from the clipboard RRS feed

  • Question

  • <newbie alert>

    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

    Any help appreciated!


    • Edited by drkhskkrs Monday, October 20, 2014 7:25 PM
    Monday, October 20, 2014 7:22 PM

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 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

    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 e neat solution is appreciated, but I am content with what I have for the time being. Thanks, guys.


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

All replies

  • Just one more thing:

    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);

    Tuesday, October 21, 2014 1:52 PM
  • Hi,

    Based on the description, you want to get the address of the copied cell.  As far as I know, we can't get the address of copied cells, as a workaournd we can get the address of active cell or the current selection cells via Application.Selection or Application.ActiveCell.

    Here is the sample to get the address of the active cell:

    Application.ActiveCell.Address

    And based on the code you are using Clipboard object, this is the class provied by .Net Framework. If you have any issue using this object, I suggest that you get more effectve response from link below:
    .NET Framework Class Libraries

    Hope it is helpful.

    Best regards

    Fei


    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.

    Wednesday, October 22, 2014 2:47 AM
    Moderator
  • Thanks, Fei,


    You are right, I want to get the address of the copied cell. As to "As far as I know, we can't get the address of copied cells": The funny thing is it must be somewhere. After all, any user can use the "Paste Link"-function. Does excel use some kind of "private" clipboard for internal purposes?

    And yes, the "ActiveCell.Address" is the workaround I currently am using, but this is not the ideal situation. 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.

    Thanks for directing me to ".NET Framework Class Libraries", I'll try it there as well.

    Best

    Dirk


    • Edited by drkhskkrs Wednesday, October 22, 2014 7:00 AM
    Wednesday, October 22, 2014 6:49 AM
  • Hi drkhskkrs,

    Thanks for the detail information.

    >>Does excel use some kind of "private" clipboard for internal purposes?<<

    No, as far as I know, the Excel object model havn't publiced the clipboard object.

    >>After all, any user can use the "Paste Link"-function. Does excel use some kind of "private" clipboard for internal purposes?<<

    Another workaround is that we can call the paste link function to set the link value to a cell then we can read the value. Here is the code for your reference:

    Worksheets("Sheet1").Range("D1").Select
    ActiveSheet.Paste Link:=True

    Best regards

    Fei


    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 9:59 AM
    Moderator
  • Thanks, Fei.

    In actual fact it is not that easy: The copied element may be a range. What I need to do is to look at the ActiveSelection after the past.link, take the references from the top leftmost (e.g. "Sheet1!F8") and the bottom rightmost cell (e.g. "Sheet1!H11") and re-construct the range to "Sheet1!F8:H11".

    But yes, then it should work.

    Friday, October 24, 2014 3:58 PM
  • 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 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

    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 e neat solution is appreciated, but I am content with what I have for the time being. Thanks, guys.


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