Excel Worksheet Modifying Current Sheet C#

Question

Is there anyway that you can modify an already opened worksheet in Excel. Their MSDN page gives you what is below. This adds a new workbook and worksheet everytime. I have the code opening and existing worksheet, and I just want it to replace the value in the cell and the close. Any help is greatly appreciated.

using Excel = Microsoft.Office.Interop.Excel; //Excel Reference

 public virtual Object ActiveSheet { get; set; }

 private void button15_Click(object sender, EventArgs e)//Generate Model and Part Numbers
    {
        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;

        //Start Excel and get Application object.
        oXL = new Excel.Application();
        oXL.Visible = true;

        //Get a new workbook.
        oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
        oSheet = (Excel._Worksheet)oWB.ActiveSheet;  
        oSheet.Cells[6, 4] = "0"; //Change Value in Cell in Excel Cell Location [y-axis, x-axis]
     }

I called Microsoft and they were going to charge me 500 bucks for this answer so anyone who gets this praise to you!! — Russell Saari, Sep 19 '11 at 16:48

Yahia · Accepted Answer · 2011-09-19T20:37:00.940

3

you can iterate over oXL.Workbooks to access the currently open Workbooks... the currently active Workbook is available via oXl.ActiveWorkbook.

EDIT - as per comment:

Either use oXL.Windows to find the already open window and call Activate on that before before accessing ActiveWorkbook or just call oXL.ActiveWindow.Close() or oXL.ActiveWindow.ActivatePrevious() to get the already open window...

EDIT 2 - as per comment final part of solution:

use

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

edited Sep 19 '11 at 20:37

answered Sep 19 '11 at 16:43

Yahia

69,653
9
115
144

Yeah I guess I can't figure it out there is no Object ActiveWorkbook at least not one that I could find – Russell Saari Sep 19 '11 at 16:44
1

see my updated answer and the link - there is `oXl.ActiveWorkbook` ! – Yahia Sep 19 '11 at 16:50
what is oXL = then it can't be new Excel.Application that will open another excel window and error out. Is there anything else I can make oXL equal to – Russell Saari Sep 19 '11 at 16:50
oXL is the new Excel application - since Excel won't be started a second time ("Singleton") everything is fine... – Yahia Sep 19 '11 at 16:54
I am not sure why but when I run that code it opens a new excel window other then the one already opened and errors – Russell Saari Sep 19 '11 at 16:56
then use `oXL.Windows` to find the already open window and call `Activate` on that that before before accessing `ActiveWorkbook`. or just call `oXL.ActiveWindow.Close()` or `oXL.ActiveWindow.ActivatePrevious()`... – Yahia Sep 19 '11 at 16:58
hmm no go again same NullReferenceException – Russell Saari Sep 19 '11 at 17:07
1

combine that with http://stackoverflow.com/questions/1118735/get-instance-of-excel-application-with-c-by-handle - if this does not work then is something really strange with you OS/Excel installation... are you by any chance trying to do this from a Windows Service OR a second "desktop session" or or ? – Yahia Sep 19 '11 at 17:23
Got it I figured it out I can't post the answer due to stack overflow – Russell Saari Sep 19 '11 at 17:29
glad that you solved it :-) please don't forget to upvote/mark as accepted any answer that was of help. – Yahia Sep 19 '11 at 17:30
Will do I will post when I can – Russell Saari Sep 19 '11 at 17:34
oXL = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); That is how i solved the whole fiasco – Russell Saari Sep 19 '11 at 17:55
that is exactly what is behind the link I put in my comment above... glad that this solved it :-) – Yahia Sep 19 '11 at 20:14
you are welcom - I added the solution as "EDIT 2" for reference. – Yahia Sep 19 '11 at 20:37
This will help tons of people I found so many forums on this. – Russell Saari Sep 19 '11 at 20:38

score 2 · Answer 2 · answered Sep 20 '11 at 11:57

Here is what I came up with I hope this will help everyone out. Thanks for everyone who helped

 using Excel = Microsoft.Office.Interop.Excel; //Excel Reference   

  public virtual Object ActiveSheet { get; set; }   

  private void button15_Click(object sender, EventArgs e)//Generate Model and Part Numbers   
  {   
        //Gets ActiveSheet to Modify
        Excel.Application oXL;
        Excel.Workbook oWB;
        Excel.Worksheet oSheet;

        //Start Excel and get Active Workbook and Sheet to modify
        oXL = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); 
        oXL.Visible = true;
        oWB = (Excel.Workbook)oXL.ActiveWorkbook; 
        oSheet = (Excel.Worksheet)oWB.ActiveSheet;   

        //Cell Input
        oSheet.Cells[6, 4] = "0"; //Change Value in Cell in Excel Cell Location [y-axis, x-axis] 
   }

score 0 · Answer 3 · edited May 23 '17 at 10:10

If you open like below then you will create just a new instance. oXL = new Excel.Application();

c# will create a second excel process. With this way you can not access to any Excel application, which one opened by the user manually.

If you want to open active worksheet which one is opened not by your application then please check here: Get instance of Excel application with C# by Handle

all the rest is same:

oSheet = (Excel._Worksheet)oWB.ActiveSheet;

if you want to have access to worksheet, which you open via c# instance then:(.net framework 4)

using Excel;

    Excel.Application oXl = new Excel.Application();
    Workbook oWb = oxl.workbooks.add();
    Worksheet oWs = oWs.Worksheets(1); //As default excel will open 3 worksheet and active worksheet will be first one.

Edit: I didnt check the code block can be typing error.

This also opens the a new application, but does not modify an existing opened file — Russell Saari, Sep 19 '11 at 16:57
Got it thanks for the help. I can't post the answer due to StackOverflow — Russell Saari, Sep 19 '11 at 17:29

score 0 · Answer 4 · answered Sep 19 '11 at 17:18

0

Might be simpler to abandon the Interop Classes and use OLEDB to run an update query on the sheet. I can provide examples if your interested.

answered Sep 19 '11 at 17:18

MAW74656

3,449
21
71
118

I got it thanks for the help can't post due to stack overflow – Russell Saari Sep 19 '11 at 17:29

Excel Worksheet Modifying Current Sheet C#

4 Answers4