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

Pride and Prejudice 

Illuminations 

QTP -Flat File Scripts

1) Creating a Flat File

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.CreateTextFile("E:\Gcreddy.txt")

2) Checking weather the File is available or not, if not creating the File

strDirectory="E:\"

strFile="Gcreddy.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(strDirectory & strFile) Then

Set objFolder = objFSO.GetFolder(strDirectory)

Else

Set objFile = objFSO.CreateTextFile("E:\Gcreddy.txt")

End if

3) Reading Data character by character from a Flat File

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("E:\Gcreddy.txt", 1)

Do Until objFile.AtEndOfStream

strCharacters = objFile.Read(1)

msgbox strCharacters

Loop

4) Reading Data line by line from a Flat File

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("E:\Gcreddy.txt", 1)

Do Until objFile.AtEndOfStream

strCharacters = objFile.Readline

msgbox strCharacters

Loop

5) Reading data from a flat file and using in data driven testing

Dim fso,myfile

Set fso=createobject("scripting.filesystemobject")

Set myfile= fso.opentextfile ("F:\Gcreddy.txt",1)

myfile.skipline

While myfile.atendofline <> True

x=myfile.readline

s=split (x, ",")

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 s(0)

Dialog("Login").WinEdit("Password:").SetSecure s(1)

Dialog("Login").WinButton("OK").Click

Window("Flight Reservation").Close

Wend

6) Writing data to a text file

Dim Stuff, myFSO, WriteStuff, dateStamp

dateStamp = Date()

Stuff = "I am Preparing this script: " &dateStamp

Set myFSO = CreateObject("Scripting.FileSystemObject")

Set WriteStuff = myFSO.OpenTextFile("e:\Gcreddy.txt", 8, True)

WriteStuff.WriteLine(Stuff)

WriteStuff.Close

SET WriteStuff = NOTHING

SET myFSO = NOTHING

7) Delete a text file

Set objFSO=createobject("Scripting.filesystemobject")

Set txtFilepath = objFSO.GetFile("E:\gcr.txt")

txtFilepath.Delete()

8) Checking weather the File is available or not, if available delete the File

strDirectory="E:\"

strFile="gcr.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(strDirectory & strFile) Then

Set objFile = objFSO.Getfile(strDirectory & strFile)

objFile.delete ()

End if

9) Comparing two text files

Dim f1, f2

f1="e:\Gcreddy1.txt"

f2="e:\Gcreddy2.txt"

Public Function CompareFiles (FilePath1, FilePath2)

Dim FS, File1, File2

Set FS = CreateObject("Scripting.FileSystemObject")

If FS.GetFile(FilePath1).Size <> FS.GetFile(FilePath2).Size Then

CompareFiles = True

Exit Function

End If

Set File1 = FS.GetFile(FilePath1).OpenAsTextStream(1, 0)

Set File2 = FS.GetFile(FilePath2).OpenAsTextStream(1, 0)

CompareFiles = False

Do While File1.AtEndOfStream = False

Str1 = File1.Read

Str2 = File2.Read

CompareFiles = StrComp(Str1, Str2, 0)

If CompareFiles <> 0 Then

CompareFiles = True

Exit Do

End If

Loop

File1.Close()

File2.Close()

End Function

Call Comparefiles(f1,f2)

If CompareFiles(f1, f2) = False Then

MsgBox "Files are identical."

Else

MsgBox "Files are different."

End If

10) Counting the number of times a word appears in a file

sFileName="E:\gcr.txt"

sString="gcreddy"

Const FOR_READING = 1

Dim oFso, oTxtFile, sReadTxt, oRegEx, oMatches

Set oFso = CreateObject("Scripting.FileSystemObject")

Set oTxtFile = oFso.OpenTextFile(sFileName, FOR_READING)

sReadTxt = oTxtFile.ReadAll

Set oRegEx = New RegExp

oRegEx.Pattern = sString

oRegEx.IgnoreCase = bIgnoreCase

oRegEx.Global = True

Set oMatches = oRegEx.Execute(sReadTxt)

MatchesFound = oMatches.Count

Set oTxtFile = Nothing : Set oFso = Nothing : Set oRegEx = Nothing

msgbox MatchesFound

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

12) Read a Text File into an Array

Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objTextFile = objFSO.OpenTextFile _

    ("e:\gcreddy.txt", ForReading)

Do Until objTextFile.AtEndOfStream

    strNextLine = objTextFile.Readline

    arrServiceList = Split(strNextLine , ",")

    Wscript.Echo "Server name: " & arrServiceList(0)

    For i = 1 to Ubound(arrServiceList)

        Wscript.Echo "Service: " & arrServiceList(i)

    Next

Loop

The Blind Side
The Adventures of Sherlock Holmes