Welcome to Delicate template
Header
Just another WordPress site
Header

Excel Scripts

February 20th, 2013 | Posted by QtpVinod in QTP | QTP Scripting

Excel Script Examples

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

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) Check if the Excel file exists or not, if exists open the file and enter some data , If not Exists create the file and enter some data and   save the file through VB script?

Dim objExcel, FilePath

FilePath=”C:\Documents and Settings\gcr.GCRC-9A12FBD3D9\Desktop\data.xls”

Set objExcel=CreateObject(“Excel.Application”)

set objFso=CreateObject(“Scripting.FileSystemObject”)

objExcel.Visible=True

If Not objFso.FileExists(FilePath)  Then

objExcel.Workbooks.Add

objExcel.Cells(1,1).value=”QTP”

objExcel.ActiveWorkbook.SaveAs (FilePath)

Else

set myFile= objExcel.Workbooks.Open (FilePath)

Set mySheet=myFile.Worksheets(“Sheet1″)

mySheet.cells(1,1).value=”QTP”

objExcel.ActiveWorkbook.Save

End If

objExcel.Quit

Set objExcel=Nothing

3) 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\data.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

4) Comparing two Excel Files

Set objExcel = CreateObject(“Excel.Application”)

objExcel.Visible =   True

Set objWorkbook1= objExcel.Workbooks.Open(“E:\data1.xls”)

Set objWorkbook2= objExcel.Workbooks.Open(“E:\data2.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

5) Data Driven Testing using Data Table methods

Datatable.AddSheet “data”

Datatable.ImportSheet   “C:\Documents and Settings\Administrator\Desktop\data.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 “nithin”

Datatable.ImportSheet “C:\Documents and   Settings\Administrator\Desktop\data.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

6) Open an Excel Spreadsheet

Set objExcel = CreateObject(“Excel.Application”)

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\data.xls”)

7) 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

8) 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

9) 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

10) Short an excel sheet column

Set objExcel = CreateObject(“Excel.Application”)

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add

Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = “5″

objExcel.Cells(2, 1).Value = “1″

objExcel.Cells(3, 1).Value = “0″

objExcel.Cells(4, 1).Value = “3″

Set objRange=objworksheet.usedrange

objrange.sort(objrange)

11) Add New Sheet to Excel File

Dim objExcel

Set objExcel = createobject(“Excel.Application”)

objExcel.Visible=True

objExcel.Workbooks.Add

objexcel.ActiveWorkbook.SaveAs (“C:\Documents and Settings\gcr.GCRC-9A12FBD3D9\Desktop\data.xls”)

objExcel.Worksheets.Add

objExcel.ActiveWorkbook.Save

objExcel.Quit

Set objExcel=Nothing

12) Rename Sheets in an Excel File (WorkBook)

Dim objExcel

Set objExcel = createobject(“Excel.Application”)

objExcel.Visible=True

objExcel.Workbooks.Add

objexcel.ActiveWorkbook.SaveAs (“C:\Documents and Settings\gcr.GCRC-9A12FBD3D9\Desktop\data.xls”)

objExcel.Worksheets(“Sheet1″).Name=”nithin”

objExcel.Worksheets(“Sheet2″).Name=”qtp”

objExcel.Worksheets(“Sheet3″).Name=”training”

objExcel.ActiveWorkbook.Save

objExcel.Quit

Set objExcel=Nothing

13) Add a Sheet to an Excel File (WorkBook) and change the Position

Dim objExcel

Set objExcel = createobject(“Excel.Application”)

objExcel.Visible=True

Set myFile= objExcel.Workbooks.Add

objexcel.ActiveWorkbook.SaveAs (“C:\Documents and Settings\gcr.GCRC-9A12FBD3D9\Desktop\data.xls”)

objExcel.Worksheets.Add

myFile.Sheets(“Sheet4″).Move, myFile.Sheets(4)

objExcel.ActiveWorkbook.Save

objExcel.Quit

Set objExcel=Nothing

 

 

You can follow any responses to this entry through the RSS 2.0 You can leave a response, or trackback.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>