If you are banging your head(which I quite frequently do) with GUI Automation of a Microsoft Office application, then this post is a must for you. QTP’s object Spy does not identify any of the Ribbon controls in any Office application. So, in order to click on any button in the ribbon, rather than relying on Sendkeys method, one can use the Command Bars Collection Object for accessing these buttons.
Here’s an example of how to use this in Microsoft Excel :-
Set objExcel=CreateObject("Excel.Application") Set objBook= objExcel.Workbooks.Add Set objSheet=objBook.Worksheets(1) 'Finding out the correct names of the Controls in the Command Bars collection For i = 1 to objExcel.CommandBars("Worksheet Menu Bar").Controls.count Print objExcel.CommandBars("Worksheet Menu Bar").Controls(i).Caption Next 'For example, if you want to simulate the clicking of Insert -> Chart button in the ribbon, from the above code you can find out on which index '"Insert" button will come (it comes on i=4), after that we can iterate to Insert control's sub controls in order to find out the correct 'index/name of the required control :- For i = 1 to objExcel.CommandBars("Worksheet Menu Bar").Controls(4).Controls.Count Print objExcel.CommandBars("Worksheet Menu Bar").Controls(4).Controls(i).Caption Next 'After finding out the correct index for the Insert Chart button, we can simulate clicking on it in the following manner :- objExcel.CommandBars("Worksheet Menu Bar").Controls(4).Controls(5).execute 'Please note that we can also use the Caption instead of the Index :- objExcel.CommandBars("Worksheet Menu Bar").Controls("&Insert").Controls("C&hart...").execute
The above code can be used for any Office application like PowerPoint,Word,Outlook etc. For more details on the Command Bars and Command Bar controls, you can visit the following links :-
PS: In UFT, all the ribbon controls are identified as WinButtons, so clicking on any Ribbon control is as easy as clicking on any button in a Web app.
Happy Automating !