MSOffice Automation

How to Automate any MSOffice Application

Automating any Microsoft Office Application can be a big challenge in itself. Purely because any Office Application(Excel/Word/PowerPoint/Outlook) can’t be directly automated with QTP’s object identification mechanism. During the course of my fights with QTP, there was one battle that required me to automate Excel,Word and PowerPoint. After hours of struggle and Googling,I have come up with techniques that can help to automate any feature of most Office Applications through VBScript in QTP.

Here’s my approach :-

1. Understand the Object Model of the Application – Microsoft has provided sufficient documentation that would help you figure out the hierarchy of the objects as well as the  properties/methods of those objects. It can be found at : http://msdn.microsoft.com/en-us/library/bb726434(v=office.12)

2. Figure out the VBA Equivalent of the operation that you are trying to Automate – From Microsoft’s msdn website, after figuring out the type of object you want to work on, see some sample codes available there and you would get a fair idea of the VBA Equivalent of the code you are trying to write in VBS (QTP).

3. Convert the VBA Code into VBS so that QTP can read it – Now this is the tricky part. There are functions that are common with both VBA and VBS. But sometimes there are differences. Most common issues encountered during this phase are :-

  • Function call in the VBS Code would need brackets if there are more than 1 parameters required.
  • VBA uses some predefined constants, we would have to find the numeric value of the constant by hit and trial. For eg : If the VBA Code uses a variable such as msoReadOnly, we would have to use hit and trial method to figure out what value it might have (usually variables like this have values like 0,1,2,3 etc)

If the above doesn’t make sense to you or if you want to practically see how we can automate any Office Application, keep following my blog as I’ll be writing a series of articles on how to automate Excel,Word,PowerPoint and Outlook with sample codes as well.

Advertisements

5 replies »

  1. I ma trying to automate powerpoint .. but QTP gets hanged when i spy/clcik on objects. Also object is indentified in below hierarcy .. Window > winobject > winbutton.Please help on this

  2. Thanks for yr quick reply.. I am using UFT 12.02. I want to click on Layout in PPT and count the templates in LAYOUT and verify the count.Please let me know hoe to spy on PPT object and add then to repository. Dou to object identified as winobject not able to take actions on PPT objects

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