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 objFS, objExcel, objSheet, objRange
Dim intTotalRow, intTotalCol
Dim intRow, intCol

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

' ensure that the xls file exists
If Not objFS.FileExists(strFileName) Then

    ' 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
intTotalCol= objSheet.Range("A1").CurrentRegion.Columns.Count

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

' 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(intRow, intcol) = 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: https://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(strStatus, strStepName, strMessage)

' 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", objDict, Reporter.GetContext

End Function 'CustomReportEntry
Source: https://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(strRoot, strPath, strValue)

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

' write the key
objShell.RegWrite strRoot & "\" & strPath, strValue, "REG_SZ"

' destroy the object
Set objShell = Nothing

End Function 'RegistryWrite
Source: https://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 objFS, objExcel, objSheet, objRange
Dim intTotalRow, intTotalCol
Dim intRow, intCol

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

' ensure that the xls file exists
If Not objFS.FileExists(strFileName) Then

    ' 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
intTotalCol= objSheet.Range("A1").CurrentRegion.Columns.Count

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

' 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(intRow, intcol) = 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: https://qtphelper.com