SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 3 of 3
  1. #1
    Junior Member
    Join Date
    Nov 2005
    Location
    Mumbai
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    QTP script with Excel sheet

    Suppose i have used the first row for column heading then can i use the heading of the column for accessing the data of perticular Cell

    e.g.
    Rowno=10
    Column_Name = Roll_No
    GetCellData(SheetName,Rowno,Column_Name)
    Then Can i get data from cell(10,Column_Name)

    If Not then how to get data using Column name?

  2. #2
    Junior Member
    Join Date
    Jan 2006
    Location
    pune
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: QTP script with Excel sheet

    yes you can do this.

    DataTable.ImportSheet "<DataTable Name With Complete Patha>", <sheet source id>
    DataTable.SetCurrentRow(<Row Number From Which You want to fetch data&gt
    data_you_want_to_fetch = DataTable("<column name>")
    Thanks and Regards,
    Bubuna

  3. #3
    Moderator ifraser's Avatar
    Join Date
    Jul 2004
    Location
    Brisbane, Australia
    Posts
    2,090
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: QTP script with Excel sheet

    Public sFileName
    Public Row
    Public sData
    Public notfound
    Public iNoError
    Public sCurrentProc
    Public sWinDir
    Public lResult
    Public strMsgText
    Public iColNum
    Public sTestData
    Public ColHead
    Public rows
    Public Columns
    Public Value
    Public FoundVal
    Public oSearchRegion
    Public oSearchResult
    Public FirstCol
    Public sFieldName
    Public vSheet
    Public TotalRows
    Public iRow
    Public fso
    Public objWorkBook
    Public objWorkSheet

    '#### This block sets up the Data File to use
    '#### You will need to create the file and the path
    sFileName = "C:\Data\Client.xls" 'Set the sFilename value
    Row = 2 'Set the start Row
    vSheet = "Client_Creation" 'Set the Start Sheet

    '### These Input boxes are to demonstrate assigning the string values. These values could come from a GET Text function.
    strClientType = InputBox("Client Type")
    strGivenName = InputBox("Enter your Given Name")
    strSurname = InputBox("Enter your Surname")

    '#### This line writes Data to sFileName. It will write data to a Column Called "ClientTypeS" it will write the value strClientType
    '#### On the Sheet called "Client_Creation". If the Column ClientTypeS doesnt exist it will be created. If the sheet Client_Creation
    '#### doesnt exist it will be created
    Call WriteExcelDataSingle(sFileName, 2, "ClientTypeS", strClientType,"Client_Creation")
    Call WriteExcelDataSingle(sFileName, 2, "Surname", strSurname,"Client_Creation")
    Call WriteExcelDataSingle(sFileName, 2, "Given_Name1", strGivenName,"Client_Creation")

    '#### This code will read the values created by the code above
    Call OpenExcel(sFileName, vSheet)
    strClientType1 = ReadExcelDataSingle(sFileName,Row,"ClientTypeS",vS heet)
    strSurname1 = ReadExcelDataSingle(sFileName,Row,"Surname",vSheet )
    strGivenName1 = ReadExcelDataSingle(sFileName,Row,"Given_Name1",vS heet)
    Call CloseExcel(sFileName)

    '#### Just to show the values
    msgbox strClientType1 & " " & strGivenName1 & " " & strSurname1

    '################################################# #############################################
    '# The code below will read across columns
    '#
    '#
    '################################################# #############################################

    iRow = 2
    ControlVar_1 = 1
    Call OpenExcel(sFileName, "Client_Creation") 'Open Data File for read
    'TestVar = ReadExcelDataSingle(sFileName,iRow,ControlVar,"Cli ent_Creation")

    Do while ControlVar_1 < 6
    ' Work along the Top Row EXCL_PERILS
    strInputEXCLPRLS = ReadExcelDataSingle(sFileName,iRow,"EXCL_PERILS_" & ControlVar_1,"Client_Creation")
    Call Popup (strInputEXCLPRLS) 'The Popup will display each value in order across the sheet
    ControlVar_1 = ControlVar_1 + 1
    loop

    Call CloseExcel(sFileName) 'Close Data File

    '************************************************* *****************************
    '* ReadExcelDataSingle Developed by: Steve Morrin
    '*
    '*
    '* Converted for QTP use by: Ian Fraser 01 Dec 2005
    '* 20/06/03 Steve Morrin Add dictionery
    '************************************************* *****************************
    '* Example
    '* strTest = ReadExcelDataSingle(sFileName,2,"PolicyNoHom","Glo bal")
    '*
    Public Function ReadExcelDataSingle(sFileName,iRow,sFieldName,vShe et)

    Dim sData
    Dim iNoError
    Dim sCurrentProc
    Dim sWinDir
    Dim lResult
    Dim strMsgText
    Dim iColNum
    Dim sTestData
    Dim ColHead

    'Count Columns
    ColCount = objWorkSheet.UsedRange.columns.count

    set oSearchRegion = objWorkSheet.Range(objWorkSheet.cells(1,1),objWork Sheet.cells(1, ColCount ))
    notfound = ""
    with oSearchRegion
    set oSearchResult = .Find(sFieldName)
    if not oSearchResult is nothing then
    iColNum = oSearchResult.column
    FirstCol = iColNum
    FoundVal = objWorkSheet.Rows(1).Columns(iColNum).Value
    if sFieldName <> FoundVal then
    do
    set oSearchResult = .Findnext(oSearchResult)
    iColNum = oSearchResult.column
    FoundVal = objWorkSheet.Rows(1).Columns(iColNum).Value
    loop while FirstCol <> iColNum and sFieldName <> FoundVal
    if sFieldName <> FoundVal then notfound = "Y"
    end if
    else
    notfound = "Y"
    end if
    End With

    'Retrieve the value from the cell
    sData = ""
    if notfound = "" then
    sData =objWorkSheet.rows(iRow).Columns(iColNum).Value
    end if

    sData = Trim(sData)

    ReadExcelDataSingle = sData

    End Function
    '************************************************* *****************************
    '************************************************* *****************************
    '* OpenExcel
    '*
    '* Open XL ready for read
    '************************************************* *****************************
    Public sub OpenExcel(sFileName, vSheet)

    Set fso = CreateObject("Excel.Application")

    Set objExcel = fso

    'objWorkBook = objExcel.Workbooks.Open (sFileName)
    Set objWorkBook = objExcel.Workbooks.Open (sFileName)

    'See if a sheet was referenced in the call. If not, default to the first sheet
    If vSheet = "" Then
    vSheet = 1
    End If

    'Set up a reference to the sheet in the workbook
    Set objWorkSheet = objWorkBook.WorkSheets(vSheet)

    'Create a new sheet if required
    'On Error Resume

    if objWorkBook.WorkSheets(vSheet) is nothing then

    objWorkBook.WorkSheets.Add
    'objWorkBook.WorkSheets.Select
    newName = objWorkBook.ActiveSheet.Name

    objWorkBook.WorkSheets(newName).Name = vSheet
    'Save the changes
    objWorkBook.Save
    end if

    'Set up a reference to the sheet in the workbook
    Set objWorkSheet = objWorkBook.WorkSheets(vSheet)

    End sub
    '************************************************* *****************************
    '************************************************* *****************************
    '* CloseExcel
    '*
    '* Close XL
    '************************************************* *****************************
    Public sub CloseExcel(sFileName)

    Set objExcel = fso
    Set objWorkBook = objExcel.Workbooks.Open (sFileName)

    'Save the changes
    objWorkBook.Save
    objWorkBook.Close

    'Clear all the references to the objects
    Set objWorkBook = Nothing


    objExcel.Quit
    Set objExcel = Nothing

    'reset the distionary object
    Set oDict = Nothing
    sPersistXL = "" 'Global var
    End sub
    '************************************************* *****************************
    '* WriteExcelDataSingle Developed by: Steve Morrin
    '*
    '* Converted for QTP use by: Ian Fraser 01 Dec 2005
    '* 20/06/03 - Steve Morrin - Add dictionary.
    '************************************************* *****************************
    '* Example
    '* Call WriteExcelDataSingle(sFileName, 2, "PolicyNoHom", strOne,"Global")
    '*
    'Function WriteExcelDataSingle(sFileName, iRow, sFieldName, vValue,vSheet)
    Public Function WriteExcelDataSingle(sFileName, iRow, sFieldName, vValue,vSheet)

    'Dimension the needed variables
    Dim iNoError
    Dim sCurrentProc
    Dim sWinDir
    Dim lResult
    Dim strMsgText
    Dim iColNum
    Dim sTestData
    Dim NewVal

    'Deal with the optional parameters
    If vSheet = "" Then
    vSheet = 1
    End If

    Set fso = CreateObject("Excel.Application")

    'Open up Excel
    If fso Is Nothing Then
    Set fso = CreateObject("Excel.Application")
    End If

    Set objExcel = fso

    'Open the workbook
    If sFileName = "" Then
    set oDict = Nothing ' do not use dictionery for new sheet
    Set objWorkBook = objExcel.Workbooks.Add
    objWorkBook.SaveAs (sFileName)
    Else
    Set objWorkBook = objExcel.Workbooks.Open(sFileName)
    End If
    'Create a new sheet if required
    On Error Resume Next
    if objWorkBook.WorkSheets(vSheet) is nothing then
    set oDict = Nothing ' do not use dictionery for new sheet
    objWorkBook.WorkSheets.Add
    newName = objWorkBook.ActiveSheet.Name

    objWorkBook.WorkSheets(newName).Name = vSheet
    'Save the changes
    objWorkBook.Save
    end if

    'Set up a reference to the sheet in the workbook
    Set objWorkSheet = objWorkBook.WorkSheets(vSheet)

    'Count Columns
    ColCount = objWorkSheet.UsedRange.columns.count

    set oSearchRegion = objWorkSheet.Range(objWorkSheet.cells(1,1),objWork Sheet.cells(1, ColCount ))
    notfound = ""
    with oSearchRegion
    set oSearchResult = .Find(sFieldName)
    if not oSearchResult is nothing then
    iColNum = oSearchResult.column
    FirstCol = iColNum
    FoundVal = objWorkSheet.Rows(1).Columns(iColNum).Value
    if sFieldName <> FoundVal then
    do
    set oSearchResult = .Findnext(oSearchResult)
    iColNum = oSearchResult.column
    FoundVal = objWorkSheet.Rows(1).Columns(iColNum).Value
    loop while FirstCol <> iColNum and sFieldName <> FoundVal
    if sFieldName <> FoundVal then notfound = "Y"
    end if
    else
    notfound = "Y"
    end if
    End With

    if notfound = "Y" then
    iColNum = ColCount + 1
    if iColNum = 2 then 'check for empty col 1 on new sheet
    NewVal = objWorkSheet.Rows(1).Columns(1).Value
    if NewVal = "" then iColNum = 1
    end if
    'Write new heading
    objWorkSheet.Rows(1).Columns(iColNum).Value = sFieldName
    end if

    'write data to XL
    objWorkSheet.Rows(iRow).Columns(iColNum).Value = vValue

    'Save the changes
    objWorkBook.Save

    'At this point, everything that follows is cleanup. Since we want it to
    'occur even if an error occurred, we'll ignore any further errors generated
    'in this function

    On Error Resume Next
    if sPersistXL = "" then
    'Close the workbook
    objWorkBook.Close

    'Clear all the references to the objects
    Set objWorkBook = Nothing
    Set objWorkSheet = Nothing

    objExcel.Quit
    set objExcel = nothing
    end if

    End Function
    '################################################# ###########################################
    '################################################# ###########################################
    '# Function to look up a stored check point and when detected optionaly provide for reposition.
    '* Converted for QTP use by: Ian Fraser 14 Dec 2005
    '* 20/06/03 Steve Morrin
    '* Associated Scripts
    '* OpenExcel.vbs
    '*CloseExcel.vbs
    '*GlobalVars.vbs
    '################################################# ###########################################
    '#Set Start Row (Check Re-Start Value in DataTable)
    '#ChkCol = "Re-Start"
    '#Row = GetCheckpoint(sFileName ,ChkCol, vSheet)
    '################################################# ###########################################
    Public Function GetCheckpoint (sFileName ,ChkCol, vSheet)
    if vSheet = "" then vSheet = 1

    'look in XL to see if check point exists
    Call OpenExcel(sFileName, vSheet)
    sStartRow = ReadExcelDataSingle(sFileName,2,ChkCol, vSheet) ' in global not included here
    Call CloseExcel(sFileName)

    if sStartRow = "" or sStartRow = "2" then
    sStartRow = "2" 'All XLs have header rows so data starts in row.
    else
    sAdjustedStart = InputBox("Restart dectected Press OK to accept or type new start point. Include Header Row", _
    "Check Point Restart", sStartRow)
    do while sAdjustedStart <> sStartRow
    sStartRow = sAdjustedStart
    sAdjustedStart = InputBox("Confirm Start row","Restart Confirmation", sAdjustedStart )
    loop
    end if
    if sStartRow = "0" or sStartRow = "1" then sStartRow = "2" ' all data starts in row 2
    GetCheckpoint = (sStartRow)
    Call CloseExcel(sFileName)
    End Function

    '################################################# ##########################
    You can buy my Art from: "Post Cards now available"
    Ian Fraser Landscape Photography
    World Wide Shipping.

    http://mowogman.wordpress.com/

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 10.71%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 10:04 PM.

Copyright BetaSoft Inc.