This below code explains how to use scripting.dictionary to read data from excel object and create a dictionary from the excel. Using dictionary object, we can data drive a test and easily use the test data in test script based on dictionary Key-value pair.
For details on scripting.dictionary object, see Dictionary Object in QTP
Below code explains how to use dictionary object for creating data driven tests using Dictionary object:
call func_getDictionaryData("c:/test.xlsX", "Sheet1", 5)
Function func_getDictionaryData(strExcelFile,strsheetName, iRow)
On error resume next
'Create an instance of Excel object
Set objExcel = Createobject("Excel.Application")
objExcel.visible = false
''open the workbook and the specified worksheet, These two are required as argument to the function
Set objExcelbook = objExcel.Workbooks.Open(strExcelFile)
Set objExcelsheet = objExcelbook.Worksheets(strsheetName)
''Get the rows and column count of the excelsheet
intColCnt = objExcelsheet.UsedRange.Columns.Count
intRowCnt = objExcelsheet.UsedRange.Rows.Count
If (iRow>intRowCnt) Then
msgbox "Row number provided in function is greater than the rows in the sheet"
else
''Create a dictionary object
Set objDictdta = Createobject("Scripting.dictionary")
For i=1 to intColCnt
dictKey = Trim(objExcelsheet.Cells(1,i))
dictVal = Trim(objExcelsheet.Cells(iRow,i))
objDictdta.Add dictKey,dictVal
Next
End If
objExcel.close
Set objExcel = nothing
objDictdta.close
Set objDictdta = nothing
If (err.number>0) then
msgbox "error in the file: "+ err.description
End If
End Function
