none
How can i add button with macro to Specific cell in a excel file ? RRS feed

  • Question

  • I am a c# developer and need to automate do the generation of excel files with macro buttons.

    Thanks!

    Thursday, December 27, 2018 2:27 PM

All replies

  • Try this:

    https://social.msdn.microsoft.com/Forums/office/en-US/be3ed2fd-f111-4a10-a306-0920b93cabe5/how-to-add-a-button-control-to-an-excel-worksheet-using-c?forum=exceldev

    Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
           
            object misValue = System.Reflection.Missing.Value;
    
            private MSForms.CommandButton btnExcel;
    
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
                //add data 
                xlWorkSheet.Cells[1, 1] = "";
                xlWorkSheet.Cells[1, 2] = "Student1";
                xlWorkSheet.Cells[1, 3] = "Student2";
                xlWorkSheet.Cells[1, 4] = "Student3";
    
                xlWorkSheet.Cells[2, 1] = "Term1";
                xlWorkSheet.Cells[2, 2] = "80";
                xlWorkSheet.Cells[2, 3] = "65";
                xlWorkSheet.Cells[2, 4] = "45";
    
                xlWorkSheet.Cells[3, 1] = "Term2";
                xlWorkSheet.Cells[3, 2] = "78";
                xlWorkSheet.Cells[3, 3] = "72";
                xlWorkSheet.Cells[3, 4] = "60";
                //
                // some other data insertion to cells
                //
                xlWorkSheet.Cells[10, 1] = "Term9";
                xlWorkSheet.Cells[10, 2] = "24";
                xlWorkSheet.Cells[10, 3] = "78";
                xlWorkSheet.Cells[10, 4] = "49";
                xlApp.Visible = true;
             
                #region
                xlWorkSheet.Shapes.AddOLEObject("Forms.CommandButton.1", Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, 60, 60, 60, 60);
    
                Excel.OLEObject oleObject = xlWorkSheet.OLEObjects(1);
                Microsoft.Vbe.Interop.Forms.CommandButton cmdButton = oleObject.Object;
    
                cmdButton.Click += new MSForms.CommandButtonEvents_ClickEventHandler(cmdButton_Click);
    
    
                #endregion
    
    
                //xlWorkBook.SaveAs(@"E:\Test.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                //xlWorkBook.Close(true, misValue, misValue);
                //xlApp.Quit();
    
                MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
    
    
            }
    
            void cmdButton_Click()
            {
                MessageBox.Show("This is a test!");  
            }


    Guy Zommer

    Thursday, December 27, 2018 2:34 PM
  • Hi zynlnow,

    You can refer to the following link to create a button in an Excel worksheet.

    c# excel create a button on excel worksheet

    And then write the logical code that automatically generates an Excel file in the event handler function

    (btndosomething), and the code that generates the Excel file can refer to the answer given by Guy.

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Friday, December 28, 2018 2:57 AM
    Moderator
  • Hi zynlnow,

    Have you solved your problem yet? if your issue is still exist then let us know about that, we will try to suggest you further to solve the issue.

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, January 3, 2019 8:47 AM
    Moderator