External File [Excel] Parameterization
Implementing parameterization by using External File
External files are the files where we maintain the data outside of QTP, like ms-Excel, Notepad, Ms-Word, XML…
Working with MS-EXCEL
Creating Excel Object: - Set objXL = CreateObject("Excel.Application")
Creating Work Book Object: - Set objWrkBk = objXL.Workbooks.Add
Open an Existing Work Book: - Set objWrkBk = objXL.Workbooks.Open("E:\01 My Scripts\external EV.xml")
Creating a Work Sheet Object: - Set objWrkSht = objWrkBk.Worksheets("Sheet1") 'Activating the Sheet
Find the No. of Rows: -
MsgBox objWrkSht.UsedRange.Rows.Count
Find the No. of Columns:-
MsgBox objWrkSht.UsedRange.Columns.Count
Retrieving a required Cell Value: - MsgBox objWrkSht.Cell(3,2)
Entering data to Cell: - objWrkSht.Cell(14,8) = "QTP"
Save opened Work Book: - objWrkBk.Save
Save the New Work Book: - objWrkBk.SaveAs"Path"
Close the Work Book: - objWrkBk.Close
Close Excel: - objXL.Quit
Kill Excel Process: - Set objWrkSht = Nothing
Set objWrkBk = Nothing
Set objXL = Nothing
External File [Excel]
ScriptNo22: - Write a script to create orders maintain data in Excel file
'Option Explicit
'Dim objXL,objWrkBk,objWrkSht,iRowCnt
Set objXL = CreateObject("Excel.Application")
Set objWrkBk = objXL.Workbooks.Open("E:\01 My Scripts\Excel.xls")
Set objWrkSht = objWrkBk.Worksheets("Sheet1")
iRowCnt = objWrkSht.UsedRange.Rows.Count
Set obj = Window("Flight Reservation")
For index = 2 To iRowCnt
obj.Activate
obj.WinMenu("Menu").Select "File;New Order"
dDate = objWrkSht.Cells(index,1) '(Row,Column)
obj.WinObject("Date of Flight:").Type dDate
sFrom = objWrkSht.Cells(index,2)
obj.WinComboBox("Fly From:").Select sFrom
sTo = objWrkSht.Cells(index,3)
obj.WinComboBox("Fly To:").Select sTo
obj.WinButton("FLIGHT").Click
obj.Dialog("Flights Table").WinList("From").Select(0)
obj.Dialog("Flights Table").WinButton("OK").Click
sName = objWrkSht.Cells(index,4)
obj.WinEdit("Name:").Set sName
sCls = objWrkSht.Cells(index,5)
obj.WinRadioButton(sCls).Set
iTckts = objWrkSht.Cells(index,6)
obj.WinEdit("Tickets:").Set iTckts
obj.WinButton("Insert Order").Click
While obj.WinButton("Update Order").GetROProperty("Enabled") = "False"
Wend
iOrdNo = obj.WinEdit("Order No:").GetROProperty("Text")
If IsEmpty(iOrdNo) = "False" Then
objWrkSht.Cells(index,7) = obj.WinEdit("Price:").GetROProperty("Text")
objWrkSht.Cells(index,8) = obj.WinEdit("Total:").GetROProperty("Text")
objWrkSht.Cells(index,9) = obj.WinEdit("Order No:").GetROProperty("Text")
objWrkSht.Cells(index,10) = "Order is Generated"
Else
objWrkSht.Cells(index,10) = "Order is Not Generated"
End If
Next
objWrkBk.Save
objWrkBk.Close
objXL.Quit
Set objWrkSht = Nothing
Set objWrkBk = Nothing
Set objXL = Nothing
Renaming a Sheet Name: -
Set objXL = CreateObject ("Excel.Application")
Set objWrkBk = objXL.Workbooks.Open("E:\01 My Scripts\Excel1.xlsx")
Set objWrkSht = objWrkBk.Worksheets("Sheet2")
objWrkSht.Name = "User_Data1"
Add a New sheet to Workbook: -
objWrkBk.Sheets.Add
Delete an Existence Sheet: -
objWrkBk.Sheets("Sheet3").Select
objXL.ActiveWindow.SelectedSheets.Delete
Auto fit for require Column in Active Sheet: -
objWrkSht.Columns("D:D").EntireColumn.AutoFit
Wrap Text {Format Column}: -
'objWrkSht.Columns("A1:D1").HorizontalAlignment = xlGeneral
'objWrkSht.Columns("A1:D1").VerticalAlignment = xlTop
objWrkSht.Columns("A1:D1").WrapText = True
Bold and Color to one Cell: -
objWrkSht.Cells(8,8).Font.Bold = True
objWrkSht.Cells(8,8).Font.Color = vbRed
objWrkSht.Cells(8,8) = "Suresh"
objWrkSht.Cells(8,8).Interior.ColorIndex = 55
ScriptNo23: - Write a script to retrieve val1 values from sheet1, retrieve val2 values from sheet2 and perform mathematical operation and retrieve the results in sheet3
Working with NOTEPAD
Creating notepad Object: -
Set objFile = CreateObject ("Scripting.FileSystemObject")
Creating Notepad Pointer: -
Syntax: -Set objPtr = objFile. OpenTextFile (“Path of Notepad”,Mode[,True])
Set objPtr = objFile. OpenTextFile ("E:\01 My Scripts\1.txt",1)
Retrieving Value from notepad: -
Retrieving a Line: objPtr.ReadLine
Retrieving all Values: objPtr.ReadAll
Retrieving One by One on till end of Line:
While objPtr.AtEndOfLine = "False"
MsgBox objPtr.SkipLine 'Even
MsgBox objPtr.ReadLine
MsgBox objPtr.SkipLine 'Odd
Wend
Retrieving given Line value: -
Set objFile = CreateObject ("Scripting.FileSystemObject")
Set objPtr = objFile. OpenTextFile ("E:\01 My Scripts\1.txt",1)
iLine = 1
iFlag = 0
While objPtr.AtEndOfLine = "False"
If iLine = 5 Then
MsgBox objPtr.ReadLine
iFlag = 1
Else
ival1 = objPtr.ReadLine
End If
iLine = iLine + 1
Wend
If iFlag= 0 Then
MsgBox "Line number not found" End If
Entering the Data/values into notepad: -
Set objFile = CreateObject("Scripting.fileSystemObject")
Set objPtr = objFile.OpenTextFile("E:\01 My Scripts\2.txt",2,True )
objPtr.write "Suresh"
objPtr.WriteLine "Sreekar"
objPtr.WriteLine"Prsad"
objPtr.WriteLine "Srilatha"
objPtr.Write "Sushma"
objPtr.WriteLine"Subashini"
objPtr.Write"Sandeep"
objPtr.Write"Sanjay"
Notepad Object Method: -
1. CopyFile
2. CopyFolder
3. CreateFolder
4. CreateTextFile
5. DeleteFile
6. DeleteFolder
7. FileExists
8. FolderExists
9. OpenTextFile
CopyFile: - Copy one file content to another file of same file types
Syntax: - objFile.CopyFile"path of source file","path of destination file"
Ex: - objFile.CopyFile" D:\sample1.txt ","D: \sample2.txt"
CopyFolder: - Copy one file content to another file of same file types
Syntax: - objFile.CopyFolder"path of source file","path of destination file"
CreateFolder: - It is used to create a required folder in required path
Syntax: -objFile.CreateFolder”path”
Ex: - objFile.CreateFolder”d:\test data\sample”
Creating a Folder with Current System data & time:
Set objFile = CreateObject("Scripting.FileSystemObject")
iFoldername = Now
iFoldername = Replace(iFoldername,"/","_")
iFoldername = Replace(iFoldername,":","_")
objFile.CreateFolder "E:\01 My Scripts\"&iFoldername
CreateTextFile: - By using this method we can create new Notepad File
Ex: - Set objFile = CreateObject("Scripting.fileSystemObject")
objFile.CreateTextFile("E:\01 My Scripts\Sample3.txt")
DeleteFile: - Used to delete existing file
DeleteFolder: - Used to delete existing Folder
FileExists: - By using this method we can find file exist:
It returns True if file exist
It returns False if file exist
Ex: - MsgBox objFile.FileExists("E:\01 My Scripts\Sample3.txt")
FolderExists: - By using this method we can find Folder exist:
It returns True if file exist
It returns False if file exist
Ex: - MsgBox objFile.FolderExists("E:\01 My Scripts")
OpenTextFile: - It is used to open exist text file for read/write/ append
Ex: - Set objPtr = objFile.OpenTextFile("E:\01 My Scripts\2.txt",2)
ScriptNo24: - Write a script to copy sample excel data into Notepad
Set objFile = CreateObject("Scripting.fileSystemObject")
Set objPtr = objFile.OpenTextFile("E:\01 My Scripts\3.txt",2,True)
Set objXl = CreateObject("Excel.Application")
Set objWrkBk = objXl.Workbooks.Open("E:\01 My Scripts\Exp.xlsx")
Set objWrkSht = objWrkBk.Worksheets("Sheet1")
iRowCnt = objWrkSht.UsedRange.Rows.Count
For iRowNum = 2 To iRowCnt
sName = objWrkSht.Cells(iRowNum,1)
iExperience = objWrkSht.Cells(iRowNum,2)
sIndustry = objWrkSht.Cells(iRowNum,3)
objPtr.WriteLine sName&" has "&iExperience&" Years of Experience in "&sIndustry&" industry”
Next
objWrkBk.Close
Set objWrkSht = Nothing
Set objWrkBk = Nothing
Set objXl = Nothing
Set objPtr = Nothing
Set objFile = Nothing
Set objFile = CreateObject("Scripting.FileSystemObject")
Set objPtr1 = objFile.OpenTextFile("E:\01 My Scripts\2.txt",1)
Set objPtr2 = objFile.OpenTextFile("E:\01 My Scripts\4.txt",2,True)
Set obj = Window("Flight Reservation")
While objPtr1.AtEndOfLine = "False"
sStr = objPtr1.ReadLine
sString = Split(sStr," ")
iOrderNo = sString(3)
iFaxNo = sString(8)
'MsgBox iOrderNo &" " &iFaxNo
obj.Activate
obj.WinMenu("Menu").Select "File;Open Order..."
obj.Dialog("Open Order").WinCheckBox("Order No.").Set "ON"
obj.Dialog("Open Order").WinEdit("Edit_2").Set iOrderNo
obj.Dialog("Open Order").WinButton("OK").Click
If obj.Dialog("Open Order").Dialog("Flight Reservations").Exist(4) Then
obj.Dialog("Open Order").Dialog("Flight Reservations").WinButton("OK").Click
obj.Dialog("Open Order").WinButton("Cancel").Click
objPtr2.WriteLine iOrderNo &"Order is not found and hence unable to send Fax"
Else
obj.Activate
obj.WinMenu("Menu").Select "File;Fax Order..."
obj.Dialog("Fax Order No. 1").WinObject("Fax Number:").Type iFaxNo
obj.Window("Fax Order No. 1").RunAnalog "Track1"
obj.Dialog("Fax Order No. 1").WinButton("Send").Click
If obj.Dialog("Fax Order No. 1").Dialog("Flight Reservations").Exist(4) Then
obj.Dialog("Fax Order No. 1").Dialog("Flight Reservations").WinButton("OK").Click
obj.Dialog("Fax Order No. 1").WinButton("Cancel").Click
objPtr2.WriteLine "Invalid Fax Number" &iFaxNo& "For the Order" &iOrderNo
Else
While obj.Dialog("Fax Order No. 1").Exist(2) = True
Wend
sFaxMsg = obj.WinObject("Fax Sent Successfully...").GetROProperty("Text")
If sFaxMsg = "Fax Sent Successfully..." Then
objPtr2.WriteLine "Fax Sent Success Fully for the order " &iOrderNo
Else
objPtr2.WriteLine "Fax Sent Success Fully for the order " &iOrderNo
End If
End If
End If
Wend
Note: - Generally we use Notepad for maintaining DATABASE queries
Note: - For general maintaining data like UserName, Password…etc we use excel files
Working with Ms-Word
Set objWrd = CreateObject(“Word.Application”)
Creating XML object
Set objXML = CreateObject(“XML.Application”)
Implementing parameterization by using Action Parameters
-->Actions are used to divide test script in to logically related groups of QTP statements.
-->Actions are used to script maintainability and script re-usability.
--> We use actions when functional testers maintain scenario based test scripts. That is one scenario containing
multiple test cases.
Ex: - Scenario
-->Every script we have “default action”, it is Action1
-->Every action of script will have sheet in data table and sheet name will be same as Action name.
Creating a New Action: -
Method-1:-
By Navigation: - Insert MenuCall to New ActionEnter Action NameClick OK Button
Method-2:-
Run Time: - Set objQtp = CreateObject(“QuickTest.Application”)
objQtp.Test.AddNewAction”
Content>”,”
Ex: - objQtp.Test.AddNewAction “sample”,”test”,”test”, 1, 1
ScriptNo26: - Write a script to create multiple Actions and prove that we can create only 255 actions
No comments :
Post a Comment