C# Add-In Excel : ActiveSheet

Question

For my first AddIn Excel, i would like select the sheet active and count columns and row in the sheet.

I've created a button in the custom ribbon.

    private void AddValues_Click(object sender, RibbonControlEventArgs e)
        {  
            int lastRow;
            int lastColonne;

            lastRow = Globals.VATTools.Application.Selection.Rows.Count;
            lastColonne = Globals.VATTools.Application.Selection.Columns.Count;

            System.Windows.Forms.MessageBox.Show("Row : " + lastRow + " " + "Columns : " + lastColonne); 

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlApp.Visible = true;             
            Microsoft.Office.Interop.Excel.Workbook xlWb = xlApp.ActiveWorkbook as Microsoft.Office.Interop.Excel.Workbook;
            Microsoft.Office.Interop.Excel.Worksheet xlSheet = xlWb.ActiveSheet;
            Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range("A19"); 
            range.Value = "hello world!";
        }

My first problem : it returns me 1 column and 1 row. But in my sheet, i've 23 columns and 17 rows. Why doesn't this return the correct number of columns and rows in my sheet ?

The second problem : i've an exception on the ActiveSheet :

Microsoft.Office.Interop.Excel.Worksheet xlSheet = xlWb.ActiveSheet;

The error : System.NullReferenceException: 'Object reference not set to an instance of an object.'

I don't understand why i've this error.

Thanks !

Possible duplicate of [What is a NullPointerException, and how do I fix it?](http://stackoverflow.com/questions/218384/what-is-a-nullpointerexception-and-how-do-i-fix-it) — BugFinder, Apr 13 '17 at 14:57
I don't understand, it's the Microsoft.Office.Interop.Excel.Application xlApp who is null. But i declare : Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); — user2814368, Apr 13 '17 at 15:28

score 1 · Accepted Answer · answered Apr 13 '17 at 15:34

First problem :

You need to use the UsedRange property from your worksheet object. The selection will only give you informations about the current portion of cells selected.

Microsoft.Office.Interop.Excel.Range usedRange = xlSheet.UsedRange;
//To get the number of rows/columns
int totalRows = usedRange.Rows.Count;
int totalColumns = usedRange.Columns.Count;

Second problem :

Since you are programming an addin, you don't need to create a new excelApp. The following code will give you access to your document

Microsoft.Office.Interop.Excel.Workbook xlWb = Globals.ThisAddin.Application.ActiveDocument.Workbook;

score 1 · Answer 2 · answered Feb 18 '19 at 20:44

Building on Rafatic's answer (second problem - accessing the document), I found I was able to access the worksheet with the below code. I mention this because Globals.ThisAddIn.Application.ActiveDocument didn't seem to exist.

//using System.Windows.Forms;
//using Excel = Microsoft.Office.Interop.Excel;

Excel.Worksheet xlWb = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;

MessageBox.Show(xlWb.Name);
xlWb.Range["A1"].Value = "Hello World!";

C# Add-In Excel : ActiveSheet

2 Answers2