0

For some reason I'm unable to retrieve the value of a cell using C#'s Interop.Excel library. I'm getting a null reference exception when trying to access the .Value property of a Range object. The sheet in question definitely has a value in the cell I'm targeting. Any tips? Please see the code below:

using System;
using System.Data;
using System.Collections.Generic;
using Microsoft.Office.Interop.Excel;

namespace C__Test
{
    class Program
    {
        static void Main(string[] args)
        {
            Application xlApp = new Application();
            xlApp.DisplayAlerts = false;
            xlApp.ScreenUpdating = false;
            xlApp.Visible = false;
            Workbook xlBook = xlApp.Workbooks.Open(@"C:\Users\tsgardn\Desktop\test.xlsb",Type.Missing,true);
            _Worksheet xlSheet = (_Worksheet)xlBook.Worksheets["Scorecard"];
            xlBook.RefreshAll();
            Range ranges = xlSheet.UsedRange;

            foreach (Range c in ranges.Cells) {
                Console.WriteLine("Address: " + c.Address + "Value: " + c.Value);
            }

            xlBook.SaveAs(@"C:\Users\tsgardn\Desktop\Corelogic Flood Scorecard - New.xlsb");
            xlBook.Close(false);
        }
    }
}
TylerG
  • 23
  • 4
  • For which reference you are getting the exception? – Sarker Nov 30 '18 at 15:13
  • An unhandled exception of type ‘System.NullReferenceException’ occurred in C# Test.dll: Object reference not set to an instance of an object. Trying to access the value property of the c in ranges.Cells. – TylerG Nov 30 '18 at 15:35
  • I don't think you need to do the foreach loop anyway. Can't you just use `ranges` directly? Although plural, the `Cells` property of a range will just return a range for the *cells you specify* or will just return a reference to the original range object if no cell address is specified as a parameter. e.g. `range.Cells(5,3)` will return a range for the cell specified, whereas `range.Cells` just references the original range object. – T_Bacon Nov 30 '18 at 15:58
  • ranges.Cells(5,3) can’t be used like a method. I have to access it as ranges.Cells[5,3] and no methods or properties are available for it with dot notation at the end. I’m pretty much at a loss on how to read the value from a cell. I’ve also tried Console.WriteLine(xlSheet.Range[“A1”].Value); and it doesn’t work either. Well, I shouldnt say it “doesn’t work” - it just returns an empty string, but there is in fact a value in the cell it’s referencing. – TylerG Nov 30 '18 at 16:17

0 Answers0