none
Help: How to read the value/text of an Excel Cell? RRS feed

  • Question

  • I'm using VSTO for Office 365  + Visual studio Community 2015.

    And I have no idea how to read the value of a cell in Excel VSTO coding.

    I can write something to the cell by using this: Globals.sheet.cells(1,1) = "blabla..."

    But I can't read the contents in the cell like this strVariable = Globals.sheet1.Cells(1,1).value, or text or some other properties except something like formulalocal.

    What's the exact way to read the text/value of a cell in the Excel worksheet?

    Thanks for help!



    cj

    Saturday, January 7, 2017 5:27 PM

Answers

  • Hi iopcj,

    please use the line of code mentioned below.

    var cellValue = (string)(excelWorksheet.Cells[1, 1] as Excel.Range).Value;

    or

    Excel.Range objRange = (Excel.Range)objYourSheet.Cells[1,1];
    strData = objRange.get_Value(Missing.Value).ToString();

    Hope it solves your issue.

    Regards

    Deepak


    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.

    • Marked as answer by iopcj Monday, January 9, 2017 5:01 AM
    Monday, January 9, 2017 12:33 AM
    Moderator
  • Hi iopcj,

    you had mentioned that,"Is there any idea of reading and changing the label of every OfficeRibbonControl (e.g button/group/dropdown, etc.) in a OfficeRiboon?"

    do you want to change the label of inbuilt controls of ribbon?

    if yes, you can't change anything in inbuilt controls.

    but you can try to change which you had developed using VSTO Ribbon.

    below is the example of that.

    public string _labelTest = string.Empty;
        public string LabelTest { get { return _labelTest; } set { _labelTest = value; } }
    


    public string GetLabelTest(Office.IRibbonControl control)
        {
            switch (control.Id.ToLower())
            {
                case "sklabeltest2":
                    if (Globals.ThisAddIn.LabelTest != string.Empty)
                        return Globals.ThisAddIn.LabelTest;
                    else
                    return "Label Test 2";
                default:
                    return "Label Test 1";
            }
        }
    


      public void SkLabelTest1(Office.IRibbonControl control)
        {
            Globals.ThisAddIn._labelTest = "Changed text";
            Globals.ThisAddIn._ribbon.InvalidateControl("SkLabelTest2");
        }
    
     

    Reference:

    Make changes to buttons and labels in an XML Ribbon during run-time

    Regards

    Deepak


    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.

    • Marked as answer by iopcj Wednesday, January 25, 2017 12:25 PM
    Monday, January 9, 2017 8:31 AM
    Moderator

All replies

  • Hi iopcj,

    please use the line of code mentioned below.

    var cellValue = (string)(excelWorksheet.Cells[1, 1] as Excel.Range).Value;

    or

    Excel.Range objRange = (Excel.Range)objYourSheet.Cells[1,1];
    strData = objRange.get_Value(Missing.Value).ToString();

    Hope it solves your issue.

    Regards

    Deepak


    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.

    • Marked as answer by iopcj Monday, January 9, 2017 5:01 AM
    Monday, January 9, 2017 12:33 AM
    Moderator
  • Thank you for helping me out!

    It's the way to read the content of a cell by using a range to pass the properties. 

    It seems that the way of reading from and writing to a cell in VBA is much easier. :)

    And I may have to get used to this way in VSTO.


    cj

    Monday, January 9, 2017 5:01 AM
  • Hi Deepak,

    Is there any idea of reading and changing the label of every OfficeRibbonControl (e.g button/group/dropdown, etc.) in a OfficeRiboon?  (Something like to change the label of every control in a loop.)

    Thanks for the reply!


    cj


    • Edited by iopcj Monday, January 9, 2017 6:09 AM
    Monday, January 9, 2017 5:03 AM
  • Hi iopcj,

    you had mentioned that,"Is there any idea of reading and changing the label of every OfficeRibbonControl (e.g button/group/dropdown, etc.) in a OfficeRiboon?"

    do you want to change the label of inbuilt controls of ribbon?

    if yes, you can't change anything in inbuilt controls.

    but you can try to change which you had developed using VSTO Ribbon.

    below is the example of that.

    public string _labelTest = string.Empty;
        public string LabelTest { get { return _labelTest; } set { _labelTest = value; } }
    


    public string GetLabelTest(Office.IRibbonControl control)
        {
            switch (control.Id.ToLower())
            {
                case "sklabeltest2":
                    if (Globals.ThisAddIn.LabelTest != string.Empty)
                        return Globals.ThisAddIn.LabelTest;
                    else
                    return "Label Test 2";
                default:
                    return "Label Test 1";
            }
        }
    


      public void SkLabelTest1(Office.IRibbonControl control)
        {
            Globals.ThisAddIn._labelTest = "Changed text";
            Globals.ThisAddIn._ribbon.InvalidateControl("SkLabelTest2");
        }
    
     

    Reference:

    Make changes to buttons and labels in an XML Ribbon during run-time

    Regards

    Deepak


    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.

    • Marked as answer by iopcj Wednesday, January 25, 2017 12:25 PM
    Monday, January 9, 2017 8:31 AM
    Moderator