VBScript Tutorial 8
(Excel Object Model in VBScript)
Excel Application operations using Excel Application Object
Excel Application Object:
It is used to perform operations on Excel Application.
Create Excel Application Object:
Set Variable = CreateObject(“Excel.Application”)
———————-
Excel Application
Excel Workbook / File
Excel Worksheet / Sheet
Excel File Operations using VBScript Examples:
1) Create an Excel file
Dim objExcel
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True ‘To view the operation (Creating Excel file) during Execution.
objExcel.Workbooks.Add ‘Create New Workbook / file
objExcel.ActiveWorkbook.SaveAs “C:\Users\gcreddy\Desktop\QTP.xls” ‘Save the Excel workbook /file
objExcel.Quit ‘To close the Excel Application
Set objExcel = Nothing ‘To release the memory
—————————————————
2) Check the existence of QTP file, if not exist then create the file.Dim objFso, objExcel, FilePath
FilePath = “C:\Users\gcreddy\Desktop\QTP.xls”
Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objExcel = CreateObject(“Excel.Application”)
If Not objFso.FileExists(FilePath) Then
objExcel.Workbooks.Add ‘Create New Workbook / file
objExcel.ActiveWorkbook.SaveAs FilePath
objExcel.Quit
End If
Set objExcel = Nothing ‘To release the memory
—————————————————-
3) Check the existence of QTP file, if exists then open the file and enter some data, If not exist then create the file and enter some data (Using Excel Application Object only)
Dim objFso, objExcel, FilePath
FilePath = “C:\Users\gcreddy\Desktop\QTP.xlsx”
Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objExcel = CreateObject(“Excel.Application”)
If Not objFso.FileExists(FilePath) Then
objExcel.Workbooks.Add
objExcel.Worksheets(1).Cells(1, 1) = “Hello UFT”
objExcel.ActiveWorkbook.SaveAs FilePath
Else
objExcel.Workbooks.Open (FilePath)
objExcel.Worksheets(1).Cells(1, 1) = “Hello UFT”
objExcel.ActiveWorkbook.Save
End If
objExcel.Quit
Set objExcel = Nothing
Excel Objects
1) Excel Application Object
It is used to perform operations on Excel Application.
Set Variable = CreateObject(“Excel.Application”)
——————————–
2) Excel Workbook object
It is used to work with specified Excel file / Workbook
Set Variable = ExcelApplicationObject.Workbooks.Add / Open(“Filepath”)
——————————–
3) Excel Worksheet object
It is used to work with specified work sheet
Set Varaible = ExcelWorkbookObject.Worksheets(Sheet Id / “Sheet name”)
——————————————————-
Excel Application is always only one.
We may have one or more Workbooks.
We may have multiple sheets in every workbook.
———————————————
> Using (“Excel.Application”) class value we create Excel Application Object.
> We create Excel Workbook object using Excel Application Object.
> We create Excel Worksheet object using Excel workbook object.
————————————–
Difference between File system object model and Excel object model in case of Sub objects.
In File system object model creating Text stream object is mandatory to perform File internal operations like Read, Write, Compare, Search etc…
In Excel Object model creating sub and sub-sub objects optional, if you want to work with multiple files and multiple sheets then we can use sub and sub-sub objects.
———————————————————-
4) Check the existence of QTP file, if exists then open the file and enter some data, If not exist then create the file and enter some data (Using Main and sub objects)
Dim objFso, objExcel, objWorkbook, objWorksheet, FilePath
FilePath = “C:\Users\gcreddy\Desktop\QTP.xlsx”
Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objExcel = CreateObject(“Excel.Application”)
If Not objFso.FileExists(FilePath) Then
Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1, 1) = “Hello UFT”
objWorkbook.SaveAs FilePath
Else
Set objWorkbook = objExcel.Workbooks.Open (FilePath)
Set objWorksheet = objworkbook.Worksheets(1)
objWorksheet.Cells(1, 1) = “Hello UFT”
objWorkbook.Save
End If
objExcel.Quit
Set objExcel = Nothing
————————————————
5) Read data form Excel file and perform Data driven Testing for Login Functionality.
Dim objExcel, objWorkbook, objWorksheet, i, RowsCount
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\gcreddy\Desktop\QTP.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(1)
RowsCount = objWorksheet.UsedRange.Rows.Count
For i = 2 To RowsCount Step 1
SystemUtil.Run “C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe”
Dialog(“Login”).Activate
Dialog(“Login”).WinEdit(“Agent Name:”).Set objWorksheet.Cells(i, “A”) ‘i is Row, A is Column Name
Dialog(“Login”).WinEdit(“Password:”).Set objWorksheet.Cells(i, 2) ‘ 2 is Column id
wait 2
Dialog(“Login”).WinButton(“OK”).Click
Window(“Flight Reservation”).Close
Next
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
————————————————
6) Read data form Excel file and perform Data driven Testing for Login Functionality. And write Test Result to the Same file 3rd column.
Dim objExcel, objWorkbook, objWorksheet, i, RowsCount
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\gcreddy\Desktop\QTP.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(1)
RowsCount = objWorksheet.UsedRange.Rows.Count
For i = 2 To RowsCount Step 1
SystemUtil.Run “C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe”
Dialog(“Login”).Activate
Dialog(“Login”).WinEdit(“Agent Name:”).Set objWorksheet.Cells(i, “A”) ‘i is Row, A is Column Name
Dialog(“Login”).WinEdit(“Password:”).Set objWorksheet.Cells(i, 2) ‘ 2 is Column id
wait 2
Dialog(“Login”).WinButton(“OK”).Click
If Window(“Flight Reservation”).Exist (12) Then
Window(“Flight Reservation”).Close
objWorksheet.Cells(i, 3) = “Login Successful – Passed”
Else
SystemUtil.CloseDescendentProcesses
objWorksheet.Cells(i, 3) = “Login Unsuccessful – Failed”
End If
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
————————————————
7) Read data form Excel file and perform Data driven Testing for Login Functionality. And write Test Result and Error messages to the same file.Dim objExcel, objWorkbook, objWorksheet, i, RowsCount
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\gcreddy\Desktop\QTP.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(1)
RowsCount = objWorksheet.UsedRange.Rows.Count
For i = 2 To RowsCount Step 1
SystemUtil.Run “C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe”
Dialog(“Login”).Activate
Dialog(“Login”).WinEdit(“Agent Name:”).Set objWorksheet.Cells(i, “A”) ‘i is Row, A is Column Name
Dialog(“Login”).WinEdit(“Password:”).Set objWorksheet.Cells(i, 2) ‘ 2 is Column id
wait 2
Dialog(“Login”).WinButton(“OK”).Click
If Window(“Flight Reservation”).Exist (12) Then
Window(“Flight Reservation”).Close
objWorksheet.Cells(i, 3) = “Login Successful – Passed”
Else
objWorksheet.Cells(i, 4) = Dialog(“Login”).Dialog(“Flight Reservations”).Static(“Agent name must be at”).GetROProperty(“text”)
SystemUtil.CloseDescendentProcesses
objWorksheet.Cells(i, 3) = “Login Unsuccessful – Failed”
End If
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
——————————————–
8) Read Button Names from Login Dialog and export to Excel file 2nd sheet.Dim objExcel, objWorkbook, objWorksheet, oButton, Buttons, i
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook= objExcel.Workbooks.Open (“C:\Users\gcreddy\Desktop\QTP.xlsx”)
Set objworksheet = objWorkbook.Worksheets(2)
Set oButton = Description.Create
oButton(“Class Name”).Value = “WinButton”
Set Buttons = Dialog(“Login”).ChildObjects(oButton)
Msgbox Buttons.Count
objWorksheet.cells(1, 1) = “Button Names”
For i = 0 To Buttons.Count – 1 Step 1
objWorksheet.cells(i+2, 1) = Buttons(i).GetRoProperty(“text”)
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objexcel = Nothing
—————————————————
9) Read Link names from Google Home page and export to Excel file 3rd sheet.Dim objExcel, objWorkbook, objWorksheet, oLink, Links, i
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook= objExcel.Workbooks.Open (“C:\Users\gcreddy\Desktop\QTP.xlsx”)
Set objworksheet = objWorkbook.Worksheets(3)
Set oLink = Description.Create
oLink(“micclass”).Value = “Link”
Set Links = Browser(“Google”).Page(“Google”).ChildObjects(oLink)
Msgbox Links.Count
objWorksheet.cells(1, 1) = “Link Names”
For i = 0 To Links.Count – 1 Step 1
objWorksheet.cells(i+2, 1) = Links(i).GetRoProperty(“text”)
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objexcel = Nothing
—————————————————
10) Read Customer names from 1 to 10 Records and export to Excel
Dim objExcel, objWorkbook, objWorksheet, Customer_Name, i
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook= objExcel.Workbooks.Open (“C:\Users\gcreddy\Desktop\QTP.xlsx”)
Set objworksheet = objWorkbook.Worksheets(2)
objWorksheet.cells(1, 1) = “Customer Names”
For i = 1 To 10 Step 1
Window(“Flight Reservation”).Activate
Window(“Flight Reservation”).WinButton(“Button”).Click
Window(“Flight Reservation”).Dialog(“Open Order”).WinCheckBox(“Order No.”).Set “ON”
Window(“Flight Reservation”).Dialog(“Open Order”).WinEdit(“Edit”).Set i
Window(“Flight Reservation”).Dialog(“Open Order”).WinButton(“OK”).Click
Customer_Name = Window(“Flight Reservation”).WinEdit(“Name:”).GetROProperty(“text”)
objWorksheet.Cells(i+1, 1) = Customer_Name
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objexcel = Nothing
————————————————–
11) Create an Excel File and Rename 1st sheet as “Module”, 2nd sheet as “TestCase”, and 3rd sheet as “TestStep”.
Dim objExcel
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Workbooks.Add
objExcel.Worksheets(1).Name = “Module”
objExcel.Worksheets(2).Name = “TestCase”
objExcel.Worksheets(3).Name = “TestStep”
objExcel.ActiveWorkbook.SaveAs “C:\Users\gcreddy\Desktop\QTP2.xlsx”
objExcel.Quit
Set objExcel = Nothing
12) Create an Excel file and Add one more sheet.Dim objExcel
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Workbooks.Add ‘Create New workbook
objexcel.Worksheets.Add ‘Create New worksheet
objExcel.ActiveWorkbook.SaveAs “C:\Users\gcreddy\Desktop\QTP3.xlsx”
objExcel.Quit
Set objExcel = Nothing
———————————————
Assignment:
Create an Excel file and Move 1st sheet to 3rd position.
Creation Time:
Sheet1 Sheet2 sheet3
Move 1st sheet to 3rd position
Sheet2 Sheet3 Sheet1
————————————————–
Comparison examples:
i) One to one comparison (Textual and binary)
ii) Many to many comparison
————————————————-
