VBScript Tutorial 9


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

0 comments:

Post a Comment