QTP:- Example for Working with Excel Sheets
Working with Excel
Sheets
a) Create an excel sheet
and enter a value into first cell
Dim objexcel
Set objexcel =
createobject("Excel.application")
objexcel.Visible = True
Set
wb=objexcel.Workbooks.add
Set
ws=wb.Worksheets("sheet1")
ws.Cells(1, 1).Value =
"Testing"
ws.ActiveWorkbook.SaveAs("f:\KKKeddy1.xls")
wb.Close
Set objexcel=nothing
Or
'''save the file with
read and write protected '''''pwd1 is for read protected pwd2 is for write
protected
Set
xl=CreateObject("Excel.Application")
Set wb=xl.Workbooks.Add
xl.DisplayAlerts=False
Set
ws=wb.Worksheets("sheet1")
ws.cells(1,1)=100
ws.cells(1,2)=200
wb.Saveas
"e:\data2.xls",,"pwd1","pwd2"
wb.Close
Set xl=nothing
Or
'''Script to open excel
file ,which is read and write protected write data
'''''pwd1 is for read
protected pwd2 is for write protected
Set
xl=CreateObject("Excel.Application")
Set
wb=xl.Workbooks.Open("e:\data2.xls",0,False,5,"pwd1","pwd2")
xl.DisplayAlerts=False
Set
ws=wb.Worksheets("sheet1")
ws.cells(1,2)="hello"
ws.cells(2,2)="new
data"
wb.Save
wb.Close
Set xl=nothing
b) Open existing file
and write data in second column in Sheet1
Dim xl,wb,ws
Set
xl=CreateObject("Excel.Application")
Set wb=xl.Workbooks.Open("e:\file1.xls")
Set
ws=wb.Worksheets("sheet1")
ws.cells(1,2)="mindq"
ws.cells(2,2)="hyd"
ws.cells(3,2)="ap"
wb.Save
wb.Close
Set xl=nothing
c) Read data from excel
from rows and columns
Dim xl,wb,ws
Set
xl=CreateObject("Excel.Application")
Set
wb=xl.Workbooks.Open("e:\file1.xls")
Set
ws=wb.Worksheets("sheet1")
r=ws.usedrange.rows.count
c=ws.usedrange.columns.count
For i=1 to r
v=""
For j=1 to c
v=v&"
"& ws.cells(i,j)
Next
print v
print
"-----------------------"
Next
wb.Close
Set xl=nothing
d) Set the bgcolor in a
cell in excel
Dim xl,wb,ws
Set
xl=CreateObject("Excel.Application")
Set
wb=xl.Workbooks.Open("e:\file3.xls")
Set
ws=wb.Worksheets("sheet1")
r=ws.usedrange.rows.count
c=ws.usedrange.columns.count
For i=1 to r
For j=1 to c
x=ws.cells(i,j).interior.colorindex
msgbox x
Next
Next
wb.Close
Set xl=nothing
e) Open an Excel
Spreadsheet
Set objExcel =
CreateObject("Excel.Application")
Set objWorkbook =
objExcel.Workbooks.Open("C:\Scripts\KKKeddy.xls")
f) 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
g) 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
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\KKK.KKKC-9A12FBD3D9\Desktop\KKK.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
Set
myExcel=Createobject("Excel.Application")
Set
myFile=myExcel.workbooks.open ("C:\Documents and Settings\admin\My
Documents\KKKeddy.xls")
Set
mySheet=myFile.worksheets("Sheet1")
Rows_Count=mySheet.usedrange.rows.count
For i= 1 to Rows_Count
Agent=mySheet.cells(i,"A")
pwd=mySheet.Cells(i,"B")
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"
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent
Name:").Set Agent
Dialog("Login").WinEdit("Password:").SetSecure
pwd
Dialog("Login").WinEdit("Password:").Type
micReturn
Window("Flight
Reservation").Close
Next
4) Compare two excel
files
Set objExcel =
CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook1=
objExcel.Workbooks.Open("E:\KKK1.xls")
Set objWorkbook2=
objExcel.Workbooks.Open("E:\KKK2.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) How to compare two
excel sheets of the same excel workbook and highlight the cells with different
values in the first sheet?
Excel object is being
created here
Set Exl_Obj =
CreateObject("Excel.Application")
Exl_Obj.Visible = True
File sac1 is being
opened
Set WB_Obj_1=
Exl_Obj.Workbooks.Open("C:\sac1.xls")
Cells in the first and
second sheet of sac1 are compared
Set WS_Obj_1=
WB_Obj_1.Worksheets(1)
Set WS_Obj_2=
WB_Obj_1.Worksheets(2)
For Each cell In WS_Obj_1.UsedRange
If cell.Value <>
WS_Obj_2.Range(cell.Address).Value Then
cell.Interior.ColorIndex
= 6
Else
cell.Interior.ColorIndex
= 0
End If
Next
Exl_Obj.workbooks("sac1.xls").save
Exl_Obj.workbooks("sac1.xls").close
Exl_Obj.Application.Quit
set Exl_Obj=nothing
6) How to compare two
excel files and highlight the cells with different values in the first file?
Excel object is being
created here
Set Exl_Obj =
CreateObject("Excel.Application")
Exl_Obj.Visible = True
Two files sac1 and sac2
are opened
Set WB_Obj_1=
Exl_Obj.Workbooks.Open("C:\sac1.xls")
Set WB_Obj_2=
Exl_Obj.Workbooks.Open("C:\sac2.xls")
Cells in the first sheet
of both the files are compared
Set WS_Obj_1=
WB_Obj_1.Worksheets(1)
Set WS_Obj_2=
WB_Obj_2.Worksheets(1)
For Each cell In WS_Obj_1.UsedRange
If cell.Value <>
WS_Obj_2.Range(cell.Address).Value Then
cell.Interior.ColorIndex
= 6
Else
cell.Interior.ColorIndex
= 0
End If
Next
Exl_Obj.workbooks("sac1.xls").save
Exl_Obj.workbooks("sac1.xls").close
Exl_Obj.workbooks("sac2.xls").save
Exl_Obj.workbooks("sac2.xls").close
Exl_Obj.Application.Quit
set Exl_Obj=nothing
7) How to compare range
of cells in two excel files and highlight the cells with different values in
the first file?
myrange=inputbox("enter
range of cells e.g. A1:A5")
Excel object is being
created here
Set Exl_Obj =
CreateObject("Excel.Application")
Exl_Obj.Visible = True
Two files sac1 and sac2
are opened
Set WB_Obj_1=
Exl_Obj.Workbooks.Open("C:\sac1.xls")
Set WB_Obj_2=
Exl_Obj.Workbooks.Open("C:\sac2.xls")
Cells in the first sheet
of both the files are compared
Set WS_Obj_1=
WB_Obj_1.Worksheets(1)
Set WS_Obj_2=
WB_Obj_2.Worksheets(1)
For Each cell In
WS_Obj_1.Range(myrange)
If cell.Value <>
WS_Obj_2.Range(cell.Address).Value Then
cell.Interior.ColorIndex
= 6
Else
cell.Interior.ColorIndex
= 0
End If
Next
Exl_Obj.workbooks("sac1.xls").save
Exl_Obj.workbooks("sac1.xls").close
Exl_Obj.workbooks("sac2.xls").save
Exl_Obj.workbooks("sac2.xls").close
Exl_Obj.Application.Quit
set Exl_Obj=nothing
8) Data Driven Testing
using Data Table methods
Datatable.AddSheet
"KKKeddy"
Datatable.ImportSheet
"C:\Documents and Settings\Administrator\Desktop\KKKeddy.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
"KKKeddy"
Datatable.ImportSheet
"C:\Documents and Settings\Administrator\Desktop\KKKeddy.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
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) Sort 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\KKK.KKKC-9A12FBD3D9\Desktop\KKKeddy.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\KKK.KKKC-9A12FBD3D9\Desktop\KKKeddy.xls")
objExcel.Worksheets("Sheet1").Name="KKK"
objExcel.Worksheets("Sheet2").Name="qtp"
objExcel.Worksheets("Sheet3").Name="training"
objExcel.ActiveWorkbook.Save
objExcel.Quit
Set objExcel=Nothing
11) 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\KKK.KKKC-9A12FBD3D9\Desktop\KKKeddy.xls")
objExcel.Worksheets.Add
myFile.Sheets("Sheet4").Move,
myFile.Sheets(4)
objExcel.ActiveWorkbook.Save
objExcel.Quit
Set objExcel=Nothing
12) Read a CSV File
Using Database Techniques
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic =
3
Const adCmdText =
&H0001
Set objConnection =
CreateObject("ADODB.Connection")
Set objRecordSet =
CreateObject("ADODB.Recordset")
strPathtoTextFile =
"C:\Databases\"
objConnection.Open
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile
& ";" & _
"Extended
Properties=""text;HDR=YES;FMT=Delimited"""
objRecordset.Open
"SELECT * FROM PhoneList.csv", _
objConnection, adOpenStatic, adLockOptimistic,
adCmdText
Do Until
objRecordset.EOF
Wscript.Echo "Name:
" & objRecordset.Fields.Item("Name")
Wscript.Echo
"Department: " & _
objRecordset.Fields.Item("Department")
Wscript.Echo
"Extension: " &
objRecordset.Fields.Item("Extension")
objRecordset.MoveNext
Loop