A way to update the active sheet from the active Excel workbook using c# ?

Question

I would like to update values in the current sheet in current document regardless of file name of excel.

In vb6.0 we could do

Set AppExcel = GetObject(, "Excel.Application")
Set SheetExcel = AppExcel.ActiveWorkbook.ActiveSheet

How ever I have been trying to do same in C# .

Is there a way to do same in C#.

Answer 1

Here is a sample console application that uses Excel Automation to access the ActiveSheet.

using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ExcelAutomationConsole
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            _Application excelApp = null;
            try
            {
                excelApp = new Application();
                excelApp.Visible = true;

                Missing missing = Missing.Value;
                excelApp.Workbooks.Add(missing);
                // or
                //excelApp.Workbooks.Open("file.xlsx", missing, missing, missing, missing,
                //                        missing, missing, missing, missing,
                //                        missing, missing, missing, missing,
                //                        missing, missing);

                Worksheet sheet = excelApp.ActiveWorkbook.ActiveSheet as Worksheet;
                Range r = sheet.Cells[1, 1] as Range;
                r.FormulaR1C1 = "Test";
            }
            finally
            {
                excelApp.Quit();
            }
        }
    }
}

Answer 2

using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Reflection;

public class MyClass
{
    public static void Main()
    {
     object xlApp  = Marshal.GetActiveObject("Excel.Application");
     Type t = xlApp.GetType();

     t.InvokeMember("Quit", BindingFlags.InvokeMethod, null, xlApp, null);

    }
}

I have modified the code based on the example given here.

Having said that, I think you could write code in vb.net (and use GetObject) and use the library from c#.

OR

You can refer the Microsoft.VisualBasic.dll.
And make a call to GetObject.

EDIT: Why should you need such a thing? Wouldn't creating an Excel Addin help?

If you have to write code in c# anyway, take care of releasing COM instances that you have created using Marshal.ReleaseComObject

EDIT2: In order to get the reference to ActiveSheet, you could write

object sheet = t.InvokeMember("ActiveSheet", BindingFlags.GetProperty, null, xlApp, null);

Again, I suggest you not take this route.
Write code in VB6 to do what you need & call it from c#/vb.net.

Answer 3

In short, the answer is, absolutely, yes.

But just so you are aware, your approach using 'GetObject' to get the user's existing Excel application can be error prone. If there is only one Excel application running, then 'GetObject' will work fine. If there is more than one Excel application that is currently open, however, then you cannot control which Excel application is returned from the 'GetObject' function -- the Excel instance returned may not be the one that you want.

You might want to think about creating a managed COM Add-in, which runs within the Excel application itself and, theerfore, never has any issues as to "which" Excel application instance it is controlling. To do this, you could start by having a look at the article How to build an Office COM add-in by using Visual C# .NET.

If you know the process id or the main window handle (a.k.a. "Hwnd") of the Excel application instance that you wish to control, then you can access this precise instance. To learn how to do this, you can have a read Andrew Whitechapel's discussion of the subject here. The section that you would be interested in begins at the line reading "To get hold of the Excel Application object, this is what we’ll do." The discussion is a bit complicated, but if you follow his code to the letter it works exactly as described.

If you want to go forward with the 'GetObject' approach, then the following code example uses early binding to get the active sheet of a currently running Excel application. It assumes that you have the 'using' statement using Excel = Microsoft.Office.Interop.Excel as well as using System.Runtime.InteropServices at the top of your code module:

// Note:
// using System.Runtime.InteropServices;
// using Excel = Microsoft.Office.Interop.Excel;

Excel.Application excelApp = 
    Marshal.GetActiveObject("Excel.Application");

object activeSheet = excelApp.ActiveSheet;

If you were using late-binding, then it could look like this:

// Note:
// using System.Runtime.InteropServices;

object excelApp = 
    Marshal.GetActiveObject("Excel.Application");

object activeSheet = 
    excelApp
        .GetType()
        .InvokeMember(
            "ActiveSheet", 
            BindingFlags.GetProperty, 
            null, 
            excelApp, 
            null);

Hope this helps...

Follow-up Reply:

Hi, Even If there are multiple excel applications running My logic is to use the application that is currently being highlighted ie the currently active (having focus).

Ah, you should have said... Your original question states that you would like to duplicate the behavior of the GetObject method as called from VB 6.0. For that, the answers you have gotten here all answer this correctly.

So, first, keep in mind that what you are now asking is well beyond the capabilites of VB 6.0's GetObject method. To accomplish this, you will need to:

1. Get the window handle of the active application by calling the GetForegroundWindow Windows API function.
2. Iterate all currently-running Excel processes via Process.GetProcessesByName("Excel") and compare the Process.MainWindowHandle for each running Excel process to the window handle you got earlier using the GetForegroundWindow API. Assuming that you have a match, then you have confirmed that an Excel application is the currently active application. If no match is found, then no Excel application currently has the focus, and you might want to report this to the user.
3. After you have confirmed that the active window is a running Excel application, you can make use of the window handle you got using the GetForegroundWindow API in order to get the Excel application object that you can control. Do to this, see the article Getting the Application Object in a Shimmed Automation Add-in by Andrew Whitechapel, beginning at the section reading "To get hold of the Excel Application object, this is what we’ll do." It's a complex topic, but the description in the article is excellent and the code example works perfectly. In your case, however, you will want to replace the first line of the code int hwnd = (int)Process.GetCurrentProcess().MainWindowHandle; with the window handle of the active window you found previously. In other words, something like this: int hwnd = theHwndOfTheActiveWindowYouFoundEarlier.

I realize that this is a lot to digest, but if you follow these steps, it will run just fine. The hardest part is Andrew Whitechapel's code, but this will run as-is, other than needing to change the first line. Good luck!

-- Mike