Working with Excel Sheet using QTP

Create a below Excel File and saved as name Book1

Use below code for working with Excel using QTP

o_fileName1="c:\Book1.xls"
o_fileName2="c:\Book2.xls"
Set xlObj = Creaeteobject("Excel.Application")   ' Create the object for Excel
Set xlWBObj = xlObj.workbooks.open(o_fileName1)  'Open the Excel
Set xlWSObj = xlObj.Worksheets(1)  'Sheet number in Excel

'Print value present in Excel cell

print xlWSObj.cells(1,1).value  'Print the value present in Cell (1,1) in Excel

'Insert value into the Excel Cell

xlWSObj.cells(3,1).value = "QTP"   ' Insert "QTP" into cell (3,1) in Excel

'Save the Excel File if we want to save in same file

xlWSObj.save   'save changes into same Excel Sheet  .

'Save the Excel in another file

xlWSObj. SaveAs c:\Book2.xls    ' save the changes into defferent Excel
xlWBObj.close
xlObj.application.close

Set xlWBObj = nothing
Set xlObj = nothing

o_fileName1="c:\Book1.xls"

Set xlObj = CreaeteObject("Excel.Application")   ' Create the object for Excel
Set xlWBObj = xlObj.workbooks.open(o_fileName1)  'Open the Excel

Set xlWSObj = xlObj.Worksheets(1)  'Sheet number in Excel

' Find the Perticular String in Excel Sheet

Set cell= xlWSObj.range("A,Z").find("Tamil")  'Find the string "Tamil"  from Col A to Col Z  in Excel Sheet
print cell.Address  ' Print the Address where Tamil Present in Excel   O/P  $A$1  ( i.e Cell (1,1) = "Tamil" )

' Add New Sheet  after find Last Sheet in Excel

Set xlCountWSSheet = xlWBObj.workSheets(xlWBObj.Sheets.Count)  ' Find the last Work Sheet
xlWBObj.worksheets.add(xlCountWSSheet).name="New Sheet"  '
                             ' Sheet number starts from 0. So xlCountWSSheet have the last number of  sheet.

' Add New Sheet  after Sheet  2 in Excel

Set xlCountSecondSheet = xlWBObj.workSheets(2)  ' Work Sheet 2
xlWBObj.worksheets.add(xlCountSecondSheet).name="New Sheet"  '

xlWSObj.save   'save changes into same Excel Sheet  .

xlWBObj.close
xlObj.application.close

Set xlWBObj = nothing
Set xlObj = nothing



Change the Font color,Font Name and interior color


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


Copy one value from cell and paste into another one cell

xlWSObj.range("A2").Select
xlObj.selection.copy
xlWSObj.range("C4").Select
xlWSObj.paste


Copy one or more value from cell and paste into another one or more cell

xlWSObj.range("A2,C2").Select
xlObj.selection.copy
xlWSObj.range("C4").Select
xlWSObj.paste


Insert image into Excel at pericular cell


xlWSObj.cells(6,"B").select
xlWSObj.pictures.insert("c.\image.PNG")