Exploit the Power of MS Excel through QTP

The objective of this article is to briefly understand the process of interaction of MS Excel and VBScripts.

For automating an application a framework is created in the beginning. This requires an independent structure for reporting and data. Microsoft Excel plays a very important role in this framework approach.

We can easily use the built-in mechanism of QTP through which we can display the test results in a predefined format. A result sheet gets generated after the execution of the test in QTP; which provides an in-depth view of the script – through which we can know the various point of failures, warnings and the passes.

In the test script we create customize various checkpoints at our will. Likewise the result file can also be customized based upon the checkpoints already created according to the defined criterion of Pass / Fail.

Generally while working in MS Excel, the user desires to generate a detailed report of the entire test. The idea of generating such detailed / customized report is to have the output in a format as per our own choice and to preserve the file in a centralized location.

The entire process can be performed in following basic steps:

Step-1: Understanding the hierarchy of MS Excel Application.

Step-2: Creation of the desired Object in MS Excel

Step-3: Creation of a new MS Excel workbook or opening an existing one.

Step-4: Setting the objects for various sheets in the workbook.

Step-5: Writing and fetching the data values in the cells.

Step-6: Saving and closing of the workbook

Step-7: Closing the application and clearing the memory

The above steps can be explained through suitable illustrations to understand the approach properly.

Understanding the hierarchy of Excel Application

It is believed that the user is fairly acquainted with the basics of MS Excel like:

1) What is MS Excel Application

2) What are Workbooks in Excel

3) What are Sheets in Excel

4) What are Cells in Excel

Hence I am skipping the basic explanation of the above basics of MS Excel & directly moving on to the main content of our topic.

Some of the simple VBScripts are being described below for performing various actions in MS Excel.

Creation of an Object in Excel:

In the process of reporting it is the first step. In MS Excel the reporting can be done in two ways like 1) in the background wherein the application shall not be visible 2) the application can be made visible to the user once the process of writing or fetching the data is going on.

However in both the above mentioned methodologies we need to create objects in Excel Application for example:

Dim xl
Set xl = CreateObject(“Excel.Application”)

When we run the above script, we can see a process named "Excel.exe" in the Windows task Manager.

Creating a new workbook or Opening an existing one:

After creation of the object in Excel, it implies that Excel application has been invoked, however it is not visible. From now on we can either continue to perform the operations in the invisible back ground alternatively we can make the application visible and then we can perform the operations.

To make the application visible:
xl.visible = true

To open a new Workbook:
xl.workbooks.Add

To open an existing Workbook:
xl.workbooks.Open(“File Name with complete path”)

Setting and accessing the objects of sheets in workbook:

After opening a workbook in Excel (A New one or opening an existing one), next activity is to feed some data in various cells in various sheets of our workbook.

MS Excel provides three sheets in a workbook by default, which are available to us for performing various operations. To access these sheets with great ease, we need to create objects referencing these sheets. This will help us in avoiding describing complete hierarchy time & again.

For example we wish to create a reference for a sheet with an index i, beginning from 1:
Set sht1 = xl.activeworkbook.sheets(i)

We can easily add or delete the desired sheets from the active workbook

To add a sheet in the workbook:
xl.activeworkbook.sheets.add

To delete a particular sheet: ( where i represents the index which begins from 1)
xl.activeworkbook.sheets(i).delete

To change the name of the sheet:
xl.activeworkbook.sheets(i).name = “Name of your choice”

To count total number of sheets in a workbook:
Countnt = xl.activeworkbook.sheets.count

Writing and fetching the data values in the cells:

To write the data in Excel sheet, we need to identify the Cell ID of the cell where the data is needed to be written. Similarly for accessing the data value from particular cells, we must know their Cell ID.

For an example we want to write some data in sheet2 cell ID as D8, the command can be written as under. Here “D” represents the Column Number & “8” represents the Row Number.


xl.activeworkbook.sheets(2).cells(8,4) = “hello”

To fetch the data from sheet3 cell ID A7:
Val = xl.activeworkbook.sheets(3).cells(7,1)

If an object has already been created in a particular sheet, we don’t have to repeat the complete hierarchy again, instead we can simply write:
Object.cells(row,col) = value

Saving and closing a workbook:

After finishing the work we can save the workbook to a desired location with a different name or save the changes made to an already existing open workbook.

To save the new workbook under a new name:
xl.activeworkbook.saveas “path_with_file_name.xls”

To save the changes made in an existing workbook:
xl.activeworkbook.save

To close a workbook:
xl.activeworkbook.close

Closing an application and clearing the memory:

To close the application:
xl.quit

To clear the memory from all objects:
Set xl = nothing

This is not the end; however it is just a beginning for us to explore the power of QTP in exploiting the potential of MS Excel through simple VBScripts.

Keywords: MS Excel, QTP, VBScript