QTP/VB Scripting - Read data from Excel using Adodb Connection

Assume that you have your test data in an Excel file spread across multiple sheets. Now if you need to read a particular value from any of the spread sheets during the script execution, how would you do that?

Say for example please have a look at the following sample spread sheet:



Now if you need to retrieve the Phone number for customer ID 12345696 at any point in your script, how would you do that?

This can be done in a very simple way by writing a function using Adodb connection. In this post we would discuss about that.

First you need to create a VBS file with the following function and attach the VBS file to your QTP script.

'Description: Function used to read data from test data.xls
'Arguments 1:  sReqParamColName(String)-- Name of the Column from which the data needs to be read 
'Arguments 2: sCondColName(String)-- Column name that is used in where condition
'Arguments 3:  sCondColValue(String)-- Condition value using which the data needs to be retrieved 
'Arguments 4: sSheetName(String)-- Sheet name from which the data needs to be read

Function GetReqParameter( ByVal sReqParamColName, ByVal sCondColName, ByVal sCondColValue, ByVal sSheetName)

    Dim objAdodbConnection, objAdodbRecordSet
    Dim sQuery

    Const adOpenForwardOnly = 0
    Const adOpenKeyset      = 1
    Const adOpenDynamic     = 2
    Const adOpenStatic      = 3
    Const adLockOptimistic = 3


    Set objAdodbConnection = CreateObject("ADODB.Connection")
    Set objAdodbRecordSet = CreateObject("ADODB.Recordset")
   
    objAdodbConnection.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\Documents and Settings\qa\Desktop\TestData.xls;ReadOnly=False;"
    objAdodbConnection.Open
    sQuery="Select " & sReqParamColName & " FROM ["& sSheetName &"$] Where " & sCondColName & "='" & sCondColValue& "'"
    objAdodbRecordSet.Open sQuery, objAdodbConnection, adOpenKeyset, adLockOptimistic                 
   
    GetrReqParameter=objAdodbRecordSet(0).Value
   
    objAdodbRecordSet.Close
    objAdodbConnection.Close

End Function

'*** As noticed above, Please make sure that you enclose sheet name in Square brackets and append a $ before closing the square bracket. This is required to access data from a spread sheet.

'*** Make sure that you close the Recordset and  Connection at the end of function

In your QTP script you just need to call the function by passing the variables as shown below:

Dim sPhonenumber

sPhoneNumber=GetReqParameter("PhoneNumber","CustomerID","12345696", "CustPersonalData")