none
Loop through the selected cells in excel and update the text RRS feed

  • Question

  • Hi,

    I am creating an excel addin where the cells in the worksheet contain names of individuals. Each cell contains FirstName and Lastname, both separated by a space.

    What I want to do is to reverse the name, i.e. Last name will come before the first name in the cells selected by me.

    I have created a ribbon for excel 2007 and a button inside a ribbon. User will select the cells where names are two be altered and click on this button to reverse the names.

    I have written the following code on click of the button:

    Excel.Range rng = (Excel.Range)Globals.ThisAddIn.Application.ActiveCell;

    This will get me the range of the selected cells.

    Now I want to loop through all cells one by one, read the string in the cell, alter the string and write it back to the cell.

    How do I do this?

    Please help.


    Himanshu Saxena
    Wednesday, November 2, 2011 12:26 PM

Answers

  • Hello,

     

    object selection = ExcelApp.Selection;
    if (selection != null)
    {
        if (selection is Excel.Range)
        {
            Excel.Range selected = selection as Excel.Range;
            Excel.Range cells = selected.Cells;
            for (int i = 1; i <= cells.Count; i++)
            {
                Excel.Range theCell = cells[i, Type.Missing] as Excel.Range;
                string s = theCell.Value.ToString();
                //process the string 
                theCell.Value = s;
                Marshal.ReleaseComObject(theCell);
            }
            Marshal.ReleaseComObject(cells);
        }
        Marshal.ReleaseComObject(selection);
    }


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Wednesday, November 2, 2011 1:01 PM