Code to Read Data From Excel Through QTP or UFT

2
631
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

2 COMMENTS

  1. I must show my thanks to this writer for bailing me out of this type of condition. As a result of exploring throughout the online world and meeting ideas which were not beneficial, I assumed my life was done. Being alive without the strategies to the problems you have solved as a result of your write-up is a critical case, and the ones that would have badly damaged my entire career if I hadn’t discovered your web site. Your own skills and kindness in playing with every item was vital. I am not sure what I would’ve done if I hadn’t discovered such a step like this. It’s possible to at this time look forward to my future. Thanks a lot so much for your reliable and results-oriented help. I will not be reluctant to suggest your site to any individual who would need tips on this topic.

  2. HI

    I have some clients who need good sites so we have found your site which is good but our budget
    is not high so please give us price for https://www.techtravelhub.com
    we need dofollow link
    we will pay you through paypal
    need time for paying 3 to 4 days because only need verification from client

    you can trust on us because we will not cheat you as we receive payment from client we will forward you..
    Wait….

LEAVE A REPLY

Please enter your comment!
Please enter your name here