Acces Excel Sheet from Button a Ribbon

Jack W picture Jack W · Sep 19, 2012 · Viewed 8.3k times · Source

Ok, so I got my main AddIn which auto generated (Attachment 1) and there is my Ribbon (Attachment 2) and I want to access the currently active excel sheet from that ribbon. But System.Windows.Forms.Application does not contain a definition for ActiveSheet.

Attachment 1:

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using System.Windows.Forms;

namespace ExcelAddIn1
{
public partial class ThisAddIn
{
    private void ThisAddIn_Startup(object sender, System.EventArgs e)
    {

    }

    private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
    {
    }

    #region VSTO generated code

    /// <summary>
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// </summary>
    private void InternalStartup()
    {
        this.Startup += new System.EventHandler(ThisAddIn_Startup);
        this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
    }
    public void doStuff()
    {
       
    }
    
    #endregion



}
}

Attachement 2:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Tools.Ribbon;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using System.Windows.Forms;

namespace ExcelAddIn1
{
public partial class Ribbon1
{
    private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
    {

    }

    private void button1_Click(object sender, RibbonControlEventArgs e)
    {
        MessageBox.Show("Test");
        Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet);
        Excel.Range firstRow = activeWorksheet.get_Range("A1");
        firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
        Excel.Range newFirstRow = activeWorksheet.get_Range("A1");
        newFirstRow.Value2 = "This text was added by using code";
    }
}
}

Answer

SliverNinja - MSFT picture SliverNinja - MSFT · Sep 19, 2012

You need to obtain the IRibbonControl.Context from the RibbonControlEventArgs parameter. This context represents the Excel.Window. Then you can access the active Window.Application property.

private void button1_Click(object sender, RibbonControlEventArgs e)
{
    Excel.Window window = e.Control.Context;
    MessageBox.Show("Test");
    Excel.Worksheet activeWorksheet = ((Excel.Worksheet)window.Application.ActiveSheet);
    Excel.Range firstRow = activeWorksheet.get_Range("A1");
    firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
    Excel.Range newFirstRow = activeWorksheet.get_Range("A1");
    newFirstRow.Value2 = "This text was added by using code";
}