Pages

Custom Search

Excel Scripts


Excel Scripts

1) Create an Excel file, enter some data and save the file through VB scripting?

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 Settings\admin\My Documents\gcreddy.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")
8) SystemUtil.Run "C:\Program Files\Mercury Interactive\QuickTest Professional\samples\flight\app\flight4a.exe","","C:\Program Files\Mercury Interactive\QuickTest Professional\samples\flight\app\","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 Settings\Administrator\Desktop\gcreddy.xls",1,3
n=datatable.GetSheet (3).GetRowCount
For i= 1 to n
Datatable.SetCurrentRow(i)
Invokeapplication "C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight4a.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 Settings\Administrator\Desktop\gcreddy.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:\Scripts\gcreddy.xls")

6) Read an Excel Spreadsheet


Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
("C:\Scripts\New_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

8) 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:\Scripts\Sort_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

2 comments:

Anonymous said...

If you depend upon a social security deposit to pay for childcare and housekeeping in your
absence. Dr It's almost looked at by insurance companies prove to be saviour for you.
Negligence, omission of reasonable care of vehicle, maintenance of the same people become lost as to what losses are not covered by the abc insurance industry.

Policies may provide the replacement cost, or pay for abc
restoration.

My web-site - zawodowe OC; ,

Anonymous said...

I'm not sure where you are getting your info, but good topic.
I needs to spend some time learning more or understanding more.

Thanks for wonderful information I was looking for this info for my mission.

Also visit my web-site; Ebon Talifarro