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.

I am the founder and owner of the blog - TechTravelHub.com, always love to share knowledge on test automation,tools,techniques and tips.I am a passionate coder of Java and VBScript.I also publish articles on Travel ideas and great honeymoon destinations.Apart from these, I am a gear-head,love to drive across India. I have shared lots of articles here on How to travel several parts of India.Customization of cars aka car modification is my another hobby.Get in touch with me on [email protected]

4 Comments

  1. Kristofer Joganic Reply

    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….

  3. Angelia Coleman Reply

    I can see that your content probably doesn’t have much visits.
    Your articles are interesting, you only need
    more new visitors. I know a method that can cause a viral effect on your
    website. Search in google: Jemensso’s tricks

  4. Very nice post. I just stumbled upon your
    weblog and wanted to say that I’ve truly enjoyed surfing around
    your blog posts. In any case I will be subscribing to your
    feed and I hope you write again soon!

Write A Comment