Executing the Menu Commands in Excel

转载 2011年01月21日 16:17:00

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

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


executing AT-commands under Windows

原文地址::http://3g-modem.wetpaint.com/page/executing+AT-commands+under+Windows     how to execute AT...

Save results to different files when executing multi SQL statements in DB Query Analyzer 7.01

You can save each result to different file when executing multi SQL statements in DB Query Analyzer...

webxml attribute is required (or pre-existing WEB-INF/web.xml if executing in update mode)

1、错误描述[INFO] Scanning for projects... [INFO] ...

The new powerful SQL executing schedule monthly or weekly in DB Query Analyzer 7.01

DB Query Analyzer 7.01 allows usersto define monthly or weekly SQL executing plans. Here I will show...

145.Examine the commands executed in a DBA session: SQL> CREATE BIGFILE TABLESPACE MRKT 2 DATAFILE '

145.Examine the commands executed in a DBA session: SQL> CREATE BIGFILE TABLESPACE MRKT 2 DATAFILE '...

Some useful commands in cmd

Some useful commands in cmd name explanation CD Displays the name of or changes the current d...

MVVM之旅-给任意的事件绑定命令(Adventures in MVVM – Binding Commands to ANY Event)

原文标题:Adventures in MVVM – Binding Commands to ANY Event 当我实现MVVM模式时,令我最为头疼一件事是需要给事件绑定命令。当我使用Prism...

Running system commands in Java applications

http://www.devdaily.com/java/edu/pj/pj010016 tags:  bufferedreader command exec execute java java ja...

【荐】20 MySQL (Mysqladmin) Commands for Database Administration in Linux

mysqladmin is a command-line utility the comes with MySQL server and it is used byDatabase Administ...