Automating Microsoft Excel Part 4 – How to Automate MS Office Ribbon

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 :-

http://msdn.microsoft.com/en-us/library/office/aa190804(v=office.10).aspx

http://msdn.microsoft.com/en-us/library/office/aa831765(v=office.10).aspx

 

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 !

Harshit Kohli

Advertisements

Eat, Sleep, Automate. REPEAT !

Tagged with: , , , , , , , , ,
Posted in Awesum Stuff
27 comments on “Automating Microsoft Excel Part 4 – How to Automate MS Office Ribbon
    • tejus parekh says:

      Your excel code is very helpful to me. I am facing 1 problem. i have a checkbox in excel created with Form Controls not with Activex control. i need to select the checkbox. i have qtp 11.5 in my office.

  1. tejus parekh says:

    i heard we can select using API in UFT

    • Hi Tejus,

      Sorry for the late reply. You can check the checkbox with the following code :-

      oExcel.Activesheet.Checkboxes(1).value=1

      uncheck can be done with following code :-

      oExcel.Activesheet.Checkboxes(1).value=-4146

      Thanks,
      Harshit

  2. dhinesh says:

    How to click excel sheet buttons like File, insert, edit view …etc using QTPSCRIPT,
    and one more thing Objsheet.cells(2,3) cell having one link how to click…pls help me on this?

    • Hi dhinesh,

      For your first problem, you can use UFT with the following line :-

      Window(“Book1”).WinObject(“Ribbon”).WinTab(“Ribbon Tabs”).Select “View”

      For your second problem, you can use the following line of code (works with UFT and QTP both) (assuming the link is in the cell A1)

      oExcel.ActiveSheet.Range(“A1”).Hyperlinks(1).Follow

      Thanks,
      Harshit

  3. dhinesh says:

    Many Many thanks, Harshit..1st problem is solved successfully, but little bit confusion about
    2nd problem, actually in my application scenario is like below..(when i select Excel2010 combobox in a webpage and Click “generate” Button,automatically Excel sheet generated from webpage) in that
    objExcel.cell(2,3)=”link1″
    objExcel.cell(3,3)=”link2″
    objExcel.cell(4,3)=”link3″….so many are there .. etc… so here the doubt is, can i able to select one particular cell and click on that particular link only?(means i need to click on “link2” now)………(we r using UFT only)

    one more,,we r using excel 2010 na, after generating excel sheet, first How to click “Enable Editing” button also?

    waiting for your favorable reply
    many many thanks in advance harshith

    • hey dhinesh,

      in the code that I sent u (oExcel.ActiveSheet.Range(“A1″).Hyperlinks(1).Follow), it does not click on the link, it just redirects to the link present in cell 1,1. So now its up to you how you want to use it(you can use some other value rather than A1)

      Following should get rid of enable editing :-

      If oExcel.Application.ProtectedViewWindows.Count > 0 Then
      oExcel.Application.ActiveProtectedViewWindow.Edit
      End If

      Thanks,
      Harshit

  4. suresh k says:

    hey kohli, i have small clarrification yaar,

    Select on “File—>Send—>Mail Recipient ” below code is working in QTP

    objExcel.CommandBars(“Worksheet Menu Bar”).Controls(1).Controls(10).controls(1).excete

    what is the code for UFT
    Window(“Book1″).WinObject(“Ribbon”).WinTab(“Ribbon Tabs”).Select “File”&”mail”& recipent? how to write code for UFT?

    thank u

    • Hi Suresh,

      i tried recording it. got something like this :-

      Window(“Book1”).WinObject(“Ribbon”).WinButton(“File Tab”).Click
      Window(“Book1”).WinObject(“WinObject”).WinMenu(“WinMenu”).Select “Save & Send”
      Window(“Book1”).WinObject(“WinObject”).WinMenu(“WinMenu_2”).Select “Send Using E-mail”
      Window(“Book1”).WinObject(“WinObject”).WinButton(“Send as Attachment”).Click

      I think this should do.

      Thanks,
      Harshit

  5. dhinesh says:

    HI Kohli,

    need small doubt pls, in my application code is like below

    msgbox Javawindow(“….”).Javatable(“….”).getcelldata(2,2)
    but it returns empty… any method is there to capture Java table cell data,,,,,

    scenario: for ex: 2nd row 1st column(2,1) enter some value ie: 78 (for ex)
    immediately overriding the existing value in (2,2)..and it displays 78… but it is working fine, no functionality issue there, BUT as a tester i need to capture getcelldata value of (2,2)..
    to report actual =expected…but it is showing EMPTY… i need to capture getcelldata 2,2 value in one variable…

    pls help me on this…..

    thanks in advance…..

    • ramesh says:

      Hi Harshith,

      I am also waiting for the above Javatable script, can you please reply on the above query.

      thank u

      • Hi Ramesh/Dhinesh,

        Ideally getcelldata should work but since you are saying its returning empty, I would suggest please try the following :-

        1. Make sure you are referring to the correct table (there may be a nested table present).
        2. Try using .object to access the native properties of the javatable.
        3. Try contacting the developer of the application. He may have changed the settings of the object somehow.
        4. Try doing the same in UFT if you are using QTP.

        Let me know in case any of the above helps.

        Thanks,
        Harshit

  6. dhinesh says:

    HI harshith

    many thanks for reply, i am able to enter the values through below qtpscript in table..
    1)Javawindow(“..”).javatable(“–“).SetCelldata ….is working Fine… but Getcelldata is not working, that is the problem.
    2)Javawindow(“..”).javatable(“–“).highlight….also hilighted the same table.(im using UFT)

    Regards,
    Dhinesh.

  7. dhinesh says:

    Hello Harshith,

    Need one Urgent help please,first time i created Local repository and convert that local to shared Rep and save in some where(by using Export local objetcs), unfortunately that saved repository is missed, now i want to use same repository only,but iam not able to export the same repository second time?? i need that Repository only how to get???(this time export Local Objects is not working-disable) Note: Repository size is more(approx 6-7 MB)…

    many Many thanks in advance.

  8. dhinesh says:

    Thanks Harshith its Read Only…

  9. gangadhar says:

    Dear Harshith,

    i need Vbscript some logic please, i want to enter “5” ,”10″ numbers in one edit box,
    first time excetion enter 5 in that editbox,next time excetion enter 2nd number 10, again 3rd time exection agin 5…4th time excetion again 10..5th time again 5….same cycle going on.. But
    as of now i am using this code
    Browser().page().webedit().set randomnumber(5,10)
    im feeling this is not good,(it enters 6,7,8,9, also)…….o i want to enter 5, 10 only
    what is the script for this????
    Thanks in advance

  10. Sanuraj says:

    Hi Harshit,

    Can you please help me with selecting an item from a list box which is there in an excel sheet. Let me be more clear, I have an excel sheet in which there is a list box added through Excel feature Data > Data Validation > Data Validation. I need to validate the items in this list box using UFT. Please let me know if you need any more details to get a clear picture about the scenario.

    Thanks
    Sanuraj

  11. dhinesh says:

    Hi Harshit,

    Need one Urgent Help Please..,I want to retrieve the Numerical values only from “PDF File”..
    can u please post the script for this???

    waiting for your reply

    Many Thanks in Advance

  12. dhinesh says:

    Thanks alot for quickreply…I tried alot but i am getting below error message while using “Learnquicktest.manipulatePDF” object

    Error: ActiveX component can’t create object: “LearnQuickTest.ManipulatePDF”
    Note: Iam using UFT 11.5

    Thanks
    Dinesh

  13. dhinesh says:

    Done Harshit after installation its working fine……..many many thanks

  14. Dhinesh says:

    Hi Harshith,

    Need small help please, i want to read and write the values from downloaded excel file from webpage…….for ex: If excel sheet is saved in our system so,we are able to use by using Objex,ObjWb,Objws objects right, But this excel sheet is not saved in Drives and Desktop… Just visible in Menubar only..How to Read and write the values By using Objex,ObjWb,Objws objects.???(by using Recording i am able to do this but i am looking for operation through Create Objects only)
    set Objex=createobject(“excel.application”)
    set ObjWb=Objex.workbooks.(???) ………………….?????? (visible in Menubar only )

    Thanks,
    dinesh

  15. shravan says:

    Hi Harshith,

    I have created a code (Macro) which gets activated with a win button located in sheet1 of the same excel. Main code is residing in the module 1.

    My requirement is to click this button via UFT/VB so as to trigger the base code. I tried using following methods so far
    1. Workbook_Open() method to auto trigger once the excel is opened.
    2. Calling the sub directly via UFT which is activated via button in sheet 1

    However as the user defined inputs required and due to some other limitations none of the solutions listed above worked for me. I realised that if i may succeed to click the button located in sheet1 i will be able to meet the requirement.

    Please help.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog Stats
  • 168,130 hits
Categories

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 796 other followers

%d bloggers like this: