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

QTP Scripting

Tags: DataTable.SetCurrentRow, Excel File Operations, excel object, excel object model, Excel Scripts, excel scripts example, excel scripts examples, objexcel.Visible = True, objexcel.workbooks.add, Set objWorkbook = objExcel.Workbooks.Open