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 sentiment the Operation during execution
objExcel.Workbooks.Add 'To practise novel file
objExcel.ActiveWorkbook.SaveAs "C:\Users\G C REDDY\Desktop\January.xlsx"
objExcel.Quit 'To closed the Excel Application
Set objExcel = Nothing
-------------------------------------
2) Check the being of Jan file, If non exists thence practise 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 practise novel file
objExcel.ActiveWorkbook.SaveAs "C:\Users\G C REDDY\Desktop\January.xlsx"
End If
objExcel.Quit
Set objExcel = Nothing
------------------------------------------------------
3) Check the being of Jan file, If exists thence opened upward the file together with last into about data. if non exists thence practise the file together with last into about 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 operate alongside Excel files/Workbooks
Set variable = ExcelApplicationObject.Workbooks.Add/Open("File Path")
---------------------------------------------
3) Excel Worksheet Object
It is used to operate alongside Excel Sheets/Worksheets
Set variable = ExcelWorkbookObject.Worksheets(Sheet Id Or "Sheet Name")
---------------------------------------------
Excel Application Object is e'er solely one
We tin forcefulness out practise i or to a greater extent than Excel Workbook objects
We tin forcefulness out practise i or to a greater extent than Excel Worksheet objects for every workbook object
-------------------------------------------------------
Difference betwixt FileSystemObject model together with Excel object model inwards representative of Sub Objects.
> In FileSystemObject model creating Text flow object (sub object) is mandatory to perform Text(Read, write etc...) related operations
> In Excel object model creating sub objects is optional, only if you lot desire operate alongside multiple files together with multiple sheets thence sub objects are required.
------------------------------------------------------------
4) Check the being of Jan file, If exists thence opened upward the file together with last into about data. if non exists thence practise the file together with last into about data.(Using Sub together with 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 information from an Excel file together with 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 = ii 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, Influenza A virus subtype H5N1 for Column
Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i, 2)'i for Row, ii 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
------------------------------------
Sumber http://www.gcreddy.com/
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 sentiment the Operation during execution
objExcel.Workbooks.Add 'To practise novel file
objExcel.ActiveWorkbook.SaveAs "C:\Users\G C REDDY\Desktop\January.xlsx"
objExcel.Quit 'To closed the Excel Application
Set objExcel = Nothing
-------------------------------------
2) Check the being of Jan file, If non exists thence practise 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 practise novel file
objExcel.ActiveWorkbook.SaveAs "C:\Users\G C REDDY\Desktop\January.xlsx"
End If
objExcel.Quit
Set objExcel = Nothing
------------------------------------------------------
3) Check the being of Jan file, If exists thence opened upward the file together with last into about data. if non exists thence practise the file together with last into about 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 operate alongside Excel files/Workbooks
Set variable = ExcelApplicationObject.Workbooks.Add/Open("File Path")
---------------------------------------------
3) Excel Worksheet Object
It is used to operate alongside Excel Sheets/Worksheets
Set variable = ExcelWorkbookObject.Worksheets(Sheet Id Or "Sheet Name")
---------------------------------------------
Excel Application Object is e'er solely one
We tin forcefulness out practise i or to a greater extent than Excel Workbook objects
We tin forcefulness out practise i or to a greater extent than Excel Worksheet objects for every workbook object
-------------------------------------------------------
Difference betwixt FileSystemObject model together with Excel object model inwards representative of Sub Objects.
> In FileSystemObject model creating Text flow object (sub object) is mandatory to perform Text(Read, write etc...) related operations
> In Excel object model creating sub objects is optional, only if you lot desire operate alongside multiple files together with multiple sheets thence sub objects are required.
------------------------------------------------------------
4) Check the being of Jan file, If exists thence opened upward the file together with last into about data. if non exists thence practise the file together with last into about data.(Using Sub together with 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 information from an Excel file together with 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 = ii 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, Influenza A virus subtype H5N1 for Column
Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i, 2)'i for Row, ii 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
------------------------------------