Excel Scripts

Excel Scripts Examples

1) Create an Excel file, enter some data and save the file using Excel object
1) Dim objexcel
2) Set objExcel = createobject(“Excel.application”)
3) objexcel.Visible = True
4) objexcel.Workbooks.add
5) objexcel.Cells(1, 1).Value = “Testing”
6) objexcel.ActiveWorkbook.SaveAs(“f:exceltest.xls”)
7) objexcel.Quit
2) Data Driven Testing through an External Excel Sheet
1) Set myExcel=Createobject(“Excel.Application”)
2) Set myFile=myExcel.workbooks.open (“C:Documents and SettingsadminMy Documentsgcreddy.xls”)
3) Set mySheet=myFile.worksheets(“Sheet1″)
4) Rows_Count=mySheet.usedrange.rows.count
5) For i= 1 to Rows_Count
6) Agent=mySheet.cells(i,”A”)
7) pwd=mySheet.Cells(i,”B”)
SystemUtil.Run “C:Program FilesMercury InteractiveQuickTest Professionalsamplesflightappflight4a.exe”,”",”C:Program FilesMercury InteractiveQuickTest Professionalsamplesflightapp”,”open”
9) Dialog(“Login”).Activate
10) Dialog(“Login”).WinEdit(“Agent Name:”).Set Agent
11) Dialog(“Login”).WinEdit(“Password:”).SetSecure pwd
12) Dialog(“Login”).WinEdit(“Password:”).Type micReturn
13) Window(“Flight Reservation”).Close
14) Next
3) Compare two excel files
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“E:gcreddy1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“E:gcreddy2.xls”)
Set objWorksheet1= objWorkbook1.Worksheets(1)
Set objWorksheet2= objWorkbook2.Worksheets(1)
For Each cell In objWorksheet1.UsedRange
If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
msgbox “value is different”
Else
msgbox “value is same”
End If
Next
objWorkbook1.close
objWorkbook2.close
objExcel.quit
set objExcel=nothing
4) Data Driven Testing using Data Table methods
Datatable.AddSheet “gcreddy”
Datatable.ImportSheet “C:Documents and SettingsAdministratorDesktopgcreddy.xls”,1,3
n=datatable.GetSheet (3).GetRowCount
For i= 1 to n
Datatable.SetCurrentRow(i)
Invokeapplication “C:Program FilesHPQuickTest Professionalsamplesflightappflight4a.exe”
Dialog(“Login”).Activate
Dialog(“Login”).WinEdit(“Agent Name:”).Set datatable(“agent”,3)
Dialog(“Login”).WinEdit(“Password:”).Set datatable(“pwd”,3)
Dialog(“Login”).WinButton(“OK”).Click
Window(“Flight Reservation”).Close
Next
Example 2):
Datatable.AddSheet “gcreddy”
Datatable.ImportSheet “C:Documents and SettingsAdministratorDesktopgcreddy.xls”,1,3
n=datatable.GetSheet (3).GetRowCount
For i= 1 to n
Datatable.SetCurrentRow(i)
VbWindow(“Form1″).Activate
VbWindow(“Form1″).VbEdit(“val1″).Set datatable(“V1″,3)
VbWindow(“Form1″).VbEdit(“val2″).Set datatable(“V2″,3)
VbWindow(“Form1″).VbButton(“ADD”).Click
eres= Datatable.Value (“res”,3)
ares=VbWindow(“Form1″).VbEdit(“res”).GetROProperty (“text”)
If eres=ares Then
datatable(“res”,3)=pass
else
datatable(“res”,3)=fail
End If
Next
5) Open an Excel Spreadsheet
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:Scriptsgcreddy.xls”)
6) Read an Excel Spreadsheet

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open _
(“C:ScriptsNew_users.xls”)
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = “”
Wscript.Echo “CN: ” & objExcel.Cells(intRow, 1).Value
Wscript.Echo “sAMAccountName: ” & objExcel.Cells(intRow, 2).Value
Wscript.Echo “GivenName: ” & objExcel.Cells(intRow, 3).Value
Wscript.Echo “LastName: ” & objExcel.Cells(intRow, 4).Value
intRow = intRow + 1
Loop
objExcel.Quit
7) Add Formatted Data to a Spreadsheet
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = “Test value”
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Font.Size = 24
objExcel.Cells(1, 1).Font.ColorIndex = 3
Sort an Excel Spreadsheet on Three Different Columns
Const xlAscending = 1
Const xlDescending = 2
Const xlYes = 1
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = _
objExcel.Workbooks.Open(“C:ScriptsSort_test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range(“A1″)
Set objRange3 = objExcel.Range(“B1″)
Set objRange4 = objExcel.Range(“C1″)
objRange.Sort objRange2,xlAscending,objRange3,,xlDescending, _
objRange4,xlDescending,xlYes