Overview
Test automation engineers usually keep all the test data in excel spread sheets. They write code in VB Script to read those data and use them as input to test the applications.So we achieve them in the following steps.
1. Create ADODB Connection
2. Create ADODB Record set
3. Loop through record set and read data
4. Store those data in VBScript Dictionaries and use them whenever required
Test Data Sheet
Below snap shot shows excel data.
Code
Following code makes ADODB connection to excel spread sheet, reads data and storing them to dictionary.Dim objCon,objRs,strQuery,objDict,Dictionary1
'Set Dictionary1=CreateObject("Scripting.Dictionary")
'Read Data from .xls
'Set Dictionary1=fnReadExcelDataToDictionary("C:\Raman\Training\USBANK\Address.xls","Select * from [Sheet1$]")
'Read Data from .xlsx
'Set Dictionary1=fnReadExcelDataToDictionary("C:\Raman\Training\USBANK\Address.xlsx","Select * from [Sheet1$]")
Set Dictionary1=fnReadExcelDataToDictionary("C:\Raman\Training\USBANK\TestData.xlsx","Select * from [Sheet1$]")
MsgBox "Dictionary1 Count - "&Dictionary1.Count
MsgBox Dictionary1("rcTypeCode")
MsgBox Dictionary1("prCode")
MsgBox Dictionary1("imDestination")
MsgBox Dictionary1("imOrigin")
MsgBox Dictionary1("fcDate")
MsgBox Dictionary1("fcTime")
MsgBox Dictionary1("fiModifier")
MsgBox Dictionary1("rcSize")
MsgBox Dictionary1("bcFactor")
MsgBox Dictionary1("frCode")
MsgBox Dictionary1("dsName")
MsgBox Dictionary1("orName")
MsgBox Dictionary1("rfCode")
Call DisposeObjects()
Public Function fnReadExcelDataToDictionary(strDataSource,strQuery)
Set objCon=CreateObject("ADODB.Connection")
Set objRs=CreateObject("ADODB.Recordset")
Set objDict=CreateObject("Scripting.Dictionary")
'strDataSource="C:\Raman\Training\USBANK\Address.xls"
'strQuery="Select * from [Sheet1$]"
'objCon.Provider="MSDASQL"
strDataSourceArr=Split(strDataSource,".")
Select Case strDataSourceArr(1)
Case "xls"
'MsgBox "Before connectionstring xls : "&strDataSource
objCon.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ="&strDataSource&"; HDR=Yes;ReadOnly=False;"
Case "xlsx"
'MsgBox "Before connectionstring xlsx : "&strDataSource
objCon.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="&strDataSource&"; HDR=Yes;ReadOnly=False;"
End Select
objCon.Open
' Check for error
If Err <> 0 Then
MsgBox Err.Number
MsgBox Err.Description
'Exit Function
Else
'MsgBox "Connection successful."
End If
'strQuery="Select * from [Sheet1$]"
objRs.Open strQuery,objCon,1,3
'MsgBox objRs.RecordCount
If Not objRs.RecordCount<0 And Not objRs.BOF And Not objRs.EOF Then
objRs.MoveFirst
l=0
While Not objRs.EOF
objDict.Add "rcTypeCode",""""&objRs.Fields("rcTypeCode")&""""
'MsgBox objRs.Fields("rcTypeCode")
objDict.Add "prCode",""""&objRs.Fields("prCode")&""""
objDict.Add "imDestination",""""&objRs.Fields("imDestination")&""""
objDict.Add "imOrigin",""""&objRs.Fields("imOrigin")&""""
objDict.Add "fcDate",""""&objRs.Fields("fcDate")&""""
objDict.Add "fcTime",""""&objRs.Fields("fcTime")&""""
objDict.Add "fiModifier",""""&objRs.Fields("fiModifier")&""""
objDict.Add "rcSize",""""&objRs.Fields("rcSize")&""""
objDict.Add "bcFactor",""""&objRs.Fields("bcFactor")&""""
objDict.Add "frCode",""""&objRs.Fields("frCode")&""""
objDict.Add "dsName",""""&objRs.Fields("dsName")&""""
objDict.Add "orName",""""&objRs.Fields("orName")&""""
objDict.Add "rfCode",""""&objRs.Fields("rfCode")&""""
objRs.MoveNext
l=l+1
Wend
Else
MsgBox "No records found in the excel sheet"
End If
MsgBox "Dictionary Count : " &objDict.Count
Set fnReadExcelDataToDictionary=objDict
End Function
Public Function DisposeObjects()
Set objDict=Nothing
Set objRs=Nothing
Set objCon=Nothing
MsgBox "objCon disposed"
End Function
