Write a script in QTP to import data from an external data sheet using Excel application File system object?

We will write a script and import the run time data from the external excel sheet and also fetch the order number to the run time data table and store it under the orderno column. Also we would print a pass or a fail based on the order no. If the order number is generated then it will print pass and in green color and if the order number is not generated then it will print fail in green color under the result column. For this apart from the four columns you need to add a column for Orderno and a column for Results and leave them blank because the values will be inserted during run time.

'Script to insert an order and print the Order Number

'Create an object to interact with Excel Library

Set xlapp = CreateObject("Excel.Application")

'Open the excel workbook

Set wbook = xlapp.workbooks.Open("E:\InsertOrder.xls")

'Set the pointer to the worksheet

Set wsheet = xlapp.worksheets(1)

'Get rowcount

rowcount = wsheet.usedrange.rows.count

For i = 1 to rowcount-1

Window("Flight Reservation").Activate
Window("Flight Reservation").WinButton("New").Click
Window("Flight Reservation").WinObject("Date of Flight:").Type wsheet.cells(i+1,1).Value
Window("Flight Reservation").WinComboBox("Fly From:").Select wsheet.cells(i+1,2).Value
Window("Flight Reservation").WinComboBox("Fly To:").Select wsheet.cells(i+1,3).Value
Window("Flight Reservation").WinButton("FLIGHT").Click
Window("Flight Reservation").Dialog("Flights Table").WinButton("OK").Click
Window("Flight Reservation").WinEdit("Name:").Set wsheet.cells(i+1,4).Value
Window("Flight Reservation").WinButton("Insert Order").Click
Wait 8
Orderno = Window("Flight Reservation").WinEdit("Order No:").GetRoProperty("text")
wsheet.cells(i+1,5).Value = Orderno

If Orderno = null Then
wsheet.cells(i+1,6).Value = "Fail"
wsheet.cells(i+1,6).Font.Colorindex = 3
Else
wsheet.cells(i+1,6).Value = "Pass"
wsheet.cells(i+1,6).font.colorindex = 10

End If

Next

wbook.save
wbook.close
xlapp.quit

Set wsheet = nothing
Set wbook = nothing
Set xlapp = nothing