CODEWITHSUNDEEP
c#excelinteropribbon
Benny
Benny

Reputation: 155

xlApp.ActiveWorkbook returns null

I have a 2010 Excel ribbon add-in. When you press a button it simply writes some lines of data into the excel app. In order to write this data I need to get the active worksheet. And in order to get the active worksheet I need the activeWorkBook. I can get the xlApp fine with 

xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

And it retrieves the xlApp fine But whenever I try to get the active workbook it returns null. No matter what. Unless I restart my computer. Then it works, but only the first time. Below is the whole block of code in context. I'm wondering how to fix the xlApp.ActiveWorkBook being null. So that I can get the active worksheet. And then write data to the active worksheet.

public void sendData()
    {
        Excel.Application xlApp = null;
        Excel.Workbook xlWorkBook = null;
        Excel.Workbooks xlWorkBooks = null;
        Excel.Worksheet xlWorkSheet = null;
        object misValue = System.Reflection.Missing.Value;

        try
        {
            xlApp = new Excel.Application();
            //xlWorkBooks = xlApp.Workbooks;
            //xlWorkBook = xlWorkBooks.Add(Properties.Settings.Default.FileToSend);
            //xlWorkSheet = xlWorkBook.Sheets[1];

            xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
            xlWorkBook = (Excel.Workbook)xlApp.ActiveWorkbook;
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
        }
        catch (Exception ex)
        {
            // handle error...
        }
        finally
        {
            if (xlWorkSheet != null)
                Marshal.ReleaseComObject(xlWorkSheet);

            if (xlWorkBook != null)
                Marshal.ReleaseComObject(xlWorkBook);

            if (xlWorkBooks != null)
                Marshal.ReleaseComObject(xlWorkBooks);

            if (xlApp != null)
                Marshal.ReleaseComObject(xlApp);
        }

Thanks in advance!

Upvotes: 0

Views: 1546

Answers (1)

Govert
Govert

Reputation: 16907

You're probably getting hold of the wrong Excel instance with your call to GetActiveObject. Use the Application reference passed into your COM add-in's OnConnection (or however your ribbon code is bootstrapped).

If you're using the Excel-DNA framework to make your managed Excel add-in, then you get hold of the right Application object with a call to ExcelDnaUtil.Application.

Upvotes: 1

Related Questions