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


Linux_Package_Management Linux_Package_Management Before Package Management API ABI 可执行程序的格式 虚拟环境 项目...
  • u010796631
  • u010796631
  • 2015年12月27日 19:59
  • 1197

编译安装PHP 时遇到问题解决方法.

编译安装PHP 时遇到问题解决方法. 环境:centos X64 最小化安装  php版本:php-5.4.3 安装前.先安装些软件和库文件 yum install -y gcc gc...
  • white__cat
  • white__cat
  • 2014年06月05日 10:18
  • 2425

Linux安装apache时 apr报错解决 rm: cannot remove `libtoolT': No such file or directory

在configure里面 RM='$RM  -f'   这里的$RM后面一定有一个空格。 如果后面没有空格,直接连接减号,就依 然会报错。把RM='$RM'改为RM='$RM  -f'   ...
  • a673341766
  • a673341766
  • 2013年06月12日 02:18
  • 14677

cannot remove `libtoolT': No such file or directory

11down votefavorite 2 I tried to compile apache APR. The instruction sayed: A...
  • lanwilliam
  • lanwilliam
  • 2017年07月28日 15:32
  • 379


架设Redis: 普通用户xiaobai,copy Redis.tar文件,解压到/usr/local失败: Linux怎么在文件系统/usr/local中复制进文件,总是显示没有权限?...
  • ghbfgb
  • ghbfgb
  • 2017年09月10日 09:46
  • 407


  • vickytong1018
  • vickytong1018
  • 2016年07月06日 11:37
  • 546

(OK) cBPM-CentOS—wrapped by fastcgi—files—executing commands

----------------------------下面针对cBPM [root@localhost html]# gedit /root/.bashrc [root@localhost ...
  • ztguang
  • ztguang
  • 2016年02月01日 00:00
  • 182

Problem executing commands on Windows over SSH

This seems like a bug because SSH seem to work otherwise: 1. Transferring files with Jenkins over S...
  • jjkliu
  • jjkliu
  • 2015年11月27日 15:22
  • 261


我们这次讲的是利用commands模块执行Linux shell命令,当我们用Python写运维脚本时,经常需要执行linux shell的命令,Python中的commands模块专门用于调用Lin...
  • wenph2008
  • wenph2008
  • 2014年11月29日 00:09
  • 2376

Eclipse Commands 指南(二)

4.  命令和菜单上下文    现在让我们将ContextMenu添加到表。创建一个新的项目“de.vogella.rcp.intro.commands.popup”基于“RCP applicatio...
  • redshlink
  • redshlink
  • 2010年09月13日 16:46
  • 4627
您举报文章:Executing the Menu Commands in Excel