VBScript Tutorial 9
(VBScript Excel Application Operations – 2, Database Operations and Dictionary Object)
Excel Data Comparison Examples:
1) Read data from an Excel file and Compare 2 Columns (One to One Binary Comparison).
Dim objExcel, objWorkbook, objWorksheet, i, RowsCount
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\gcreddy\Desktop\abcd.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
RowsCount = objWorksheet.UsedRange.Rows.Count
For i = 2 To RowsCount Step 1
Expected = objWorksheet.Cells(i, 1)
Actual = objWorksheet.Cells(i, 2)
If Expected = Actual Then
objWorksheet.Cells(i, 3) = “Passed”
Else
objWorksheet.Cells(i, 3) = “Failed”
End If
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
——————————————–
2) Read data from an Excel file and Compare 2 Columns (One to One Textual Comparison).
Dim objExcel, objWorkbook, objWorksheet, i, RowsCount
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\gcreddy\Desktop\abcd.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
RowsCount = objWorksheet.UsedRange.Rows.Count
For i = 2 To RowsCount Step 1
Expected = objWorksheet.Cells(i, 1)
Actual = objWorksheet.Cells(i, 2)
If StrComp (Expected, Actual, 1) = 0 Then
objWorksheet.Cells(i, 3) = “Passed”
Else
objWorksheet.Cells(i, 3) = “Failed”
End If
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
——————————–
3) Read data from an Excel file and Compare 2 Columns (Many to Many Textual Comparison).
Dim objExcel, objWorkbook, objWorksheet, i, RowsCount, j, Expected, Actual
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\gcreddy\Desktop\abcd.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
RowsCount = objWorksheet.UsedRange.Rows.Count
Flag = 0
For i = 2 To RowsCount Step 1
Expected = objWorksheet.Cells(i, 1)
For j = 2 To RowsCount Step 1
Actual = objWorksheet.Cells(j, 2)
If StrComp(Expected, Actual, 1) = 0 Then
Flag = 1
Exit For
Else
Flag = 0
End If
Next
If Flag = 1 Then
objWorksheet.Cells(i, 3) = “Passed”
Else
objWorksheet.Cells(i, 3) = “Failed”
End If
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
Database Objects in VBScript
i) Database Connection Object
It is used to connect to a Database (any database).
Note: Connection string only varies from one database to another.
Create Database Connection Object
Set Variable = CreateObject(“Adodb.Connection”)
——————————–
ii) Database Recordset object
It is used to perform operations on Database Tables (Records)
Create Database Recordset Object
Set Variable = CreateObject(“Adodb.Recordset”)
VBScript Examples:
1) Read data from a Database and perform Data Driven Testing for Login functionality.
Dim objConnection, objRecordset
‘Create Database Connection Object, it is used to connect to a Database
Set objConnection = CreateObject(“Adodb.Connection”)
‘Create Database recordset object, it is used to perform operations on Database Records.
Set objRecordset = CreateObject(“Adodb.Recordset”)
‘Create Connection String for MS Access Database
objConnection.Provider = (“Microsoft.Jet.OLEDB.4.0”)
objConnection.Open “C:\Users\gcreddy\Desktop\testdb.mdb”
‘Fetch data using SQL Statements
objRecordset.Open “Select Agent, Password from Login”,objConnection
While objRecordset.EOF = False
SystemUtil.Run “C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe”,””,”C:\Program Files\HP\Unified Functional Testing\samples\flight\app\”,””
Dialog(“Login”).Activate @@ hightlight id_;_1704654_;_script infofile_;_ZIP::ssf1.xml_;_
Dialog(“Login”).WinEdit(“Agent Name:”).Set objRecordset.Fields(“Agent”) @@ hightlight id_;_2097980_;_script infofile_;_ZIP::ssf2.xml_;_
Dialog(“Login”).WinEdit(“Password:”).Set objRecordset.Fields(“Password”) @@ hightlight id_;_197912_;_script infofile_;_ZIP::ssf3.xml_;_
wait 2
Dialog(“Login”).WinButton(“OK”).Click @@ hightlight id_;_197914_;_script infofile_;_ZIP::ssf4.xml_;_
Window(“Flight Reservation”).Close
objRecordset.MoveNext
Wend
objRecordset.Close
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing
—————————————————
2) Read data (Range of Records) from a Database and perform Data Driven Testing for Login functionality.
Dim objConnection, objRecordset, RecordCount
Set objConnection = CreateObject(“Adodb.Connection”)
Set objRecordset = CreateObject(“Adodb.Recordset”)
objConnection.Provider = (“Microsoft.Jet.OLEDB.4.0”)
objConnection.Open “C:\Users\gcreddy\Desktop\testdb.mdb”
objRecordset.Open “Select Agent, Password from Login”,objConnection
RecordCount = 1
While objRecordset.EOF = False
‘Msgbox RecordCount
If RecordCount > 1 And RecordCount < 5 Then
SystemUtil.Run “C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe”
Dialog(“Login”).Activate
Dialog(“Login”).WinEdit(“Agent Name:”).Set objRecordset.Fields(“Agent”)
Dialog(“Login”).WinEdit(“Password:”).Set objRecordset.Fields(“Password”)
wait 2
Dialog(“Login”).WinButton(“OK”).Click
Window(“Flight Reservation”).Close
End If
objRecordset.MoveNext
RecordCount = RecordCount + 1
Wend
objRecordset.Close
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing
————————————————
3) Export data from a database to an Excel file.
4) Export data from a database to a Text file.
5) Export data from an Excel file to a Text file
6) Export data from a text file to Excel file
———————————
Assignments
1) Export data from a Text file to a Database
2) Export data from an Excel file to a Database
————————————————–
3) Export data from a Database to an Excel file.
Dim objConnection, objRecordset, RecordCount
Dim objExcel, objWorkbook, objWorksheet
Set objConnection = CreateObject(“Adodb.Connection”)
Set objRecordset = CreateObject(“Adodb.Recordset”)
objConnection.Provider = (“Microsoft.Jet.OLEDB.4.0”)
objConnection.Open “C:\Users\gcreddy\Desktop\testdb.mdb”
objRecordset.Open “Select Agent, Password from Login”,objConnection
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\gcreddy\Desktop\abcd.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1, 1) = “Agent”
objWorksheet.Cells(1, 2) = “Password”
i = 2
Do Until objRecordset.EOF = True
objWorksheet.Cells(i, 1) = objRecordset.Fields(“Agent”)
objWorksheet.Cells(i, 2) = objRecordset.Fields(“Password”)
objRecordset.MoveNext
i = i + 1
Loop
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
objRecordset.Close
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing
————————————————
4) Export data from a database to a Text file.
Dim objConnection, objRecordset, RecordCount
Dim objFso, objTextstream
Set objConnection = CreateObject(“Adodb.Connection”)
Set objRecordset = CreateObject(“Adodb.Recordset”)
objConnection.Provider = (“Microsoft.Jet.OLEDB.4.0”)
objConnection.Open “C:\Users\gcreddy\Desktop\testdb.mdb”
objRecordset.Open “Select Agent, Password from Login”,objConnection
Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objTextstream =objFso.OpenTextFile(“C:\Users\gcreddy\Desktop\xyz.txt”, 2)
objTextstream.WriteLine “Agent Password”
Do Until objRecordset.EOF = True
objTextstream.WriteLine objRecordset.Fields(“Agent”) &”, “& objRecordset.Fields(“Password”)
objRecordset.MoveNext
Loop
objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing
objRecordset.Close
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing
———————————————
5) Export data from an Excel file to a Text file
Dim objExcel, objWorkbook, objWorksheet, RowsCount
Dim objFso, objTextstream
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\gcreddy\Desktop\abcd.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objTextstream =objFso.OpenTextFile(“C:\Users\gcreddy\Desktop\xyz.txt”, 2)
objTextstream.WriteLine “Agent Password”
RowsCount = objWorksheet.UsedRange.Rows.Count
For i = 2 To RowsCount Step 1
objTextstream.WriteLine objWorksheet.Cells(i, 1) & “, “& objWorksheet.Cells(i, 2)
Next
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing
—————————————-
6) Export data from a text file to Excel file
Dim objExcel, objWorkbook, objWorksheet
Dim objFso, objTextstream, myField, myLine
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\gcreddy\Desktop\abcd.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objTextstream =objFso.OpenTextFile(“C:\Users\gcreddy\Desktop\xyz.txt”)
objTextstream.SkipLine
objWorksheet.Cells(1, 1) = “Agent”
objWorksheet.Cells(1, 2) = “Password”
i = 2
While objTextstream.AtEndOfStream = False
myLine = objTextstream.ReadLine
myField = Split(myLine, “, “)
objWorksheet.Cells(i, 1) = myField(0)
objWorksheet.Cells(i, 2) = myField(1)
i = i + 1
Wend
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing
Dictionary Object
It is used to define key, value pairs.
It is equalent to Associated Array in Perl Script.
VBScript Vs Perl in case of Variables.
VBScript Perl
———————————-
Scalar Variables Scalar Variables
Array Variables Array variables
Dictionary Object Associated Arrays / Hash Variables
Array vs. Associate Array
In case of Arrays user can define values only, keys (Indexes) are pre-defined.
Din a(3)
a(0) = “India”
a(1) = 100
a(2) = #10/10/2010#
a(3) = “abcd”
Index Values
or
Key
——————-
In case of Associated Array/Dictionary object user can deine key, value pairs.
Dictionary Object Example:
Dim objDictionary
Set objDictionary = CreateObject(“Scripting.Dictionary”)
objDictionary.Add “Agent”, “Hyderabad”
objDictionary.Add “Password”, “mercury”
objDictionary.Add “AppPath”, “C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe”
objDictionary.Add 1, 100
objDictionary.Add “*”, “abc”
objDictionary.Add “d”, #10/10/2012#
Msgbox objDictionary.Count ‘6
objDictionary.Remove(“*”)
Msgbox objDictionary.Count ‘5
Msgbox objDictionary.Exists(“d”) ‘True
Msgbox objDictionary.Exists(“a”) ‘False
objDictionary.RemoveAll
Msgbox objDictionary.Count ‘0
Usage of Dictionary Object in UFT:
We can use dictionary keys as Global variables.
Example:
SystemUtil.Run objDictionary(“AppPath”)
Dialog(“Login”).Activate
Dialog(“Login”).WinEdit(“Agent Name:”).Set objDictionary(“Agent”)
Dialog(“Login”).WinEdit(“Password:”).Set objDictionary(“Password”)
Wait 2
Dialog(“Login”).WinButton(“OK”).Click
