EXCEL FILE OPERATIONS

                      

Excel File Operations

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