VBScript Excel Object Model

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
------------------------------------

0 comments:

Post a Comment