none
How to develope MS Excel Utility/Plug-In in .Net RRS feed

  • Question

  • How to develope MS Excel Utility/Plug-In  for (including features various tabs -- Insert --> shapes , buttons,tables etc,  PageLayout , Formulas etc) and including buttons drag drop and events generating for buttons.On this Button we can write event just like we are doing in VB.net.

    It's very urgent. Every help is appreciated.

    If you can mail me , please mail at archanazohns@gmail.com

     

     

     

     

    • Moved by Paul Zhou Monday, December 5, 2011 5:17 AM move for better support (From:.NET Platform Architecture Development Discussions)
    Saturday, December 3, 2011 4:01 AM

Answers

  • Hi,

    Sorry for the late response. There are some mistake with the forum alert email. I haven't got your reply until I check this thread now.

    So according to your description, I think you want to add button for your workbook runtime, it's possible, and here are the steps:

    1. Create an Excel add-in and add a Ribbon Designer item named RibbonControls

    2. Add a button for the ribbon and design the click event for it like:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using Excel = Microsoft.Office.Interop.Excel;
    using vstoExcel = Microsoft.Office.Tools.Excel;
    using System.Windows.Forms;
    
    
    namespace AddFormControls
    {
        public partial class RibbonControls
        {
            static int buttonIndex = 0;
            private void RibbonControls_Load(object sender, RibbonUIEventArgs e)
            {
    
            }
    
            private void button1_Click(object sender, RibbonControlEventArgs e)
            {
                Excel.Worksheet acSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
                if (acSheet == null)
                {
                    return;
                }
    
                Excel.Range rng = Globals.ThisAddIn.Application.Selection as Excel.Range;
    
                buttonIndex++;
                vstoExcel.Worksheet vstoSheet = Globals.Factory.GetVstoObject(acSheet);
    
                Button btn = new Button();
                btn.Text = "Button" + buttonIndex.ToString();
                btn.Click += new EventHandler(btn_Click);
                vstoSheet.Controls.AddControl(btn, rng, "Button" + buttonIndex.ToString());
            }
    
            void btn_Click(object sender, EventArgs e)
            {
                Button thisButton = sender as Button ;
    
                MessageBox.Show(thisButton.Text);
            }
        }
    }
    
    


    The add-in works, but here is the problem, after you save as the workbook with WinForm controls added, when you open the workbook again, you will find all the controls are unclickable. They can be moved by using Design Mode of Excel, but they can't be clicked.

    Adding WinForm controls on the surface of Excel file only works for VSTO document-level solution as far as I know. You may need to use OELObject instead, which is always clickable.

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 9, 2011 8:07 AM
    Moderator
  • Hi,

    Here is the code how to add OLEObject controls for your Excel file:

    Add another button for the ribbon, and register the button click event like:

            private void button2_Click(object sender, RibbonControlEventArgs e)
            {
                Excel.Worksheet acSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
                if (acSheet == null)
                {
                    return;
                }
    
                Excel.Range rng = Globals.ThisAddIn.Application.Selection as Excel.Range;
    
                Excel.OLEObjects ole = acSheet.OLEObjects();
                ole.Add(ClassType: "Forms.CommandButton.1", 
                    Link: false, DisplayAsIcon: false, 
                    Left: rng.Left, Top: rng.Top, Width: rng.Width, Height: rng.Height);         
            }
    

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 9, 2011 8:18 AM
    Moderator

