This is the first article in the Excel Automation category and there are many more to come. Excel provides Methods for Add-ins to add new Menu options. Automation for testing or using these options requires us to execute the Menu Option through code. In this article we will explore how the same is done
We will use the following object for this
- Application - The top level application object
- CommandBars - The collection of CommandBar objects. Derived from the Application object
- CommandBar - Individual CommandBar object
- Controls - Child Controls of the CommandBar object
Now we follow various steps to execute a specified Menu item
Open a New Excel Application
Set xlsApp = CreateObject("Excel.Application") |
Open a new or an Existing file
'To open a new one Set xlsWorkBooks = xlsApp.WorkBooks.Add 'To open an existing one Set xlsWorkBooks = xlsApp.WorkBooks.Open ("C:/MyTest.xls") |
Note: Above code would not be required if the code is being run inside a excel macro only
Get the CommandBar object
This can be a bit tricky if you are not aware of which CommandBar object you are looking for. Below code can be used to see all of them
For i = 1 to xlsApp.CommandBars.Count Debug.Print xlsApp.CommandBars(i).Name Next |
To get a individual CommandBar object we can also use its name as shown in code below
xlsApp.CommandBars("Worksheet Menu Bar").Name |
Getting the Sub Menus and Executing them
Once we have the Specific CommandBar object we were looking for we can use the Controls object to find the child menus/options
xlsApp.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Spelling...").Execute |