Code to Read Data From Excel Through QTP or UFT

0
85
Spread the love
  • 1
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
    1
    Share

Code to read data from excel through QTP

Code to Read Data From Excel Through QTP or UFT
Code to Read Data From Excel Through QTP or UFT

Code to Read Data From Excel Through QTP or UFT or How to Read Data From Excel Through QTP or UFT?

1st method:


datatable.importsheet "path of the excel file.xls",source sheetID,desination sheetID
n = datatable.getsheet("desination sheetname").getrowcount
for i = 1 to n
columnname = datatable.getsheet("destination sheetname").getparameter(i).name
if colunmname = knowncolumnname then
value = datatable.getsheet(destinationsheetname).getparameter(i)
end if
next
 

The more fine tuned way to code…..

2nd method:


set objexcel = createobject("excel.application")
Set objWorkbook = objExcel.WorkBooks.Open("path of the file.xls")
Set objDriverSheet = objWorkbook.Worksheets("name of the sheet")
columncount = objDriverSheet.usedrange.columns.count
rowcount = objDriverSheet.usedrange.rows.count
for i = 1 to colunmcount
columnname = objDriversheet.cells(i,1)
if columnname = knowncolumnname then
for j = 1 to rowcount
fieldvalue = objdriversheet.cells(j,i)
next
end if
next
 

How to Compare two Sheets?


'The CompareSheets method compares between two sheets.

'if there is a difference between the two sheets then the value in the second sheet
'will be changed to red and contain the string:
'"Compare conflict - Value was 'Value2', Expected value is 'value2'"
'sheet1, sheet2 - the excel sheets to be compared
'startColumn - the column to start comparing in the two sheets
'numberOfColumns - the number of columns to be compared
'startRow - the row to start comparing in the two sheets
'numberOfRows - the number of rows to be compared
Function CompareSheets(sheet1, sheet2, startColumn, numberOfColumns, startRow, numberOfRows, trimed) 'As Boolean
Dim returnVal 'As Boolean
returnVal = True
'In case that one of the sheets doesn't exists, don't continue the process
If sheet1 Is Nothing Or sheet2 Is Nothing Then
CompareSheets = False
Exit Function
End If
'loop through the table and fill values into the two worksheets
For r = startRow to (startRow + (numberOfRows - 1))
For c = startColumn to (startColumn + (numberOfColumns - 1))
Value1 = sheet1.Cells(r, c)
Value2 = sheet2.Cells(r, c)
'if 'trimed' equels True then used would like to ignore blank spaces
If trimed Then
Value1 = Trim(Value1)
Value2 = Trim(Value2)
End If
'in case that the values of a cell are not equel in the two worksheets
'create an indicator that the values are not equel and set return value
'to False
If Value1 <> Value2 Then
Dim cell 'As Excel.Range
sheet2.Cells(r, c) = "Compare conflict - Value was '" & Value2 & "', Expected value is '" & Value1 & "'."
Set cell = sheet2.Cells(r, c)
cell.Font.Color = vbRed
returnVal = False
End If
Next
Next
CompareSheets = returnVal
End Function
 

Create an excel sheet and enter a value into first cell through QTP/UFT?


Dim objexcel
Set objExcel = createobject("Excel.application")
objexcel.Visible = True
objexcel.Workbooks.add
objexcel.Cells(1, 1).Value = "Testing"
objexcel.ActiveWorkbook.SaveAs("D:ani.xls")
objexcel.Quit
 
Don't miss out!
Subscribe To Newsletter

Receive top technical news, lesson ideas, travel tips and more!

Invalid email address
Give it a try. You can unsubscribe at any time.

Spread the love
  • 1
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
    1
    Share

LEAVE A REPLY

Please enter your comment!
Please enter your name here