How to work with Excel using QTP

Here in this video i demonstrated how to work with Excel files using QTP.

Following are the concepts are covered in this video:

•How to get a cell value

•How to set a cell value

•How to find a string

•How to add an Excel sheet

•How to set a font color

•How to set a interior color for a cell

•How to copy paste cell values

•How to insert an image

Following is the QTP Script for above functions:

o_FileName1="C:\Test1.xls"
o_FileName2="C:\Test2.xls"
Set xlObj=createobject("Excel.Application")
Set xlWBObj=xlObj.workbooks.open(o_FileName1)
Set xlWSObj=xlWBObj.worksheets(1)

print xlWSObj.cells(2,1).value
'
xlWSObj.cells(3,2).value="IJK"

Set Cell=xlWSObj.range("A:Z").find("XYZ")
print Cell.Address



Set xlWSLastSheetObj=xlWBObj.worksheets( xlWBObj.Sheets.count)
xlWBObj.worksheets.add(,xlWSLastSheetObj).name="TestSheet3"


xlWSObj.cells(2,1).Font.Color=vbRed
xlWSObj.cells(2,1).Font.Name="Verdana"
xlWSObj.cells(2,2).Interior.Color=vbBlue

xlWSObj.range("A2").select
xlWSObj.range("A2:B2").select
xlObj.selection.copy
xlWSObj.range("E5").select
xlWSObj.paste

xlWSObj.cells(6,"B").select
Set x=xlWSObj.pictures.insert("C:\174.JPG")
Set objRange=xlWSObj.range("B6:C13")
With x
    .Top=objRange.Top
    .Left=objRange.Left
    .Width=objRange.Width
    .Height=objRange.Height
End With

xlWBObj.SaveAs o_FileName2
xlWBObj.close
xlObj.application.quit

Set xlWBObj=nothing
Set xlObj=nothing