Working with Excel objects in QTP

www.appsys.co.in

We  create framework for automating the application. For this we need the independent structure for reporting and data. Excel plays a very important role in this approach.

QTP has its own test result displaying mechanism in the predefined format. Once the test is run, the result sheet is generated which gives you the insight of the script – stating the point of failures, warnings and passes.

We create customized checkpoint in the script and it is possible to customize the result file also depending upon the checkpoint created will be passed or failed.

In most of the cases we want to create summarized or detailed report of the entire test in excels. The reason to create customized report is that one is able to keep the file in central location and to create the report in our own format.

In this article we are going to learn the interaction of Excel with VBScript.

The whole mechanism goes in the following steps:
1. Understanding the hierarchy of Excel Application.
2. Creating the Excel Object
3. Opening an existing workbook or creating the new one
4. Setting the objects for various sheets in workbook.
5. Writing and fetching the data values in the cells.
6. Saving and closing the workbook
7. Closing the application and releasing the memory

We will go through each of the above stated steps with a suitable example to understand the approach properly.

Understanding the hierarchy of Excel Application

We will not go into the details of the complete hierarchy of the Excel application but to the extend what is required.

Excel Application
Workbooks
Sheets
Cells

Creating the Excel Object

The first step towards the process of reporting via excel is to create object of Excel. Reporting in Excel can either be done in backend, without making the application visible or u can make it appear to user once the process of writing or fetching the data is going. In either way creating of the Excel Application object is required.
It goes as:
Dim xl
Set xl = CreateObject(“Excel.Application”)

Opening an existing workbook or creating the new one

Once the excel object has been created, it means that excel application has been invoked but is not visible. So either one can perform the operations like that or make the application visible and then 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.

Once the workbook has been opened, either existing or new one, we need to write some data in various cells in various sheets of that workbook.
By default there are 3 sheets in a workbook and various operations can be performed on. So one need create the object to reference these sheets as it becomes easy to access them and you don’t have to mention the complete hierarchy over and over again.

Say one has to create a reference for sheet with index i, which starts from 1
Set sht1 = xl.activeworkbook.sheets(i)

One can add or delete n number of sheets from the activeworkbook
To add a sheet in workbook –
xl.activeworkbook.sheets.add

To delete a particular sheet where i represent the index which starts from 1 –
xl.activeworkbook.sheets(i).delete

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

To count the total number of sheets in the workbook
Cnt = xl.activeworkbook.sheets.count

Writing and fetching the data values in the cells

To write the data in Excel sheet, one should know the cell address in which the data has to be written. Same thing goes for accessing the data from the cells

To write the data in sheet2 cell address as D8, we write the following command. Cell address here is represented by row number followed by column number –
xl.activeworkbook.sheets(2).cells(8,4) = “hello”
To fetch the data from sheet3 cell address A7 –
Val = xl.activeworkbook.sheets(3).cells(7,1)

If one has already created the object of the particular sheet, you don’t have to write the complete hierarchy but simply –
Object.cells(row,col) = value

Saving and closing the workbook

Once the work completed you can save the newly created workbook to a specified location or save the changes made to already existing opened workbook.

To save as in case of new workbook
xl.activeworkbook.saveas “path_with_file_name.xls”

To save in case of existing workbook
xl.activeworkbook.save

To close the workbook
xl.activeworkbook.close

Closing the application and releasing the memory

To close the application
xl.quit

To release the memory of all the objects
Set xl = nothing

AppSys
1st Floor, 624 Tonk Road (Above Apex Automobiles), Tonk Phatak
Near Krishna Mall, Jaipur, Rajasthan, India
+91-9785022310, 0141-3143687
info@appsys.in
www.appsys.in
www.appsys.co.in