none
Need some direction on where to get help creating an Excel Add-In RRS feed

  • Question

  • I'm using VS2010 and creating an add-in that will have a task pane that needs to automate the workbook. I've already read quite a few "How to guides" from MS on this. I can create an add-in that will display a task pane. What I can't do is get this task pane to control the workbook. The example that I have found from MS is for PPT so it doesn't help me much. Can I get some links or help on how to start this? Thank you!
    • Moved by OmegaManModerator Wednesday, November 30, 2011 9:27 PM (From:Visual C# General)
    Wednesday, November 30, 2011 9:14 PM

Answers

  • Hi Webbee,

    The following code assume that there is button within the UserControl. Clicking the button should import some data to your currently worksheet:

            private void button1_Click(object sender, EventArgs e)
            {
                DataTable table = new DataTable();
                //assign some data for the datatable here
                
                //retrieve the currerently cell to import data
                Excel.Range cellImport = Globals.ThisAddIn.Application.ActiveCell;
                if (cellImport != null)
                {
                    ImportData(cellImport, table);
                }            
            }
    
            //the method to import data from datatable to cells of workbook
            public void ImportData(Excel.Range rng, DataTable dataTable)
            {    
                //iterate over the datatable
                for (int i =0; i <dataTable.Rows.Count; i++)
                {
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        rng.Offset[i,j].Value = dataTable.Rows[i][j].ToString();
                    }
                }       
            }
    

     

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by Webbee Thursday, December 1, 2011 3:52 PM
    Thursday, December 1, 2011 9:01 AM
    Moderator

All replies

  • The first link is to the Visual Studio Tools For Office forum where I will move this thread. For interop questions ask them in the appropriate Office Development Forums.

    HTH


    William Wegerson (www.OmegaCoder.Com)
    Wednesday, November 30, 2011 9:26 PM
    Moderator
  • Hi Webbee

    I'm not sure exactly what it is you're asking. Are you saying you don't know how to work with the Excel object model? Or that you simply don't know how to access it from the code in the class for the task pane (usually for a User Control)? Something else?

    Which version of Office is involved?

    The VSTO documentation (assuming this is a VSTO and not a Shared Add-in) on MSDN starts here and some of that might help you take a "next step":
    http://msdn.microsoft.com/en-us/library/d2tx7z6d.aspx


    Cindy Meister, VSTO/Word MVP
    Wednesday, November 30, 2011 11:11 PM
    Moderator
  • I thank you for your reply. I guess the answer is yes to both :) I'm pretty new to this so I'm not sure how to work with the Excel object model and also can't seem to grasp how to manipulate it from my user control. Currently I'm VS2010 and Office 2007. The main goal here is this, I'll be pulling data in from a web service and want to display the results on the active sheet. The data is really pretty simple, just standard rows without any special formatting required. Can you help direct me to an example for of something like this (not the web service part :-)  ).
    Wednesday, November 30, 2011 11:29 PM
  • Hi Webbee,

    The following code assume that there is button within the UserControl. Clicking the button should import some data to your currently worksheet:

            private void button1_Click(object sender, EventArgs e)
            {
                DataTable table = new DataTable();
                //assign some data for the datatable here
                
                //retrieve the currerently cell to import data
                Excel.Range cellImport = Globals.ThisAddIn.Application.ActiveCell;
                if (cellImport != null)
                {
                    ImportData(cellImport, table);
                }            
            }
    
            //the method to import data from datatable to cells of workbook
            public void ImportData(Excel.Range rng, DataTable dataTable)
            {    
                //iterate over the datatable
                for (int i =0; i <dataTable.Rows.Count; i++)
                {
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        rng.Offset[i,j].Value = dataTable.Rows[i][j].ToString();
                    }
                }       
            }
    

     

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by Webbee Thursday, December 1, 2011 3:52 PM
    Thursday, December 1, 2011 9:01 AM
    Moderator
  • Calvin this is very helpful! When I try this snippet out the Excel object does not seem to be readily available to me? I'm very forminulre with adding references and using statements so is there something I need to add to this UserControl?
    Thursday, December 1, 2011 1:03 PM
  • This took care of it :)

    using Excel = Microsoft.Office.Interop.Excel;

     

    This simple test worked fine and I'm off and running now!!

    string[] data = new string[] { "one", "two", "three" };


                //retrieve the currerently cell to import data
                Excel.Range cellImport = Globals.ThisAddIn.Application.ActiveCell;
                if (cellImport != null)
                {
                    ImportData(cellImport, data);
                } 

     

    //the method to import data from datatable to cells of workbook
            public void ImportData(Excel.Range rng, string[] data)
            {

                for (int i = 0; i < data.Length; i++)
                {
                    rng.Offset[Type.Missing, i].Value = data[i];
                }
            }

    Thursday, December 1, 2011 3:52 PM