How-To Program with Microsoft Office Excel and C# using a Ribbon and Interop

Checkout my other Excel Tutorials using Ribbons and C#

Be sure to checkout my other articles on Excel Ribbon development. The following 3 part tutorial shows you how to create a client/server CRUD application using ADO.Net, C# and Ribbon objects

  1. Create an Excel Windows Application - PART 1
  2. Create a table in SQL Server to store the data. - PART 1
  3. Create and configure an ADO.NET Data Source - PART 2
  4. Add code to load, update and save data back to the data store using a ribbon in C#. - PART 3

In the previous example (How-To program with Excel and C#), I demonstrated how to program in Excel 2007 using a Workbook Project in Visual Studio 2008. This example will use the Addin Project in Visual Studio 2008 to create a ribbon that can be inserted into any Excel file automatically.

Getting Started

If you are familiar with VS2008, start by creating a Microsoft Office 2007 Excel Addin Project. If you don't have the 2007 Office VSTO templates, you can download them from the Microsoft Download site. I won't include a link so not to end up with a potentially broken link in the future.

If you are new to VS2008, start by creating a project. Just do File->New-> Project. Expand the C# node in the Project Types(if you are using the C# settings) and expand the Office 2007 node of the VSTO and select the Excel 2007 Add-in template.

You can name your project anything you like. I named mine TestAddin. Also choose a location where to create the project or use the default location. Accept the other defaults.


New Excel 2007 Addin Project

Add Form for Dialogbox

In this step we are going to add a Windows Form to the project.

Right-Click the Project in the Solution Explorer window, click Add->Windows Form. You can name it anything you like. For the purposes of this example, I will name mine "HW".

Once the form is created in the editor, I will add a TextBox, Label and Button from the Toolbox. If you are new the Visual Studio, you can drag and drop them from the Toolbox palette.

Select the Textbox component and change the following properties in the Properties window:

  • Change the Name property to "txtName" and;
  • Change the Caption of the Label to something like "Enter Your Name".
  • For the Button, change its Caption to "Send to Excel".

In the next section I will add code to the button to take the value entered in the TextBox and append that value the "Hello World " String and insert the value into the "A1" cell on Sheet1 of an Excel file or the ActiveSheet

Dialogbox Windows Form
Dialogbox Windows Form

If I can get you to focus on the code in the Button1_Click method, the following code will create an Excel object "excelObj" and will activate the HW form by calling the "Activate" property:

Microsoft.Office.Interop.Excel.Application excelObj;
 

//Make sure it is active to load HW form

this.Activate ( );

will allow us to get a handle on the opened Excel file in our application. The next bit of code will assign the active object (Excel.Application) to the excelObj object.

excelObj = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
 

Once we have a handle on our Excel file, we can start accessing the workbook and worksheet. In order to access a worksheet, we will need to first access the workbook where the worksheet is located. You will be able to do that with the following bit of code:

//Get the Active workbook
 Microsoft.Office.Interop.Excel.Workbook wb;
 wb = excelObj.ActiveWorkbook;
 
 

In the next section of code, I have provided two options for accessing a Worksheet. You only need to use one of the two depending on your needs. In the first option, the code will allow you access the ActiveSheet, which is usually the first sheet in a workbook.

Microsoft.Office.Interop.Excel.Worksheet sheet =
(Microsoft.Office.Interop.Excel.Worksheet)this.ActiveSheet;
 
 

The second option allows you to get a specific worksheet through the available Worksheet collection "Microsoft.Office.Interop.Excel.Sheets". You only need to implement one of the two options.

Microsoft.Office.Interop.Excel.Sheets sheets = (Microsoft.Office.Interop.Excel.Sheets)wb.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item("Sheet1");

The rest of the code in the button will get a handle on a cell (or cells) with the get_Range method in Worksheet class. You will need to cast it to the Range class. The code below will demonstrate how this is done. In the following example, I am only accessing the "A1" cell and left the second Range parameter empty "System.Reflection.Missing.Value" but I could have specified a second value to select a range of cells.

Microsoft.Office.Interop.Excel.Range afield = (Microsoft.Office.Interop.Excel.Range)
sheet.get_Range("A1",System.Reflection.Missing.Value);
 

To finish up, you are going to add the following code to actually insert a value into the select cell (Range). In my example the value to be inserted is "Hello World " + the value from the "name" field.

 
afield.set_Value(System.Reflection.Missing.Value, "Hello World " + this.name.Text);

Finally call "this.hide" to close the form.

//Hide the dialogbox
this.Hide();
 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace TestAddin
{
    public partial class HW : Form
    {
        public HW()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //This is the Excel file that is already open so we don't need to re-open it

            Microsoft.Office.Interop.Excel.Application excelObj;

            //Make sure it is active
            this.Activate ( );

            
            excelObj = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

            //Get the Active workbook
            Microsoft.Office.Interop.Excel.Workbook wb;
            
            wb = excelObj.ActiveWorkbook;

            

            //To get the top sheet (e.g. Sheet1) or the Active Sheet use this syntax
            //Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)this.ActiveSheet;

            //Get a handle on all the worksheets in the Workbook
            Microsoft.Office.Interop.Excel.Sheets sheets = (Microsoft.Office.Interop.Excel.Sheets)wb.Worksheets;
            //Get a specific sheet in the Workbook
            Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item("Sheet1");
            //To get a cell or group of cells, you can use the following synatx
            Microsoft.Office.Interop.Excel.Range afield = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range("A1",
                                                            System.Reflection.Missing.Value);
            //Set the value of the A1 cell equal to "Hello World" plus the value in the name field in the dialogbox
            afield.set_Value(System.Reflection.Missing.Value, "Hello World " + this.name.Text);

            //Hide the dialogbox
            this.Hide();

        }
    }
}

