none
Press buttons automatically using an Add-in RRS feed

  • Question

  • Hey,

    Is there a way to make an Excel Add-in (in C#) that searches for all the buttons in the excel document (if there are any) and clicks them one by one?

    I want to make this add-in work at start-up when opening any excel file.

    Thanks.

    Wednesday, July 19, 2017 1:47 PM

Answers

  • Hi DoctorDD,

    When a form control is created, it will be "assigned" an macro, however ,in fact, there is no such macro be created. So when we try to call the macro, it will get error and not continue to click next button. I think you need do error handle when trying to call these click event/assigned macro. It look like.

    foreach (Excel.Worksheet ws in Wb.Worksheets) { foreach (Excel.Shape sh in ws.Shapes) { if (sh.Type == Microsoft.Office.Core.MsoShapeType.msoOLEControlObject) { if (sh.OLEFormat.progID == "Forms.CommandButton.1") { try { Globals.ThisAddIn.Application.Run(ws.CodeName + "." + sh.Name + "_Click"); } catch { MessageBox.Show("It failed to call" + ws.CodeName + "." + sh.Name + "_Click"); } } } if (sh.Type == Microsoft.Office.Core.MsoShapeType.msoFormControl) { if (sh.OnAction != "") { try { Globals.ThisAddIn.Application.Run(sh.OnAction); } catch { MessageBox.Show("It failed to call " + sh.OnAction); } } } } }

    The result is

    Besides, I suggest you not call this method in Workbook open event. If you get error, it may cause failure to open the workbook. You could use a button to call it.

    Best Regards,

    Terry

    Thursday, July 27, 2017 11:19 AM

All replies

  • Hello,

    If it is a form control, we could check if there is onAction callback and then use Globals.ThisAddIn.Application.Run(sh.OnAction); to call the event.

    If it is an ActiveX control, the OnAction string returns empty, we could we simply use Globals.ThisAddIn.Application.Run to call its click event.

      

    using Excel = Microsoft.Office.Interop.Excel;

    foreach (Excel.Shape sh in Globals.ThisAddIn.Application.ActiveSheet.Shapes) { if (sh.Type== Microsoft.Office.Core.MsoShapeType.msoOLEControlObject) { if (sh.OLEFormat.progID== "Forms.CommandButton.1") { Globals.ThisAddIn.Application.Run("Sheet1."+sh.Name+"_Click"); } } if (sh.Type == Microsoft.Office.Core.MsoShapeType.msoFormControl) { if (sh.OnAction!="") { Globals.ThisAddIn.Application.Run(sh.OnAction); } } }

    To make this add-in work at start_up when opening any excel file, please registry the WorkbookOpen event at ThisAddIn_Startup.

      private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                this.Application.WorkbookOpen += Application_WorkbookOpen;  
            }
            private void Application_WorkbookOpen(Excel.Workbook Wb)
            {
    
            }

    Regards,

    Celeste


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 20, 2017 8:54 AM
    Moderator
  • Thanks for the answer. just a couple of problems:

    1. If there are many sheets then it won't work for everything. Is there a way to click all the buttons on all of the sheets or even better, press all the buttons on the active sheet, and if a different sheet will be active then the script will run again.

    2. It still doesn't work for the form buttons (although the button has a macro assigned to it).


    • Edited by DoctorDD Thursday, July 20, 2017 1:53 PM
    Thursday, July 20, 2017 1:52 PM
  • Hi DoctorDD,

    ->If there are many sheets then it won't work for everything.

    You could iterate through worksheets, so it could click all buttons on all sheets, even if a different sheet will be active, but don't add or delete any sheet.

    Just like

    private void Application_WorkbookOpen(Excel.Workbook Wb)

            {

                foreach (Excel.Worksheet ws in Wb.Worksheets)

                {

                    foreach (Excel.Shape sh in ws.Shapes)

                    {

                        if (sh.Type == Microsoft.Office.Core.MsoShapeType.msoOLEControlObject)

                        {

                            if (sh.OLEFormat.progID == "Forms.CommandButton.1")

                            {

                                Globals.ThisAddIn.Application.Run("Sheet1." + sh.Name + "_Click");

                            }

                        }

                        if (sh.Type == Microsoft.Office.Core.MsoShapeType.msoFormControl)

                        {

                            if (sh.OnAction != "")

                            {

                                Globals.ThisAddIn.Application.Run(sh.OnAction);

                            }

                        }

                    }

                }

            }

    ->It still doesn't work for the form buttons

    In my test, macro assigned to a form button could be fired. I suggest you double check if there is any macro assigned to the form button. You could assign a macro with obvious signs, such messagebox a word, to check if the macro is fired. If it still doesn't work you, I suggest you share us your office version and vs version so we could try to reproduce your issue.

    Best Regards,

    Terry

    Monday, July 24, 2017 8:20 AM
  • 1. Hey thanks for the help, but shouldn't it be

    Globals.ThisAddIn.Application.Run(ws.Name + sh.Name +"_Click");

    instead of Globals.ThisAddIn.Application.Run("Sheet1." + sh.Name +"_Click");

    2. Here is the excel file:

    https://uploadfiles.io/7rboc

    Monday, July 24, 2017 8:45 AM
  • Hi DoctorDD,

    Thanks for your kindly remind. I suggest you use

    Globals.ThisAddIn.Application.Run(ws.CodeName+"." + sh.Name + "_Click");

    to call the click event.

    I download your file and I find the drop down control was assigned a DropDpwn2_Change method/event, however I didnt find such a method/event in VBE, I suggest you remove this assignment or create such method/event so we could call its  assignment macro correctly. You could do error handling when you try to call a macro/click event which does not exists.

    Best Regards,

    Terry

    Tuesday, July 25, 2017 11:56 PM
  • Sorry for the the confusion, but the problem is not with the drop down control (Because nothing was assigned to it really).

    The problem is with the button, which has a macro assigned to it named "Button_click" (and if you click on it, you'll get a message box saying  "you clicked it"). Now, although it has a macro assigned to it, it has no "OnAction"method, so the Add-In doesn't click on it :(

    Wednesday, July 26, 2017 12:34 PM
  • Hi DoctorDD,

    When a form control is created, it will be "assigned" an macro, however ,in fact, there is no such macro be created. So when we try to call the macro, it will get error and not continue to click next button. I think you need do error handle when trying to call these click event/assigned macro. It look like.

    foreach (Excel.Worksheet ws in Wb.Worksheets) { foreach (Excel.Shape sh in ws.Shapes) { if (sh.Type == Microsoft.Office.Core.MsoShapeType.msoOLEControlObject) { if (sh.OLEFormat.progID == "Forms.CommandButton.1") { try { Globals.ThisAddIn.Application.Run(ws.CodeName + "." + sh.Name + "_Click"); } catch { MessageBox.Show("It failed to call" + ws.CodeName + "." + sh.Name + "_Click"); } } } if (sh.Type == Microsoft.Office.Core.MsoShapeType.msoFormControl) { if (sh.OnAction != "") { try { Globals.ThisAddIn.Application.Run(sh.OnAction); } catch { MessageBox.Show("It failed to call " + sh.OnAction); } } } } }

    The result is

    Besides, I suggest you not call this method in Workbook open event. If you get error, it may cause failure to open the workbook. You could use a button to call it.

    Best Regards,

    Terry

    Thursday, July 27, 2017 11:19 AM