All replies

  • Archana,

    Below Link may help you more on developing Add-in for Ms-Office using VS.

    http://msdn.microsoft.com/en-us/library/bb398248(v=vs.90).aspx

    Please post/move your question to   Visual Studio Tools for Office Forum for prompt response.


    Lingaraj Mishra
    Sunday, December 4, 2011 2:36 AM
  • Hi,

     

    Welcome to the MSDN forum.

     

    What kind of technology you want to use? If you are working with VSTO, as Lingaraj suggested, Visual Studio Tools for Office will be the correct forum.

     

    Moreover, it sounds like a problem about Ribbon Designer. You can read the MS document below, it is helpful for Ribbon Designer development and tells us how to customize the Microsoft Office button menu.

    Ribbon Designer

    Ribbon XML

     

    Have a nice day.


    Paul Zhou [MSFT]
    MSDN Community Support | Feedback to us
    Monday, December 5, 2011 3:23 AM
  • hye,,

    How can i create draggable  and dropable button in Ribbon designer..

    I want the same functionality like excel draaging and dropping button , setting it is properties etc.

     

    ANy help will be welcome

    Monday, December 5, 2011 4:52 AM
  • I'm moving your thread to Visual Studio Tools for Office forum to get better support.

    There are experts there so that you can get useful response.

    Have a nice day.


    Paul Zhou [MSFT]
    MSDN Community Support | Feedback to us
    Monday, December 5, 2011 5:16 AM
  • hey ,

    I am not that much sound in All .net technology...So could u please explain what is the dif

    ference between both of the above replies.

    Monday, December 5, 2011 5:18 AM
  • How can i create draggable  and dropable button in Ribbon designer..

    I want the same functionality like excel draaging and dropping button , setting it is properties etc.

     

    Hi Archana,

    What do you mean of draggbale and dropable button? What the button suppose to do? With Ribbon Designer or Ribbon XML, you can customize your ribbon, such as adding custom tabs, groups, controls, deleting or hiding built-in tabs or groups, repurpose built-in controls and so on. But most controls only has click event, which is totally unlike controls of WinForm.

    In addition, by the way, VSTO provides an approach enable us to use WinForm controls in Office document surface but not Ribbon, if you are interest in this, we can have a more detailed discussion later.

    I look forward to hearing of you.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Monday, December 5, 2011 5:30 AM
    Moderator
  • simple  i mean as u click on any shape in the excel , it is dragged on to the excel surface same i want in my Utility and other functions also..
    Monday, December 5, 2011 6:02 AM
  • Hey can i  add buit In font , and color boxes in my new own created Ribbon..

    If then how please suggest ...

    Any help will be welcomed.

    Tuesday, December 6, 2011 7:00 AM
  • please help me..please

    it is urgent

    Tuesday, December 6, 2011 8:27 AM
  • Hmm...I'm sorry I'm still unable follow your intention. Do you want to drag control from ribbon and drop onto surface of workbook? Well, this is not possible. 

    Moreover, what do you mean of adding built-in font? We can do some customization for Office ribbon by using Ribbon Designer or Ribbon XML, but there are some limitations, one of which is that you can edit controls in built-in groups. If you want to add some controls into a built-in a group, I have to tell you that's not possible. 

    I look forward to hearing of you.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 6, 2011 9:31 AM
    Moderator
  • ok..please litsen carefully..

    I have a my own ribbon and one button there.

    Now i want to click on this button and every time when i click on the button i want to create new button.Suppose i click 2 times on that button that will create 2 button on  Active sheet.

    That buttons will be moveable.

    Now please help me how can i do this for Ribbon?

    Tuesday, December 6, 2011 10:09 AM
  • Hey Calvin,

    i am interested  .. please help me...

    I have a my own ribbon and one button there.

    Now i want to click on this button and every time when i click on the button i want to create new button.Suppose i click 2 times on that button that will create 2 button on  Active sheet.

    That buttons will be moveable.

    Now please help me how can i do this for Ribbon?

     

    Tuesday, December 6, 2011 11:53 AM
  • hey,

    I am facing many problem contnoulsy...I want to create a button on ActiveSheet ..after clicking on the button at Ribbongroup.

    So i was trying to  do the code in my Ribbon.cs class

    please see here

    using

     

    System;

    using

     

    System.Collections.Generic;

    using

     

    System.Linq;

    using

     

    System.Text;

    using

     

    Microsoft.Office.Tools.Ribbon;

    using

     

    Microsoft.Office.Tools.Excel;

    using

     

    Excel = Microsoft.Office.Interop.Excel;

    using

     

    Microsoft.Office.Tools.Excel.Controls;

    using

     

    Microsoft.Office.Core;

    using

     

    Microsoft.Office.Tools;

    namespace

     

    ExcelProtoTypeinal

    {

     

    public partial class Ribbon1 :

    RibbonBase

    {

    Microsoft.Office.Interop.Excel.

    Worksheet

    objWork;

     

    private void Ribbon1_Load(object sender, RibbonUIEventArgs

    e)

    {

    }

     

    private void btnSample_Click(object sender, RibbonControlEventArgs

    e)

    {

    objWork =

    Globals

    .ThisAddIn.Application.ActiveSheet;

    Microsoft.Office.Tools.Excel.Controls.

    Button objButton = new Button

    ();

    objWork.Controls.AddButton(objWork.Range[

    "A1"

    ], objButton);

     

    // objWork.Activate();

     

    //objWork.Shapes.AddOLEObject ClassType:="Forms.CommandButton.1";

     

    //objWork.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _

     

    // Link:=False, DisplayAsIcon:=False, Left:=30, Top:=20, Width:=72, Height:=24);

     

     

     

    // objWork.Controls.AddButton(objWork.Range["A1"], objButton);

    }

    }

    }

    but there is an error in this line :

    objWork.Controls.AddButton(objWork.Range["A1"], objButton);

    please suggest me how can i add this?

     

    Tuesday, December 6, 2011 1:04 PM
  • Hi,

    Sorry for the late response. There are some mistake with the forum alert email. I haven't got your reply until I check this thread now.

    So according to your description, I think you want to add button for your workbook runtime, it's possible, and here are the steps:

    1. Create an Excel add-in and add a Ribbon Designer item named RibbonControls

    2. Add a button for the ribbon and design the click event for it like:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using Excel = Microsoft.Office.Interop.Excel;
    using vstoExcel = Microsoft.Office.Tools.Excel;
    using System.Windows.Forms;
    
    
    namespace AddFormControls
    {
        public partial class RibbonControls
        {
            static int buttonIndex = 0;
            private void RibbonControls_Load(object sender, RibbonUIEventArgs e)
            {
    
            }
    
            private void button1_Click(object sender, RibbonControlEventArgs e)
            {
                Excel.Worksheet acSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
                if (acSheet == null)
                {
                    return;
                }
    
                Excel.Range rng = Globals.ThisAddIn.Application.Selection as Excel.Range;
    
                buttonIndex++;
                vstoExcel.Worksheet vstoSheet = Globals.Factory.GetVstoObject(acSheet);
    
                Button btn = new Button();
                btn.Text = "Button" + buttonIndex.ToString();
                btn.Click += new EventHandler(btn_Click);
                vstoSheet.Controls.AddControl(btn, rng, "Button" + buttonIndex.ToString());
            }
    
            void btn_Click(object sender, EventArgs e)
            {
                Button thisButton = sender as Button ;
    
                MessageBox.Show(thisButton.Text);
            }
        }
    }
    
    


    The add-in works, but here is the problem, after you save as the workbook with WinForm controls added, when you open the workbook again, you will find all the controls are unclickable. They can be moved by using Design Mode of Excel, but they can't be clicked.

    Adding WinForm controls on the surface of Excel file only works for VSTO document-level solution as far as I know. You may need to use OELObject instead, which is always clickable.

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 9, 2011 8:07 AM
    Moderator
  • Hi,

    Here is the code how to add OLEObject controls for your Excel file:

    Add another button for the ribbon, and register the button click event like:

            private void button2_Click(object sender, RibbonControlEventArgs e)
            {
                Excel.Worksheet acSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
                if (acSheet == null)
                {
                    return;
                }
    
                Excel.Range rng = Globals.ThisAddIn.Application.Selection as Excel.Range;
    
                Excel.OLEObjects ole = acSheet.OLEObjects();
                ole.Add(ClassType: "Forms.CommandButton.1", 
                    Link: false, DisplayAsIcon: false, 
                    Left: rng.Left, Top: rng.Top, Width: rng.Width, Height: rng.Height);         
            }
    

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 9, 2011 8:18 AM
    Moderator