How do I reference a Worksheet from a Form (Add-in for Excel 2003)

[royend]

Hi.
How can I reference the Worksheet (and Application) from a Form in an Add-in
for Excel 2003? I have tried this code:

Excel.Worksheet sheet = (Excel.Worksheet)this.Sheets["Sheet2"];
sheet.Select(Type.Missing);
Excel.Range newFirstRow = sheet.get_Range("A1", Type.Missing);
newFirstRow.Value2 = "This text was added by using code";

But I only get an error telling me that 'Form1' does not contain a
definition for 'Worksheets'.

I also tried this:

Excel.Worksheet sheet =
((Excel.Worksheet)Application.ActiveSheet);

Which led to the following error:
'System.Windows.Forms.Application' does not contain a definition for
'ActiveSheet'

My goal is to do some calculations with my Add-in, present a summary of the
calculations in the Add-in while saving all calculations in the Excel calling
the Add-in.
I am using Visual Studio 2008 for programming an Add-in for Excel 2003.

 

[Jacob Skaria]

Hi

You need to add a reference to Microsoft Excel 11.0 Object Library.
Checkout this for C# code (http://www.codeproject.com/KB/office/package.aspx)

If this post helps click Yes

 

[royend]

Hi.
Thanks for your reply.
In the code of your link the programmer always needs to open an Excel-file.
Do you know if it is possible to reference the already opened
Excel-application?

A normal scenario would be that the user has opened his/hers Excel Workbook
and then selected my Add-in to do some analysis. It should not be necessary
to reopen the Excel-file, but automatically use the open workbook. But how
can I do this?

Best regards,
royend

 

[royend]

Hi again.
I am still struggling with this, and I have decided to take another approach.

Since I am familiar with .NET-coding for web, I tried defining my Form as a
user control with a property for a Worksheet:
public partial class Form1 : Form
{
private Excel.Worksheet _excelWorksheet = null;
public Excel.Worksheet excelWorksheet { get { return
_excelWorksheet; } set { _excelWorksheet = value; } }
....

I also added a new constructor for my Form1 class which recievew my
Worksheet as an input:

public Form1(Excel.Worksheet ws)
{
InitializeComponent();
excelWorksheet = ws;
}

This excelWorksheet should then be available for my Form1 class at a later
time.

From my Add-in class I then instantiates the Form1 class with a worksheet
like this:
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
Excel.Worksheet activeWorksheet =
((Excel.Worksheet)Application.ActiveSheet);
Form1 form = new Form1(activeWorksheet);
form.Show();
}

However when I debug I see that the activeWorksheet is null.
What am I doing wrong?

Looking forward to any advice, hint and help.