QTP code -Read From Excel File


 Read all the data from an Excel file.



' =============================================================
' function: ReadXLS
' desc :    Reads a sheet from an XLS file and stores the content
'           in a multi-dimensional array
' params :  strFileName is XLS file to read, including path
'           strSheetName is the name of the sheet to read, i.e "Sheet1"
' returns : Multi-dimensional array containing all data from 
'           the XLS
' =============================================================
Function ReadXLS(strFileName,strSheetName)

Dim strData()
Dim objFSobjExcelobjSheetobjRange
Dim intTotalRowintTotalCol
Dim intRowintCol

' create the file system object
Set objFS = CreateObject("Scripting.FileSystemObject")

' ensure that the xls file exists
If Not objFS.FileExists(strFileNameThen

    ' issue a fail if the file wasn't found
    Reporter.ReportEvent micFail"Read XLS""Unable to read XLS file, file not found: " & strFileName
    ' file wasn't found, so exit the function
    Exit Function

End If ' file exists

' create the excel object 
Set objExcel = CreateObject("Excel.Application")

' open the file
objExcel.Workbooks.open strFileName

' select the worksheet
Set objSheet = objExcel.ActiveWorkbook.Worksheets(strSheetName)

' select the used range
Set objRange = objSheet.UsedRange

' count the number of rows
intTotalRow=CInt(Split(objRange.Address"$")(4)) - 1

' count the number of columns
intTotalColobjSheet.Range("A1").CurrentRegion.Columns.Count

' redimension the multi-dimensional array to accomodate each row and column
ReDim strData(intTotalRowintTotalCol)

' for each row
For intRow = 0 to intTotalRow - 1

    ' for each column
    For intCol =0 to intTotalCol - 1

        ' store the data from the cell in the array
        strData(intRowintcol) = Trim(objSheet.Cells(intRow + 2,intcol + 1).Value)

    Next ' column

Next ' row

' close the excel object
objExcel.DisplayAlerts = False
objExcel.Quit 

' destroy the other objects 
Set objFS = Nothing 
Set objExcel = Nothing
Set objSheet = Nothing 

' return the array containing the data
ReadXLS = strData

End Function ' ReadXLS

Source: http://qtphelper.com

QTP code -Custom Report Entry


 Creating a customised entry in the results.



' Example usage
CustomReportEntry micFail"Custom Report Example""
This is a custom report entry!
"


' =============================================================
' function: CustomReportEntry
' desc : Creates a customized entry in the result file, you
' can use standard HTML tags in the message.
' params : strStatus is the result, micPass, micFail etc
' strStepName is the name of the step
' strMessage is the failure message, this can contain
' html tags
' returns : Void
' =============================================================
Function CustomReportEntry(strStatusstrStepNamestrMessage)

' create a dictionary object
Set objDict = CreateObject("Scripting.Dictionary")

' set the object properties
objDict("Status") = strStatus
objDict("PlainTextNodeName") = strStepName
objDict("StepHtmlInfo") = strMessage
objDict("DllIconIndex") = 206
objDict("DllIconSelIndex") = 206
objDict("DllPAth") = "C:\Program Files\Mercury Interactive\QuickTest Professional\bin\ContextManager.dll"

' report the custom entry
Reporter.LogEvent "User"objDictReporter.GetContext

End Function 'CustomReportEntry

Source: http://qtphelper.com

QTP code -Write to the Registry


Write a value to the Registry.



' =============================================================
' Sub :    RegistryWrite
' desc :   Writes a key value to the registry
' params : strRoot is the root key, i.e. "HKLM", "HKCU"
'          strPath is the path to create, i.e. 
'          "Software\Test\Automation"
'          strValue is the value to write in the key
' returns : void
' =============================================================
Function RegistryWrite(strRootstrPathstrValue)

' create the shell object
Set objShell = CreateObject("WScript.Shell")

' write the key
objShell.RegWrite strRoot & "\" & strPathstrValue"REG_SZ"

' destroy the object
Set objShell = Nothing

End Function 'RegistryWrite

Source: http://qtphelper.com

QTP code -Read From Excel File


Read all the data from an Excel file.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
' =============================================================
' function: ReadXLS
' desc :    Reads a sheet from an XLS file and stores the content
'           in a multi-dimensional array
' params :  strFileName is XLS file to read, including path
'           strSheetName is the name of the sheet to read, i.e "Sheet1"
' returns : Multi-dimensional array containing all data from 
'           the XLS
' =============================================================
Function ReadXLS(strFileName,strSheetName)

Dim strData()
Dim objFSobjExcelobjSheetobjRange
Dim intTotalRowintTotalCol
Dim intRowintCol

' create the file system object
Set objFS = CreateObject("Scripting.FileSystemObject")

' ensure that the xls file exists
If Not objFS.FileExists(strFileNameThen

    ' issue a fail if the file wasn't found
    Reporter.ReportEvent micFail"Read XLS""Unable to read XLS file, file not found: " & strFileName
    ' file wasn't found, so exit the function
    Exit Function

End If ' file exists

' create the excel object 
Set objExcel = CreateObject("Excel.Application")

' open the file
objExcel.Workbooks.open strFileName

' select the worksheet
Set objSheet = objExcel.ActiveWorkbook.Worksheets(strSheetName)

' select the used range
Set objRange = objSheet.UsedRange

' count the number of rows
intTotalRow=CInt(Split(objRange.Address"$")(4)) - 1

' count the number of columns
intTotalColobjSheet.Range("A1").CurrentRegion.Columns.Count

' redimension the multi-dimensional array to accomodate each row and column
ReDim strData(intTotalRowintTotalCol)

' for each row
For intRow = 0 to intTotalRow - 1

    ' for each column
    For intCol =0 to intTotalCol - 1

        ' store the data from the cell in the array
        strData(intRowintcol) = Trim(objSheet.Cells(intRow + 2,intcol + 1).Value)

    Next ' column

Next ' row

' close the excel object
objExcel.DisplayAlerts = False
objExcel.Quit 

' destroy the other objects 
Set objFS = Nothing 
Set objExcel = Nothing
Set objSheet = Nothing 

' return the array containing the data
ReadXLS = strData

End Function ' ReadXLS

Source: http://qtphelper.com