none
Selection of multiple cells by get_Range RRS feed

  • Question

  • I am trying to select different cells by using the get_Range function. 

    According to the documentation:

    https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.get_range(v=vs.120).aspx

    Cell1
    Type: System.Object

    The name of the range in A1-style notation in the language of the application. It can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). It can also include dollar signs, but they are ignored. You can use a local defined name in any part of the range. If you use a name, the name is assumed to be in the language of the application. This parameter is required.

    I should be able to pass as first parameter something like "C1,D4,H9" as single cells.

    This is my code:

    using _Excel = Microsoft.Office.Interop.Excel;
    
    ///...
    var excelApp = Globals.ThisAddIn.Application;
    string rangeDef = "C4,D5,H9";
    
    var sheet = (excelApp.ActiveSheet as _Excel.Worksheet);
    var range = sheet.get_Range(rangeDef, Type.Missing);
    

    But I get the following error:

    Exception from HRESULT: 0x800A03EC
    
    at Microsoft.Office.Interop.Excel._Worksheet.get_Range(Object Cell1, Object Cell2)
    at TestExcelAddin.TestRibbon.btnSelect_Click(Object sender, RibbonControlEventArgs e) in c:\Users....
    
    {Microsoft.Office.Interop.Excel.Range get_Range(System.Object, System.Object)}
    I am using .Net 4.5.2, office 2013 and 2016. 

    Any idea about this?

    Wednesday, March 29, 2017 8:02 AM

Answers

  • Hi,

    Thanks for answering. 

    I already tested in another computer with fresh installation of office 2013 and visual studio (Version 2017). It stills raises exception.

    Finally I managed to make it work with the following code:

    List<string> cells = new List<string>();
    cells.Add("$C$4");
    cells.Add("$D$5");
    cells.Add("$H$10");
    
    for (int idx = 0; idx < cells.Count; idx++)
        cells[idx] = String.Format("'{0}'!{1}", (excelApp.ActiveSheet as _Excel.Worksheet).Name, cells[idx]);
    string rangeDef = String.Format("={0}", String.Join(";", cells));
    
    var sheet = (excelApp.ActiveSheet as _Excel.Worksheet).get_Range(rangeDef, Type.Missing).Select();

    Looks like the worksheet name is required. But I don't know if this has something to do with localization and missing "default" worksheets names or whatever.

    The .Range[rangeDef] also raises exception.

    Thursday, March 30, 2017 12:55 PM

All replies

  • Hello,

    The code works for me. I suggest you create a new add-in and disable all other add-ins to test the code only to check if the issue still exists. If the issue persists, I suggest you test on other computer.

    To work around, please use var range=sheet.Range[rangeDef];

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 30, 2017 7:56 AM
    Moderator
  • Hi,

    Thanks for answering. 

    I already tested in another computer with fresh installation of office 2013 and visual studio (Version 2017). It stills raises exception.

    Finally I managed to make it work with the following code:

    List<string> cells = new List<string>();
    cells.Add("$C$4");
    cells.Add("$D$5");
    cells.Add("$H$10");
    
    for (int idx = 0; idx < cells.Count; idx++)
        cells[idx] = String.Format("'{0}'!{1}", (excelApp.ActiveSheet as _Excel.Worksheet).Name, cells[idx]);
    string rangeDef = String.Format("={0}", String.Join(";", cells));
    
    var sheet = (excelApp.ActiveSheet as _Excel.Worksheet).get_Range(rangeDef, Type.Missing).Select();

    Looks like the worksheet name is required. But I don't know if this has something to do with localization and missing "default" worksheets names or whatever.

    The .Range[rangeDef] also raises exception.

    Thursday, March 30, 2017 12:55 PM
  • Hello,

    Thanks for sharing your solution with us. I suggest you mark it as answer so that it would help others who have similar issue. Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 4, 2017 5:22 AM
    Moderator