Add Ribbon

For the next piece of the puzzle you will add a Ribbon Object; modify the default Group by changing its name and add a button. We finish this part by adding some code to open the HW form.

Right -Click on the Solution, in my example this would be TestAddin. In the context menu, select "Add-> New Item". In the "New Item" dialog box, select the "Ribbon (Visual Designer)" template. You can give any name you like. I named mine Hello.cs

When the Ribbon is created and the Visual Designer appears, select the Group1 control and change its name to "Hello there" or some other arbitrary name in the Properties View.

Next expand the "Office Ribbon Controls" in the Toolbox and drag a button onto the Group Control. Name the button "Click say Hello" or anything else that you like.

New Ribbon Item
New Ribbon Item
Ribbon Visual Designer
Ribbon Visual Designer

So far so good. Now Double Click on the Button Control and the Code Behind Editor will appear where you will add the code to open the dialogbox: "helloForm".

In the button1_Click method add the following code:

//Declare a dialogbox object

HW helloForm = new HW();

//Call the Show method to load the form

helloForm.Show();

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.Office.Tools.Ribbon;



namespace TestAddin

{

    public partial class Hello : OfficeRibbon

    {

        public Hello()

        {

            InitializeComponent();

        }



        private void Hello_Load(object sender, RibbonUIEventArgs e)

        {



        }



        private void button1_Click(object sender, RibbonControlEventArgs e)

        {

            //Declare a dialogbox object

            HW helloForm = new HW();

            //Call the Show method to load the form

            helloForm.Show();

        }

    }

}

Text Output

Ok, finally click F5 to launch the Ribbon app and Excel. Click on the "Addin" Menu and in the Addin Ribbon click on the "Say Hello" button to launch the "helloForm" form.

Enter your Name in the TextBox and Click on the "Send to Excel" button.

Addin Menu
Addin Menu
Click to say Hello Button
Click to say Hello Button
Dialogbox
Dialogbox

Sample Output

If everything according to plan, you should see something like this.

Output
Output

More by this Author


Comments 13 comments

shamshirha 5 years ago

This is a great tutorial, however, i have big issues that i can't get past. When i get to this point, it gives me an error

//To get the top sheet (e.g. Sheet1) or the Active Sheet use this syntax

//Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)this.ActiveSheet;

for some reason, it cannot find this.ActiveSheet. When I debugged the code during run time (after changing the this to wb), it shows a null return for activeworkbook and active worksheet. anything i'm missing?


shamshirha 5 years ago

figured it out, my excel wasn't activated yet and once i activated it, it worked.


klanguedoc profile image

klanguedoc 5 years ago from Canada Author

I am glad you got it figured out. I will review the code and add an entry about assuring to activate the object. Thanks


klanguedoc profile image

klanguedoc 5 years ago from Canada Author

If I understand your question correctly, when the dialog box (form) open read the value from the underlining Worksheet. You can use something like Globals.Worksheet.Range.Value and assign it to a field in the dialog box.


Das 4 years ago

Nice post, but im having the same problem shamshirha had. It shows a null return for activeworkbook and active worksheet.How to get through this?


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Set your application as active... Excel.Activate


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

This should work

Microsoft.Office.Interop.Excel.Application excelObj;

this.Activate ( );

or excelObj.Activate();


Bruce 4 years ago

Hi, May I know how to depoly the add in to end users and how to upgrade the add in if some logic is changed.


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Hi Bruce

Sorry for my late reply... You can deploy using a setup program and you can also add upgrades to the setup project. I don't an example right now but I can put one together some time next week.

Kevin


Dado 3 years ago

I'm currently into this stuff and these tutorials of yours helped me a lot. Thank you Kevin on it. :-) I was wondering is there some book about this Excel Add-In topic that would propose to me.


klanguedoc profile image

klanguedoc 3 years ago from Canada Author

Glad I could help. Actually there is a few. Here is a good one

http://www.amazon.com/Visual-Studio-Tools-Office-I...


karthi2901 2 years ago

I want the reverse process...while clicking the ribbon button i need to enter the values from excel fields into textbox in usercontrol


klanguedoc profile image

klanguedoc 2 years ago from Canada Author

Karth2901

Where is your usercontrol...in the ribbon?

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working