How do I get and compare the contents of a cell in an Excel spreadsheet by name?

Question

I'm trying to compare a cell with a string to replace it if it is equal. But when I try to do the code below the 0x800A03EC error occurs.

                int cont = 0;
                string cell;
                do
                {
                    cont++;
                    cell = rCol.ToUpper() + cont.ToString(); // = "D1"
                    string cellData = ((Excel.Range)sheet.Cells[cell]).Value2.ToString();

                    if (cellData == from)
                    {
                        sheet.Cells[cell] = to;
                    }
                } while (sheet.Cells[cell] == null);

How can I do this?

Did you try changing `Cells` to `Range`? – Mr. Mascaro Dec 01 '14 at 15:38 — Mr. Mascaro, Dec 01 '14 at 15:38
I tried and did not work – André Oliveira Dec 01 '14 at 16:15 — André Oliveira, Dec 01 '14 at 16:15

MattSull · Accepted Answer · 2014-12-02T22:40:22.000

If you know the cell you want to check, for example A1, you can do it like this:

using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {
            // create app
            var excelApp = new Excel.Application();
            // open workbook
            var workbook = excelApp.Workbooks.Open(
                @"C:\Users\Home\Documents\Book1.xlsx",
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            // open sheet
            var sheet = (Excel.Worksheet)workbook.Sheets[1];

            // create some variables
            var from = "Pete";
            var to = "Dave";
            // compare cell A1 [1,1] with 'from'
            if (string.Equals(sheet.Cells[1,1].Value, from))
            {
                sheet.Cells[1, 1].Value = to;
            }

            // save the workbook
            workbook.Save();
            // close the workbook and release resources
            workbook.Close(true, workbook.Path);
            Marshal.ReleaseComObject(workbook);
            workbook = null;
        }
    }
}

score 0 · Answer 2 · edited May 23 '17 at 12:33

Try this to get at a simple range:

int row = 1;
string col = "D";        
string text = sheet.get_Range(col + row.ToString()).Value;

The 0x800A03EC error is a value returned by Excel, which means NAME_NOT_FOUND (see this SA question). Looks like you were passing a parameter that Excel could not find, probably because you were passing a string ("D1"), rather than two integer parameters (4,1).

It's impossible to tell where you are getting the rCol.ToUpper() value, without seeing more of your code. However, if you are attempting to go through a series of columns and rows to check for an equality condition (that's what it looks like you are attempting), you will quickly run into the pesky problem of how to increment the column value using capital letters (just try it; not much fun!).

One solution I did in VB recently was to use the native Excel function index, which uses numeric values to get at a particular cell. You would need to cast an object of the type Excel.WorksheetFunction to use that function. But I have since discovered there are easier solutions than using an Excel function:

using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace exceltest
{
class Program
{

    static void Main(string[] args)
    {
        Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook workbook = xl.Workbooks.Open(@"C:\test.xlsx");
        Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Sheets[1];
        xl.Visible = true;

        //use this if you want to use native Excel functions (such as index)
        Microsoft.Office.Interop.Excel.WorksheetFunction wsFunc = xl.WorksheetFunction;

        int maxNum = 100; // set maximum number of rows/columns to search

        string from = "blah";
        string to = "blum";

        //this is pretty slow, since it has to interact with 10,000 cells in Excel
        // just one example of how to access and set cell values           
        for (int col = 1; col <= maxNum; col++)
        {
            for (int row = 1; row <= maxNum; row ++)
            {
                Range cell = (Range)sheet.Cells[row, col];
                if ((string)cell.Value == from) //cast to string to avoid null reference exceptions
                {
                    cell.Value = to;
                }
            }
        }
    }
}

}

How do I get and compare the contents of a cell in an Excel spreadsheet by name?

2 Answers2