Get Excel Active Worksheet with C#

Question

I am wondering why this code gets the open worksheet when I run it in debug, and catch an exception when I run it with no debug..

Excel.Application xlApp = null;
Excel.Workbook xlWBook = null;
Excel.Worksheet xlWSheet = null;
Excel.Range xlRange = null;
try {
    xlApp = (Excel.Application) Marshal.GetActiveObject("Excel.Application");
    xlApp.Visible = true;
    xlWBook = xlApp.ActiveWorkbook;
    xlWSheet = xlWBook.ActiveSheet;
    xlRange = xlWSheet.UsedRange;
    logResult = logResult + "Agganciato " + xlWBook.Name + " \r\n";
} catch {
    logResult = logResult + "Nessun file aperto rilevato. \r\n";
}

Any suggestion?

Are you running the code when Excel starts? If Excel has not added a workbook to the instance of Excel, you can't get the object. You could try and catch the exception to understand, why it throws the error. Use 'Catch (ex expression)' and then log the value of ex. — STHOH, Jul 01 '22 at 12:19
I am running the code after the Excel workbook was already open for a while. "Run with debug" from the project completes the try-segment with success, "Run without debug" throws an exception, so I have trouble understanding what is going on.. — Sara Vecchio, Jul 02 '22 at 16:12
And what info does the exception give you? If you catch it as suggested above. — STHOH, Jul 05 '22 at 07:43

score 0 · Answer 1 · answered Jul 01 '22 at 16:43

The following shows how one can use Excel Interop to either interact with an open Excel workbook or create a new workbook if one isn't open.

Try the following:

Add Reference (Option 1)

Download / install NuGet package: Microsoft.Office.Interop.Excel

Add Reference (Option 2)

In VS menu, click Project
Select Add Reference...
Click COM
Check Microsoft Excel xx.x Object Library (ex: Microsoft Excel 16.0 Object Library)

Add the following using directives:

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

CreateExcelWorkbook

public void CreateExcelWorkbook(string filename)
{
    bool isExcelAlreadyRunning = false;
    string logResult = string.Empty;
    Excel.Application xlApp = null;
    Excel.Workbook xlWBook = null;
    Excel.Worksheet xlWSheet = null;
    Excel.Range xlRange = null;
    try
    {
        try
        {
            //if Excel isn't open, this will throw a COMException
            xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");

            //set value
            isExcelAlreadyRunning = true;
        }
        catch (System.Runtime.InteropServices.COMException ex)
        {
            //create new instance
            xlApp = new Excel.Application();
        }

        //whether or not to make Excel visible
        xlApp.Visible = true;

        //prevent prompting to overwrite existing file
        xlApp.DisplayAlerts = false;

        //disable user control while modifying the Excel Workbook
        //to prevent user interference
        //only necessary if Excel application Visibility property = true
        //need to re-enable before exitin this method
        //xlApp.UserControl = false;

        if (xlApp.Workbooks.Count > 0)
            xlWBook = xlApp.ActiveWorkbook;
        else
            xlWBook = xlApp.Workbooks.Add();

        if (xlWBook.Worksheets.Count > 0)
            xlWSheet = xlWBook.ActiveSheet;
        else
            xlWSheet = xlWBook.Sheets.Add();

        xlRange = xlWSheet.UsedRange;

        //set value
        xlWSheet.Cells[1, 1] = $"Test {DateTime.Now.ToString("HH:mm:ss.fff")}";

        //save Workbook - if file exists, overwrite it
        // xlWBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookDefault, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
        xlWBook.SaveAs(filename, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

        logResult = logResult + "Agganciato " + xlWBook.Name + " \r\n";
    }
    catch
    {
        logResult = logResult + "Nessun file aperto rilevato. \r\n";
    }
    finally
    {
        if (xlWBook != null)
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWSheet);

            xlWSheet = null;
            xlRange = null;

            if (!isExcelAlreadyRunning)
            {
                //close workbook
                xlWBook.Close(false);
            }

            //release all resources
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWBook);

            xlWBook = null;
        }

        System.Threading.Thread.Sleep(150);

        if (xlApp != null)
        {
            if (!isExcelAlreadyRunning)
            {
                xlApp.Quit();
            }

            //release all resources
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);

            xlApp = null;

            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();

            System.Threading.Thread.Sleep(175);
        }
    }
}

Usage:

CreateExcelWorkbook(filename);

//the following is necessary otherwise the Excel process seems to persist in Task Manager
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

Resources:

Get Excel Active Worksheet with C#

1 Answers1

Linked