Blog

QTP General Function to Read Input Data from Excel and store it in an array.

The below function reads data from an excel file stored at some location, into an array. 

   1: Function ReadData(TestId)
   2:     Dim DataArray()
   3:     'Specify  Values of path
   4:     ExcelPath="path of excel file"
   5:     WorkSheetName1="Test_InputData"
   6:     'Create the File System Object
   7:     CheckFile(ExcelPath)
   8:     'if file exists, proceed as below
   9:     'Create excel object
  10:     Set ObjExcel = createobject("excel.application") 
  11:     Set ObjWorkbook = ObjExcel.WorkBooks.Open(ExcelPath)
  12:     Set ObjSheet = ObjWorkbook.Worksheets(WorkSheetName1)
  13:     'Count  number of rows and columns
  14:     iRowCount = ObjSheet.usedrange.rows.count
  15:     jColumnCount=ObjSheet.usedrange.columns.count
  16:     'Retrieve row with current test  name
  17:     For i=1 to iRowCount
  18:         TestName=Trim(ObjSheet.cells(i,1).value)
  19:         'Retrieve value of  input data in array for current 
test
  20:         If  TestName=TestId Then
  21:             For j=1 to jColumnCount
  22:                 ReDim Preserve DataArray(j)
  23:                 DataArray(j)=Trim(ObjSheet.cells(i,j).value
) 
  24:                 Next
  25:             Exit For
  26:         End If
  27:     Next
  28:     'Close excel sheet
  29:     objExcel.DisplayAlerts = False
  30:     objExcel.Quit
  31:     ' destroy the other objects
  32:     Set objFS = Nothing
  33:     Set ObjExcel = Nothing
  34:     Set ObjSheet = Nothing
  35:     ReadData=DataArray
  36: End Function
Line Number Wise Explanation:-

The excel file comprises of a workbook and the workbook then contains sheet(s). We are using an excel sheet which contains it’s first column as the testId or TestName.

1: We pass the TestId or TestName as a string to the function argument.

4: Specify the path name in argument “ExcelPath”. Here we can also extract the path name from a XML file by using the below code.

   1: Environment.LoadFromFile("pathEnvFile.xml")
   2: ExcelPath=Environment.Value("INPUTEXCELPATH")

we load the environment variables from the XML file. The contents of XML file can be as below.

   1: <Environment>
   2:     <Variable>
   3:         <Name>FLOWREPOSITRY</Name>
   4:         <Value>pathShared_Repository.tsr</Value>
   5:     </Variable>
   6:     <Variable>
   7:         <Name>FUNCTIONLIBRARY</Name>
   8:         <Value>PathGeneralFunctionLibrary.qfl</Value>
   9:     </Variable>
  10:     <Variable>
  11:         <Name>INPUTEXCELPATH</Name>
  12:         <Value>PathTestData.xls</Value>
  13:     </Variable>
  14:     <Variable>
  15:         <Name>OUTPUTEXCELPATH</Name>
  16:         <Value>PathTestResult.xls</Value>
  17:     </Variable>
  18:     <Variable>
  19:         <Name>RECOVERYSCENARIO</Name>
  20:         <Value></Value>
  21:     </Variable>    
  22: </Environment>

5:   Define the variable to store name of worksheet.
7:   Calling the function to check if the file name exists or not.
10: Create the object for Excel
11: Create the object for workbook
12: Create the objects for sheet.
14: Count the number of rows in input sheet and store in variable iRowCount.
15: Count the number of columns in input sheet and store in variable iColumnCount.
17: The outer loop is used for retrieving row which contains the test name or test id same as that was passes in the function
18: for each row, 1st column value is saved in the variable TestName
20: The controls goes into If condition if the TestName retrieved is same as TestId passed. Other wise it is skipped and goes to next row till end of rows.
21: The inner for loop traverses through the columns of required test.
23: each column value is saved in DataArray.
25: Exit the outer for loop after all data has been retrieved.
26: Exit the if statement
29: We need to close the excel object.
33: We should destroy the Objects as not doing so would
35: This statement returns the array “DataArray” to the calling function.

Post Views: 493
Tags: ,