Fetch Values From Excel - QTP

'Capture the input price from datasheet

strExpectedPrice = Fn_FetchValuesFromExcel (strTestCaseDataFile,"Parameters","Price","TestCaseName",strTestCaseName)


Function Fn_FetchValuesFromExcel(strFilePath, strSheetName, strSelectFields, strWhereClauseFields, strWhereClauseVals)
   Dim objConnection, strSQL, objRecordSet, intRow, strUniqueValue
   Set objConnection = getDBConnectionExcelObject (strFilePath)
   objConnection.Open
  
   arrSelect = split(strSelectFields,"|")
   strSelectFields = arrSelect(0)
   For i=1 to ubound(arrSelect)
    strSelectFields =  strSelectFields & "," & arrSelect(i)
   Next
   arrWhereFields = split(strWhereClauseFields,"|")
   arrWhereVals = split(strWhereClauseVals,"|")
  
   Set objRecordSet = CreateObject("ADODB.Recordset")
   If ubound(arrWhereFields)>1 Then
    strSQL =  "SELECT "&strSelectFields&" FROM [" & strSheetName & "$]"&" WHERE " & arrWhereFields(0) & " = '" &arrWhereVals(0) & "' And " &arrWhereFields(1)& " = '" &arrWhereVals(1) & "'"
   Else
   strSQL =  "SELECT "&strSelectFields&" FROM [" & strSheetName & "$]"&" WHERE " & arrWhereFields(0) & " = '" &arrWhereVals(0) &"'"
   End If
  
      objRecordSet.Open strSQL, objConnection,3
   intCount = objRecordSet.Fields.Count
   arrReturnValues = objRecordSet.Fields.Item(0)
   For i=1 to intCount-1
  arrReturnValues = arrReturnValues &  "|" & objRecordSet.Fields.Item(i) 
   Next
  
       objRecordSet.Close
       Set objRecordSet = Nothing
   
    Call closeDBConnectionObject (objConnection)
   CustomReporter micInfo, "Fetch Values Info","Values: " &arrReturnValues & " have been successfully fetched for Where Clause values: " & strWhereClause &" From Path: " &strFilePath &" For Sheet: " &strSheetName
   Fn_FetchValuesFromExcel = arrReturnValues
End Function

Public Function getDBConnectionExcelObject (strFilePath)
 Dim objConnection, objExcel, strExcelVersion, strVersion
 Set objExcel=CreateObject("Excel.Application")
 strExcelVersion=objExcel.Version
 Set objExcel = Nothing
 Set objConnection = CreateObject("ADODB.Connection")
 If strExcelVersion<=11.0 Then
  objConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
  strVersion="8.0"
 ElseIf strExcelVersion>=12.0 Then
  objConnection.Provider = "Microsoft.ACE.OLEDB.12.0"
  strVersion="12.0"
 End If
 objConnection.ConnectionString ="Data Source= " & strFilePath &";" &"Extended Properties=Excel " & strVersion & ";"
 Set getDBConnectionExcelObject = objConnection
End Function