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 :

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.


Eat, Sleep, Automate. REPEAT !

Tagged with: , , , , ,
Posted in MSOffice Automation
2 comments on “How to Automate any MSOffice Application
  1. shashank chaturvedi says:

    Awesome, thanks for sharing harshit

Leave a Reply

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

You are commenting using your 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

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

Join 796 other followers

%d bloggers like this: