Drives, files and folders are the lifeblood of any organization; this makes file
system administration one of the most important responsibilities assigned to
system administrators. Of course, file system administration is also one of the
More difficult responsibilities to carry out, simply because files and folders are
Scattered on multiple hard disks and multiple computers throughout the
Organization. Scripts can help make file system management much easier,
Particularly when the files and folders managed, are located on remote computers.
1) Creating Excel Application Object
Syntax: Set ObjVar = CreateObject("Excel.Application")'Create
Examples: Excel ApplicationObject
Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
By running the preceding script, you really did create a brand-new instance of Microsoft Excel. Press CTRL-ALT-DEL and take a look at the Processes tab in the Task Manager. You should see an instance of Excel.exe
Examples:
-----------------------------------------------------------------------------------------
1) 'Create Excel file /Work book
Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True 'To make the excel visible
objExcel.Workbooks.Add ‘Adding a work book
objExcel.ActiveWorkbook.SaveAs "D:\ALOK\alok.xls"
objExcel.Quit
Set objExcel=Nothing ‘Releasing memory
-------------------------------------------------------------------------------------------------------
2)‘Checking for the existence of the file and entering some data into it
' If Not exists Create the Excel file /Work book and enter some data
Dim objExcel, objFso, FilePath
FilePath="D:\Alok\alok.xls"
Set objFso=CreateObject("Scripting.FileSystemObject")
Set objExcel=CreateObject("Excel.Application")
If objFso.FileExists(FilePath) Then
objExcel.Workbooks.Open (FilePath)
objExcel.Worksheets("Sheet1").Cells(1,1)="VB Script"
objExcel.ActiveWorkbook.Save
Else
objExcel.Workbooks.Add
objExcel.ActiveSheet.Cells(2,2)="VB Script"
objExcel.ActiveWorkbook.SaveAs (Filepath)
End If
objExcel.Quit 'To Quit the Excel Appliction
Set objExcel=Nothing
------------------------------------------------------------------------------------------------
3) 'Fetch Test Data directly from an Excel file and perform Data driven testing for Login Operation
Dim objExcel, objWorkbook, objWorksheet
'Create Excel application Object that can be used to perform operations on Excel Appliction
Set objExcel=CreateObject("Excel.Application")
'Create WorkBook Object using Excel application Object that can be used to perform operations on Excel Work Books
Set objWorkbook=objExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\input.xls")
'Create Work sheet object Using Work Book Object, that can be used to perform operations on Excel Sheets
Set objWorksheet=objWorkbook.Worksheets("Sheet1")
Rows_Count=objWorksheet.usedrange.rows.count
For i= 2 to Rows_Count Step 1
SystemUtil.Run "C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight4a.exe","","C:\Program Files\HP\QuickTest Professional\samples\flight\app\","open"
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set objWorksheet.Cells(i,"A")
Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i,"B")
Wait 1
Dialog("Login").WinButton("OK").Click
Window("Flight Reservation").Close
Next
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
-------------------------------------------------------------------------
4) 'Fetch Test Data directly from an Excel file and perform Data driven testing for Login Operation
'Export Test Results to the same file
Dim objExcel, objWorkbook, objWorksheet
'Create Excel application Object that can be used to perform operations on Excel Appliction
Set objExcel=CreateObject("Excel.Application")
'Create WorkBook Object using Excel application Object that can be used to perform operations on Excel Work Books
Set objWorkbook=objExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\input.xls")
'Create Work sheet object Using Work Book Object , that can be used to perform operations on Excel Sheets
Set objWorksheet=objWorkbook.Worksheets("Sheet1")
objWorksheet.Cells(1,3)="Results"
Rows_Count=objWorksheet.usedrange.rows.count
For i= 2 to Rows_Count Step 1
SystemUtil.Run "C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight4a.exe","","C:\Program Files\HP\QuickTest Professional\samples\flight\app\","open"
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set objWorksheet.Cells(i,"A")
Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i,"B")
Wait 1
Dialog("Login").WinButton("OK").Click
If Window("Flight Reservation").Exist(12) Then
Window("Flight Reservation").Close
objWorksheet.Cells(i,"C")="Login Successful"
Else
SystemUtil.CloseDescendentProcesses
objWorksheet.Cells(i,"C")="Login Filed"
End If
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
5) 'Fetch Test Data directly from an Excel file and perform Data driven testing for Login Operation
'Export Test Results & Error Messgae to the same file
Dim objExcel, objWorkbook, objWorksheet, rows_Count
Set objExcel=CreateObject("Excel.Application")
Set objWorkbook=objExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\input.xls")
Set objWorksheet=objWorkbook.Worksheets(1)
objWorksheet.Cells(1,3)="Test Result"
objWorksheet.Cells(1,4)="Error Message"
rows_Count=objWorksheet.usedrange.rows.count
For i= 2 to rows_Count Step 1
SystemUtil.Run "C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight4a.exe","","C:\Program Files\HP\QuickTest Professional\samples\flight\app\","open"
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set objWorksheet.Cells(i, 1)
Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i, "B")
Dialog("Login").WinButton("OK").Click
If Window("Flight Reservation").Exist(12) Then
Window("Flight Reservation").Close
objWorksheet.Cells(i, 3)="Login Successful"
Else
objWorksheet.Cells(i, 3)="Login Failed"
objWorksheet.Cells(i, 4)=Dialog("Login").Dialog("Flight Reservations").Static("Agent name must be at").GetROProperty ("text")
SystemUtil.CloseDescendentProcesses
End If
Next
objWorkbook.Save
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
6)
Using While...Wend Loop
------------------------
Dim objExcel, objWorkbook, objWorksheet, rows_Count, i
Set objExcel=CreateObject("Excel.Application")
Set objWorkbook=objExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\input.xls")
Set objWorksheet=objWorkbook.Worksheets(1)
objWorksheet.Cells(1,3)="Test Result"
objWorksheet.Cells(1,4)="Error Message"
rows_Count=objWorksheet.usedrange.rows.count
i= 2
While i<= rows_Count
SystemUtil.Run "C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight4a.exe","","C:\Program Files\HP\QuickTest Professional\samples\flight\app\","open"
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set objWorksheet.Cells(i, 1)
Dialog("Login").WinEdit("Password:").Set objWorksheet.Cells(i, "B")
Dialog("Login").WinButton("OK").Click
If Window("Flight Reservation").Exist(12) Then
Window("Flight Reservation").Close
objWorksheet.Cells(i, 3)="Login Successful"
Else
objWorksheet.Cells(i, 3)="Login Failed"
objWorksheet.Cells(i, 4)=Dialog("Login").Dialog("FlightReservations").Static("Agent name must be at").GetROProperty ("text")
SystemUtil.CloseDescendentProcesses
End If
i=i+1
Wend
objWorkbook.Save
objExcel.Quit
Set objWorksheet=Nothing
Set objWorkbook=Nothing
Set objExcel=Nothing
7) 'Capture Link names from Google home page and export to Excel file3rd sheet
Dim ObjExcel,ObjWorkbook,ObjWorksheet
Dim oLink,Links,myLink,i
Set ObjExcel=CreateObject("Excel.Application")
Set ObjWorkbook=ObjExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls")
Set ObjWorksheet=ObjWorkbook.Worksheets(3)
ObjWorksheet.Cells(1,1)="Link Names"
Set oLink=Description.Create
oLink("micclass").value="Link"
Set Links=Browser("title:=Google").Page("title:=Google").ChildObjects(oLink)
For i=0 to Links.Count-1 step 1
myLink=Links(i).GetRoProperty("text")
ObjWorksheet.Cells(i+2,1)=myLink
Next
ObjWorkbook.Save
ObjExcel.Quit
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set ObjExcel=Nothing
----------------------------------------------------------------------------------
8) 'Capture Button names from Login Dialog (Flight Reservation Application) and export to Excel file 3rd sheet
Dim ObjExcel,ObjWorkbook,ObjWorksheet
Dim oButton,Buttons,myButton,i
Set ObjExcel=CreateObject("Excel.Application")
Set ObjWorkbook=ObjExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls")
Set ObjWorksheet=ObjWorkbook.Worksheets(2)
ObjWorksheet.Cells(1,1)="Button Names"
Set oButton=Description.Create
oButton("Class Name").value="WinButton"
Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
For i=0 to Buttons.Count-1 step 1
myButton=Buttons(i).GetRoProperty("text")
ObjWorksheet.Cells(i+2,1)=myButton
Next
ObjWorkbook.Save
ObjExcel.Quit
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set ObjExcel=Nothing
-----------------------------------------------------------------------------------
9) ' Read/capture order numbers and customer names from 1 - 10 orders in Flight Reservation window
' and export to excel file 2nd sheet
Dim objExcel, objWorkBook, objWorkSheet, ord, C_Name
Set objExcel = createobject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Open("C:\Documents and Settings\akm\Desktop\Sample.xls")
Set objWorkSheet = objWorkBook.Worksheets(2)
objWorkSheet.cells(1,1) = "Order No."
objWorkSheet.cells(1,2) = "C-Name"
For ord= 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 ord
Window("Flight Reservation").Dialog("Open Order").WinButton("OK").Click
Wait 1
C_Name = Window("Flight Reservation").WinEdit("Name:").GetROProperty("text")
objWorkSheet.cells(ord+1,1) = ord
objWorkSheet.cells(ord+1,2) =C_Name
Next
objWorkBook.Save
objExcel.Quit
Set objWorkSheet=Nothing
Set objWorkBook=Nothing
Set objExcel=Nothing
10) One to One Comparison and Exact match
----------------------------------------
'Capture Button names from Login Dialog (Flight Reservation Application) and Perform One to One Comparison and Exact match
Dim ObjExcel,ObjWorkbook,ObjWorksheet
Dim oButton,Buttons,myButton,i
Set ObjExcel=CreateObject("Excel.Application")
Set ObjWorkbook=ObjExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls")
Set ObjWorksheet=ObjWorkbook.Worksheets(2)
ObjWorksheet.Cells(1,2)="Buttons"
Set oButton=Description.Create
oButton("Class Name").value="WinButton"
Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
For i=0 to Buttons.Count-1 step 1
myButton=Buttons(i).GetRoProperty("text")
ObjWorksheet.Cells(i+2, 2)=myButton
Next
rows_Count= ObjWorksheet.usedrange.rows.count
For j= 2 to rows_Count step 1
Expected=ObjWorksheet.Cells(j, 1)
Actual=ObjWorksheet.Cells(j, 2)
If Expected=Actual Then
ObjWorksheet.Cells(j, 3)="Pass"
Else
ObjWorksheet.Cells(j, 3)="Fail"
End If
Next
ObjWorkbook.Save
ObjExcel.Quit
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set ObjExcel=Nothing
11) One to One Textual Comparison
------------------------------
'Capture Button names from Login Dialog (Flight Reservation Application) and Perform and Perform One to One Textual Comparison
Dim ObjExcel,ObjWorkbook,ObjWorksheet
Dim oButton,Buttons,myButton,i
Set ObjExcel=CreateObject("Excel.Application")
Set ObjWorkbook=ObjExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls")
Set ObjWorksheet=ObjWorkbook.Worksheets(2)
ObjWorksheet.Cells(1,2)="Buttons"
Set oButton=Description.Create
oButton("Class Name").value="WinButton"
Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
For i=0 to Buttons.Count-1 step 1
myButton=Buttons(i).GetRoProperty("text")
ObjWorksheet.Cells(i+2, 2)=myButton
Next
rows_Count= ObjWorksheet.usedrange.rows.count
For j= 2 to rows_Count step 1
Expected=ObjWorksheet.Cells(j, 1)
Actual=ObjWorksheet.Cells(j, 2)
If StrComp (Expected,Actual,1)=0 Then
ObjWorksheet.Cells(j, 3)="Pass"
Else
ObjWorksheet.Cells(j, 3)="Fail"
End If
Next
ObjWorkbook.Save
ObjExcel.Quit
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set ObjExcel=Nothing
--------------------------------------------------------------------------
12) Many to Many Comparison
-----------------------------------
'Capture Button names from Login Dialog (Flight Reservation Application) and Perform and Perform Many to Many Comparison
Dim ObjExcel,ObjWorkbook,ObjWorksheet
Dim oButton,Buttons,myButton,i
Set ObjExcel=CreateObject("Excel.Application")
Set ObjWorkbook=ObjExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls")
Set ObjWorksheet=ObjWorkbook.Worksheets(2)
ObjWorksheet.Cells(1,2)="Buttons"
Set oButton=Description.Create
oButton("Class Name").value="WinButton"
Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
For i=0 to Buttons.Count-1 step 1
myButton=Buttons(i).GetRoProperty("text")
ObjWorksheet.Cells(i+2, 2)=myButton
Next
rows_Count= ObjWorksheet.usedrange.rows.count
For j= 2 to rows_Count step 1
Expected=ObjWorksheet.Cells(j, 1)
For k=2 to rows_Count step 1
Actual=ObjWorksheet.Cells(k, 2)
If Expected=Actual Then
Flag =1
Exit for
else
Flag= 0
End If
next
If Flag=1 Then
ObjWorksheet.Cells(j, 3)="Pass"
Else
ObjWorksheet.Cells(j, 3)="Fail"
End If
Next
ObjWorkbook.Save
ObjExcel.Quit
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set ObjExcel=Nothing
-------------------------------------------------------------------
13) Many to Many Textual Comparison
'Capture Button names from Login Dialog (Flight Reservation Application) and Perform and Perform Many to Many Textual Comparison
-----------------------------------
'Capture Button names from Google home page and export to Excel file 3rd sheet
Dim ObjExcel,ObjWorkbook,ObjWorksheet
Dim oButton,Buttons,myButton,i
Set ObjExcel=CreateObject("Excel.Application")
Set ObjWorkbook=ObjExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls")
Set ObjWorksheet=ObjWorkbook.Worksheets(2)
ObjWorksheet.Cells(1,2)="Buttons"
Set oButton=Description.Create
oButton("Class Name").value="WinButton"
Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
For i=0 to Buttons.Count-1 step 1
myButton=Buttons(i).GetRoProperty("text")
ObjWorksheet.Cells(i+2, 2)=myButton
Next
rows_Count= ObjWorksheet.usedrange.rows.count
For j= 2 to rows_Count step 1
Expected=ObjWorksheet.Cells(j, 1)
For k=2 to rows_Count step 1
Actual=ObjWorksheet.Cells(k, 2)
If StrComp (Expected,Actual,1)= 0 Then
Flag =1
Exit for
else
Flag= 0
End If
next
If Flag=1 Then
ObjWorksheet.Cells(j, 3)="Pass"
Else
ObjWorksheet.Cells(j, 3)="Fail"
End If
Next
ObjWorkbook.Save
ObjExcel.Quit
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set ObjExcel=Nothing
---------------------------------------------------------------------------------------
14) 'Create Excel file and Rename 1st sheet as "Module", 2nd Sheet as "Test Case", 'and 3rd Sheet as "Test Step"
Dim objExcel
Set objExcel=CreateObject("Excel.Application")
objExcel.Visible=True
objExcel.Workbooks.Add
objExcel.Worksheets("Sheet1").Name="Module"
Wait 4
objExcel.Worksheets("Sheet2").Name="TestCase"
Wait 4
objExcel.Worksheets("Sheet3").Name="TestStep"
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\abcd.xls"
objExcel.Quit
Set objExcel=Nothing
-------------------------------------------------------------------------
15) 'Create an Excel file and add one more
Dim objExcel
Set objExcel=CreateObject("Excel.Application")
objExcel.Visible=True
objExcel.Workbooks.Add 'Creating Work Book
objExcel.Worksheets.Add 'Creating Work Sheet
Wait 4
objExcel.ActiveWorkbook.SaveAs "C:\Documents and Settings\Administrator\Desktop\abcde.xls"
objExcel.Quit
Set objExcel=Nothing
--------------------------------------------------------------------------------------
16) 'Capture Button names from Login Dialog (Flight Reservation Application) and perform Many to Many Complete Comparison
Capture Button names from Google home page and export to Excel file 3rd sheet
Dim ObjExcel,ObjWorkbook,ObjWorksheet
Dim oButton,Buttons,myButton,i
Set ObjExcel=CreateObject("Excel.Application")
Set ObjWorkbook=ObjExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\input.xls")
Set ObjWorksheet=ObjWorkbook.Worksheets(2)
ObjWorksheet.Cells(1,2)="Buttons"
Set oButton=Description.Create
oButton("Class Name").value="WinButton"
Set Buttons=Dialog("text:=Login").ChildObjects(oButton)
For i=0 to Buttons.Count-1 step 1
myButton=Buttons(i).GetRoProperty("text")
ObjWorksheet.Cells(i+2, 2)=myButton
Next
rows_Count= ObjWorksheet.usedrange.rows.count
x =0
For j= 2 to rows_Count step 1
Expected=ObjWorksheet.Cells(j, 1)
flag = 0
For k=2 to rows_Count step 1
Actual=ObjWorksheet.Cells(k, 2)
If StrComp (Expected,Actual,1)= 0 Then
Flag =1
End If
x=x+1 ' increment the comparison count
next
If Flag=1 Then
ObjWorksheet.Cells(j, 3)="Pass"
Else
ObjWorksheet.Cells(j, 3)="Fail"
End If
msgbox x 'inner loop comparison values
Next
msgbox x ' Total number of comparisons
ObjWorkbook.Save
ObjExcel.Quit
Set ObjWorksheet=Nothing
Set ObjWorkbook=Nothing
Set ObjExcel=Nothing
