Scribd Logo
Upload

Welcome to Scribd!

  • 611 views

    QTP Excel Scripts

    Uploaded by

    G.C.Reddy
    This document provides examples of using VBScript to perform various operations with Excel files and spreadsheets, including creating and saving Excel files, reading and writing data to Excel, sorting data, and performing data-driven testing using Excel as an external data source. The examples cover topics such as opening and closing Excel files, importing and exporting data between Excel and QTP's data table, comparing cell values between two Excel files, and formatting cells and text.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd

    QTP Excel Scripts

    Uploaded by

    G.C.Reddy
    611 views4 pages
    This document provides examples of using VBScript to perform various operations with Excel files and spreadsheets, including creating and saving Excel files, reading and writing data to Excel, sorting data, and performing data-driven testing using Excel as an external data source. The examples cover topics such as opening and closing Excel files, importing and exporting data between Excel and QTP's data table, comparing cell values between two Excel files, and formatting cells and text.

    Original Description:

    Excel Sheet Operations and Excel object model

    Original Title

    QTP Excel scripts

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides examples of using VBScript to perform various operations with Excel files and spreadsheets, including creating and saving Excel files, reading and writing data to Excel, sorting data, and performing data-driven testing using Excel as an external data source. The examples cover topics such as opening and closing Excel files, importing and exporting data between Excel and QTP's data table, comparing cell values between two Excel files, and formatting cells and text.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    611 views4 pages

    QTP Excel Scripts

    Uploaded by

    G.C.Reddy
    This document provides examples of using VBScript to perform various operations with Excel files and spreadsheets, including creating and saving Excel files, reading and writing data to Excel, sorting data, and performing data-driven testing using Excel as an external data source. The examples cover topics such as opening and closing Excel files, importing and exporting data between Excel and QTP's data table, comparing cell values between two Excel files, and formatting cells and text.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    You are on page 1of 4

    gcreddy.

    com

    Visit:

    www.gcreddy.com
    for QTP Info

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

    QTP Training 1
    gcreddy.com

    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"

    QTP Training 2
    gcreddy.com

    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

    QTP Training 3
    gcreddy.com

    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

    QTP Training 4

    You might also like