VBScript Excel Object Model
It is used to Perform Operations on Excel Application.
Excel Application
Excel File / Excel Workbook
Excel Sheet / Excel Worksheet
Create Excel Application Object
Syntax:
Set variable = CreateObject(“Class value”)
example
Set objExcel = CreateObject(“Excel.Application”)
VBScript Excel Scripting Examples:
1) Create an Excel file
Dim objExcel
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True ‘To view the Operation during execution
objExcel.Workbooks.Add ‘To create new file
objExcel.ActiveWorkbook.SaveAs “C:\Users\G C REDDY\Desktop\January.xlsx”
objExcel.Quit ‘To close the Excel Application
Set objExcel = Nothing
2) Check the existence of January file, If not exists then create the file.
Dim objFso, objExcel
Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objExcel = CreateObject(“Excel.Application”)
If Not objFso.FileExists(“C:\Users\G C REDDY\Desktop\January.xlsx”) Then
objExcel.Workbooks.Add ‘To create new file
objExcel.ActiveWorkbook.SaveAs “C:\Users\G C REDDY\Desktop\January.xlsx”
End If
objExcel.Quit
Set objExcel = Nothing
3) Check the existence of January file, If exists then open the file and enter some data. if not exists then create the file and enter some data.
Dim objFso, objExcel, FilePath
FilePath = “C:\Users\G C REDDY\Desktop\January.xlsx”
Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objExcel = CreateObject(“Excel.Application”)
If objFso.FileExists(FilePath) Then
objExcel.Workbooks.Open(FilePath)
objExcel.Worksheets(1).Cells(1,1) = “Hello UFT”
objExcel.ActiveWorkbook.Save
Else
objExcel.Workbooks.Add
objExcel.Worksheets(1).Cells(1,1) = “Hello UFT”
objExcel.ActiveWorkbook.SaveAs(FilePath)
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 Excel files/Workbooks
Set variable = ExcelApplicationObject.Workbooks.Add/Open(“File Path”)
3) Excel Worksheet Object
It is used to work with Excel Sheets/Worksheets
Set variable = ExcelWorkbookObject.Worksheets(Sheet Id Or “Sheet Name”)
Excel Application Object is always only one
We can create one or more Excel Workbook objects
We can create one or more Excel Worksheet objects for every workbook object
Difference between FileSystemObject model and Excel object model in case of Sub Objects.
> In FileSystemObject model creating Text stream object (sub object) is mandatory to perform Text(Read, write etc…) related operations
> In Excel object model creating sub objects is optional, but if you want work with multiple files and multiple sheets then sub objects are required.
4) Check the existence of January file, If exists then open the file and enter some data. if not exists then create the file and enter some data.(Using Sub and Sub-sub objects)
Dim objFso, objExcel, FilePath, objWorkbook, objWorksheet
FilePath = “C:\Users\G C REDDY\Desktop\January.xlsx”
Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objExcel = CreateObject(“Excel.Application”)
If objFso.FileExists(FilePath) Then
Set objWorkbook = objExcel.Workbooks.Open(FilePath)
Set objworksheet = objWorkbook.Worksheets(1)
objWorksheet.cells(1, 1) =”Hello UFT”
objWorkbook.Save
Else
Set objWorkbook = objExcel.Workbooks.Add
Set objworksheet = objWorkbook.Worksheets(1)
objWorksheet.cells(1, 1) =”Hello UFT”
objWorkbook.SaveAs(FilePath)
End If
objExcel.Quit
Set objWorksheet = Nothing
Set objworkbook = Nothing
Set objExcel = Nothing
5) Read Test data from an Excel file and perform Data driven testing for Login Functionality
Dim objExcel, objWorkbook, objWorksheet, RowsCount
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Users\G C REDDY\Desktop\January.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 for Row, A for Column
Dialog(“Login”).WinEdit(“Password:”).Set objWorksheet.Cells(i, 2)’i for Row, 2 for Column
Wait 2
Dialog(“Login”).WinButton(“OK”).Click
Window(“Flight Reservation”).Close
Next
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
