0

I'm trying to read an excel file and highlight the cells with a color with #N/A errors in all the sheets , However i've not been able to find a breakthrough.

Referred to the below article with no luck

How to know if a cell has an error in the formula in C#

Code

public void ExcelScanInternal()
        {
            //
            // Get sheet Count and store the number of sheets.
            //
            //int RowNo = 0;

            xlApp = new xl.Application();
            workbooks = xlApp.Workbooks;
            workbook = workbooks.Open(sFileName);


            int numSheets = workbook.Sheets.Count;

            try
            {
                for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
                {
                    xl.Worksheet sheet = (xl.Worksheet)workbook.Sheets[sheetNum];
                    xl.Range range = sheet.UsedRange;
                    //range = sheet.Cells.SpecialCells(xl.XlCellType.xlCellTypeFormulas, xl.XlSpecialCellsValue.xlErrors);
                    //select all the cells with error formula
                    

                    for (int i = 3; i <= range.Rows.Count; i++)
                    {
                        for (int j = 1; j <= range.Columns.Count; j++)
                        {
                            /*if (range.Cells[i, j] != null && range.Cells[i, j].Value2 != null)
                            //{ 
                                if(range.Formula= "#N/A")
                                {
                                range.Select();
                                range.Borders.LineStyle = xl.XlLineStyle.xlDot;
                                range.Borders.Color = ColorTranslator.ToOle(Color.Red);
                                }
                            }*/
                            bool isNA = xlApp.WorksheetFunction.IsNA(range.Cells[i, j]);
                            
                            if (isNA)
                            {
                                range.Select();
                                range.Borders.LineStyle = xl.XlLineStyle.xlDot;
                                range.Borders.Color = ColorTranslator.ToOle(Color.Red);
                            }

                            workbook.Save();
                            CloseXcel();
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show(ex.StackTrace);
            }

        }

Below are the errors that I got in the process of testing

error stack trace

Also read about Excel WorksheetFunction.IsNA , not sure how to use it in this scenario.

https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.isna

Any help would be appreciated

codrz
  • 3
  • 2
  • does `range.Cells[i, j]` return a value or an `IEnumerable`? The docs seem to suggest it needs a `value`, but from the `Cells` naming convention that seems to imply it's returning back all values between `[i, j]`. No experience with excel interop but that was my first impression. – James Gould Mar 01 '22 at 10:51
  • @JayGould thanks for the reply , it does seem to access the correct cell , However when I try to use range.Cells[i, j].Value , it gives an error does not contain a definition for value. – codrz Mar 01 '22 at 10:58

0 Answers0