Excel Handling in QTP

Like the way we access database using the COM object, here also using the COM (Component Object Model) approach only to  play with excel.

Having a quick look at the hierarchy of the excel:

Excel Application

Workbooks

Sheets

Cells

Not going into the deep details of the excel automation model, for our current discussion the outlined steps are: 

1.       Create an excel object.

2.       Opening an existing workbook or creating a new workbook.

3.       Assigning objects with reference to the sheets in the workbooks.

4.       Fetching and writing values in the cells of the sheet.

5.       Saving the Workbook.

6.       Closing the workbooks and Releasing the memory.

The approach will be creating an instance of EXCEL then open or create a WORKBOOK, will get a reference to the SHEET object from the workbook object and finally, will work on the CELLS of the sheet.

1.       Create an instance of the Excel Application.

         Set objExcel = CreateObject(“Excel.Application”)

             objExcel.visible = True

2.       Opening an existing workbook or creating s new workbook.

             Set objWorkBook = objExcel.Workbooks.Add

                             Or

             Set objWorkBook = objExcel.Workbooks.Open (sPath)

3.       Creating reference object for the sheets.

             Set objSheet = objWorkBook.Sheets(1)

Here, index “1” indicates the sheet index. We can use name of the sheet as well.

             Set objSheet = objWorkBook.Sheets(“Test_Sheet”)

If we want to add a new sheet, it wud be like

                                 Set objSheet = objWorkBook.Worksheets.Add

To delete a sheet:

                               objSheet.delete

To rename a sheet:

            

             objSheet.name = “Sheet_Name”

                              

4.       Fetching and Posting values in the cells of the sheet.

Writing in a particular cell ( 5th row and 7th column )

             objSheet.cells(5,7)  = “Test_Data”

Reading from a cell:

             sVar = objSheet.cells(5,7)

Taking a practical scenario in which we need to find a particular cell data from excel but we are not aware of as in how many rows and columns have been used and we have no idea about the cell address.

             sText_to_Find =  “Test_String”

             bDataFoundFlag = False

             Set objRange = objSheet.UsedRange

             For each objCell in objRange

                 If  objCell .Value = sText_to_Find  Then

                     bDataFoundFlag = True

                     Exit For

                 End If

             Next

             If bDataFoundFlag =  True Then

                Msgbox “Data in Cell ”& objCell.Address (, , 4)

             End If

Above code snippet will return the address of the cell having the text to find.

5.        Saving the workbook.

Saving a newly created workbook:

            objWorkBook.SaveAs “sPath”

Saving a opened workbook:

            objWorkBook.Save

6.       Closing the Workbook and releasing the memory.

Closing a workbook:

            objWorkBook.Close

Closing the excel instance:

            objExcel.Quit

Releasing the memory:

            Set objExcel = Nothing

------------------------------------------------------------------------

Stay tuned  (:

--

# Hims