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 or How to Read Data From Excel Through QTP or UFT?
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…..
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