QTP & QC->Code for function validate the displayed Error pop up

'****************************************************************************************************************************
'Function Name -------- Fun_ValidateExpectedPopUp(ip_strExpectPopUpDetails)
'Description ------------ Use this function validate the displayed Error pop up
'Parameters ----------- ip_strExpectPopUpDetails : EX: Y|Invalid Date|OK or N|NO Message
'Return Value ---------
'         1: Indicates expected pop up is displayed (Need to exit the component on this event)
'         1: Indicates expected pop up is not displayed (Need to exit the component on this event)
'         1: Unexpectedly pop up is displayed (Need to exit the component on this event)
'         0: Unexpected pop up is not displayed (Need not to exit the component on this event)
'Example -------------- Call Fun_ValidateExpectedPopUp(ip_strExpectPopUpDetails)
'****************************************************************************************************************************
Function Fun_ValidateExpectedPopUp(ip_strExpectPopUpDetails)
 '<<< Local variable declaration >>>
 Dim arrErrorMessageDetails, strErrorMsg, blnFlag
 Fun_ValidateExpectedPopUp = "1" '
 arrErrorMessageDetails = Split(ip_strExpectPopUpDetails, "|")
 If UCase(arrErrorMessageDetails(0)) = "Y" Then
   If Browser("Browser Main").Window("-- Web Page Dialog").Exist(intConstMidWaitTime) Or _
    Browser("Browser Main").Dialog("Message from webpage").Exist(intConstMidWaitTime) Then
     '<<< Expected Pop Up expectedly displayed => Success >>>
     '<<< Validating the error message >>>
     strErrorMsg = ""
     If Browser("Browser Main").Window("-- Web Page Dialog").Page("Page").WebElement("WebTable").Exist(intConstShortWaitTime) Then
       strErrorMsg = Browser("Browser Main").Window("-- Web Page Dialog").Page("Page").WebElement("WebTable").GetROProperty("innertext")
     ElseIf Browser("Browser Main").Dialog("Message from webpage").Static("Please").Exist(intConstShortWaitTime) Then
       strErrorMsg =Browser("Browser Main").Dialog("Message from webpage").Static("Please").GetROProperty("text")
     End If
     If Instr(strErrorMsg, arrErrorMessageDetails(1)) Then
      '<<< Expected error message is displayed >>>
      Fun_ReportSuccess "Validate the pop up Message", "Expected Pop up message is displayed on the pop up Message as:" & VBNewline & strErrorMsg
     Else
      '<<< Expected error message is not displayed >>>
      Fun_ReportFailure "Validate the pop up Message", "Expected Pop up message is not displayed on the pop up message as:" & VBNewline & arrErrorMessageDetails(1) & VBNewline & "Instead it is displayed as:" & VBNewline & strErrorMsg, Browser("Browser Main")
     End If
   Else
     '<<< Expected Pop Up unexpectedly not displayed=> Failure >>>
     Fun_ReportFailure "Expected Pop up Validation", "Expected pop up not displayed.", Browser("Browser Main")
   End If  
 Else
   If Browser("Browser Main").Window("-- Web Page Dialog").Exist(intConstShortWaitTime) Or _
    Browser("Browser Main").Dialog("Message from webpage").Exist(intConstShortWaitTime) Then
     strErrorMsg = ""
     If Browser("Browser Main").Window("-- Web Page Dialog").Page("Page").WebElement("WebTable").Exist(intConstShortWaitTime) Then
       strErrorMsg = Browser("Browser Main").Window("-- Web Page Dialog").Page("Page").WebElement("WebTable").GetROProperty("innertext")
     ElseIf Browser("Browser Main").Dialog("Message from webpage").Static("Please").Exist(intConstShortWaitTime) Then
       strErrorMsg =Browser("Browser Main").Dialog("Message from webpage").Static("Please").GetROProperty("text")
     End If
     '<<< Unexpected Pop Up unexpectedly displayed => Failure >>>
     Fun_ReportFailure "Unexpected Pop up Validation", "Unexpected pop up is displayed with text as:" & VBNewLine & strErrorMsg, Browser("Browser Main")
   Else
     '<<< Unexpected Pop Up expectedly not displayed=> Success >>>
     Fun_ReportSuccess "Unexpected Pop up Validation", "No pop ups displayed as expected."  
     Fun_ValidateExpectedPopUp = "0" 
   End If
 End If
 
 If Browser("Browser Main").Window("-- Web Page Dialog").Exist(intConstShortWaitTime) Then
   Browser("Browser Main").Window("-- Web Page Dialog").Close
 ElseIf Browser("Browser Main").Dialog("Message from webpage").Exist(intConstShortWaitTime) Then
   Browser("Browser Main").Dialog("Message from webpage").Close
 End If
End Function


'****************************************************************************************************************************
'SubRoutine Name -------- sub_popUpValidate(ip_ExpectPopUp)
'Description ------------ Use this sub routine to call the eexpected pop up funtion and handling the pop up message
'Author ----------------- Naresh gupta
'Parameters ----------- ip_ExpectPopUp : EX: Parameter("p_SC_CSS_ExpectPopUp")
'Return Value --------- NA
'Registerd To ---------- NA
'Base Project ---------- EFAS
'Example -------------- Call sub_popUpValidate(Parameter("p_SC_CSS_ExpectPopUp"))     
'****************************************************************************************************************************
Sub sub_popUpValidate(ip_strExpectPopUp)
 Dim returnVal
 returnVal = Fun_ValidateExpectedPopUp(ip_strExpectPopUp)
 If Cstr(returnVal) = "1"  Then
  ExitComponentIteration
 End If
End Sub

JAVA Addin->Code for function used for java table

'**********************************************************************************************************
'Function Name:   funGetColumnNumberFromJavaTable
'Purpose :           This function will return the column number from the JAVA Table based on the column name passed
'Parameters:      ip_objTable: Object Table
'       ip_strExpectedColName: Expected column Name
'      ip_intStartColNum: Start index from this point search for the expected column will start
'      ip_blnIsRelativeComparison: Pass this parameter as False if column name is to be matched with exact column header
'                else pass as True for relative compartison
'Return  value       "": If Expected column is not found
'Example:           returnValue = funGetColumnNumberFromJavaTable(objLeftGrid, "Item", 0, False)
'**********************************************************************************************************
Public Function funGetColumnNumberFromJavaTable(ByRef ip_objTable, ip_strExpectedColName, ip_intStartColNum, ip_blnIsRelativeComparison)
  '<<< Default Return value is set as empty >>>
 funGetColumnNumberFromJavaTable = ""
 
 Dim intColCnt, intColIndex, strColName
 intColCnt = ip_objTable.GetROProperty("cols")
 '<<< Extract the cell value from given row number starting from the mentioned start index for column >>>
 For intColIndex = ip_intStartColNum To intColCnt - 1
  strColName = ip_objTable.GetColumnName(intColIndex)

  '<<< Using Instr function for relative comparison based upon the flag value passed >>>
  If ip_blnIsRelativeComparison = True Then  
   If Instr(UCase(Trim(strColName)),UCase(Trim(ip_strExpectedColName))) Then
    funGetColumnNumberFromJavaTable = intColIndex
    Exit Function
   End If
  Else
   If UCase(Trim(strColName)) = UCase(Trim(ip_strExpectedColName)) Then
    funGetColumnNumberFromJavaTable = intColIndex
    Exit Function
   End If
  End If
 Next
End Function

'**********************************************************************************************************
'Function Name:   Fun_ExpandJavaTable
'Purpose :           This function will exapnd the grid based on the value cell details passed
'Parameters:      ip_objTable: Object Table
'Return  value       0: If succeeded
'       1: If Fails
'Example:           returnValue = Fun_ExpandJavaTable(objLeftGrid)
'**********************************************************************************************************
Public Function Fun_ExpandJavaTable(ByRef ip_objTable)
 Dim intExpandableColNum, strExpandableColName
 Fun_ExpandJavaTable = 0 'indicates success
 intExpandableColNum = funGetColumnNumberFromJavaTable(ip_objTable, "(+)", 0, False)
 If Trim(CStr(intExpandableColNum)) = "" Then
  Fun_ReportFailure "Expandworklist grid", "Unable to find the expandable column with name as (+)", Browser("Browser Main")
  Fun_ExpandJavaTable = 1 'indicates failure
 End If

 strExpandableColName = ip_objTable.GetColumnName(intExpandableColNum)
 ip_objTable.SelectColumnHeader strExpandableColName
 Wait intConstShortWaitTime

 intExpandableColNum = funGetColumnNumberFromJavaTable(ip_objTable, "(-)", 0, False)
 If Trim(CStr(intExpandableColNum)) = "" Then
  Fun_ReportFailure "Expandworklist grid", "Unable to expand the java table", Browser("Browser Main")
  Fun_ExpandJavaTable = 1 'indicates failure
    End If
End Function

'**********************************************************************************************************
'Function Name:   fun_GetLotRowNumberWithDetails
'Purpose :           This function will return the expected lot under the javatable
'Parameters:      ip_objGridLeftGrid: Object Left Table which contains Item Lot information
'      ip_objGridRightGrid: Object Right Table which contains Item details information
'      ip_strItemNum: Expected Item Number EX: 5889550
'      ip_strLineNum: Expected Line Number under the given lot EX: 0108
'      ip_strColNameCollection: Column name details EX: "SSC#|Whse PO #| Engine Status"
'      ip_strColValueCollection: Expected value collection: "258|9564|Running"
'Return  value       "": If Fails
'       ROw number: If succeeds in finding a row
'Example:           returnValue = fun_GetLotRowNumberWithDetails(objLeftGrid, objRightGrid, "5889550", "0108", "SSC#|Whse PO #| Engine Status", "258|9564|Running")
'**********************************************************************************************************
Public Function fun_GetLotRowNumberWithDetails(ip_objGridLeftGrid,ip_objGridRightGrid, ip_strItemNum, ip_strLineNum, ip_strColNameCollection, ip_strColValueCollection)
 Dim arrColNameCollection, arrValueCollection, intExpectedColIndex, intRowCount, intRowIndex
 Dim blnDetailsMatched, intDetailsIndex, strActualValue, strLineValue, intItemColNum
 Dim arrColNumCollection
 Dim intFindItemNumIndex, strItemNum
 
 fun_GetLotRowNumberWithDetails = "" 'indicates failure
 intItemColNum = funGetColumnNumberFromJavaTable(ip_objGridLeftGrid, "Item", 0, False)
 If Trim(intItemColNum) = "" Then
  Fun_ReportFailure "Find Columns", "Unable to find the column with name as Item", Browser("Browser Main")
  fun_GetLotRowNumberWithDetails = "" 'indicates failure
  Exit Function
 End If
 arrColNameCollection = Split(ip_strColNameCollection, "|")
 arrValueCollection = Split(ip_strColValueCollection, "|")
 ReDim arrColNumCollection(UBound(arrColNameCollection))

 For intExpectedColIndex = 0 To UBound(arrColNameCollection)
  arrColNumCollection(intExpectedColIndex) = funGetColumnNumberFromJavaTable(ip_objGridRightGrid, Trim(arrColNameCollection(intExpectedColIndex)), 0, False)
  If Trim(arrColNumCollection(intExpectedColIndex)) = "" Then
   Fun_ReportFailure "Find Columns", "Unable to find the column with name as:" & arrColNameCollection(intExpectedColIndex), Browser("Browser Main")
   fun_GetLotRowNumberWithDetails = "" 'indicates failure
   Exit Function
  End If
 Next

 intRowCount = ip_objGridLeftGrid.GetROProperty("rows")
 For intRowIndex = 0 To intRowCount - 1
   blnDetailsMatched = True
   For intDetailsIndex = 0 To UBound(arrValueCollection)
    strActualValue = ip_objGridRightGrid.GetCellData(intRowIndex, arrColNumCollection(intDetailsIndex))
    Select Case UCase(Trim(arrValueCollection(intDetailsIndex)))
      Case "BLANK"
           If Trim(strActualValue) <> "" Then
            blnDetailsMatched = False
            Exit For
           End If
      Case "NON BLANK"   
           If Trim(strActualValue) = "" Then
            blnDetailsMatched = False
            Exit For
           End If
      Case Else
           If Trim(strActualValue) <> Trim(arrValueCollection(intDetailsIndex)) Then
            blnDetailsMatched = False
            Exit For
           End If
    End Select
   Next
   If blnDetailsMatched = True Then
     strLineValue = ip_objGridLeftGrid.GetCellData(intRowIndex, intItemColNum)
     If Trim(strLineValue) = Trim(ip_strLineNum) Then
       For intFindItemNumIndex = intRowIndex - 1 To 0 Step -1
         strItemNum = Trim(ip_objGridLeftGrid.GetCellData(intFindItemNumIndex, intItemColNum))
         If Len(strItemNum) = 7 Then
          If Trim(strItemNum) = Trim(ip_strItemNum) Then
           fun_GetLotRowNumberWithDetails = intFindItemNumIndex
           Exit Function
          Else
           Exit For
          End If
         End If               
       Next    
     End If 
   End If
 Next
End Function


'**********************************************************************************************************
'Function Name:   fun_GetLotRowNumberWithoutDetails
'Purpose :           This function will return the expected lot under the javatable
'Parameters:      ip_objLeftGrid: Object Left Table which contains Item Lot information
'      ip_strItemNum: Expected Item Number EX: 5889550
'      ip_strLineNum: Expected Line Number under the given lot EX: 0108
'Return  value       "": If Fails
'       ROw number: If succeeds in finding a row
'Example:           returnValue = fun_GetLotRowNumberWithoutDetails(objLeftGrid, "5889550", "0108")
'**********************************************************************************************************
Public Function fun_GetLotRowNumberWithoutDetails(ip_objLeftGrid, ip_strItemNum, ip_strLineNum)
 Dim intRowCount, intItemColNum, intRowIndex, strLineValue, intFindItemNumIndex, strItemNum
 fun_GetLotRowNumberWithoutDetails = ""
 intRowCount = ip_objLeftGrid.GetROProperty("rows")
 intItemColNum = funGetColumnNumberFromJavaTable(ip_objLeftGrid, "Item", 0, False)
 If Trim(Cstr(intItemColNum)) = "" Then
  Fun_ReportFailure "Validate Item column", "Unable to find column with name Item", Browser("Browser Main")
  fun_GetLotRowNumberWithoutDetails = ""
  Exit Function
 End If
 For intRowIndex = 0 To intRowCount - 1
  strLineValue = ip_objLeftGrid.GetCellData(intRowIndex, intItemColNum)
  If Trim(strLineValue) = Trim(ip_strLineNum) Then
    For intFindItemNumIndex = intRowIndex - 1 To 0 Step -1
      strItemNum = Trim(ip_objLeftGrid.GetCellData(intFindItemNumIndex, intItemColNum))
      If Len(strItemNum) = 7 Then
       If Trim(strItemNum) = Trim(ip_strItemNum) Then
        fun_GetLotRowNumberWithoutDetails = intFindItemNumIndex
       Else
        Exit For
       End If
      End If               
    Next    
  End If 
 Next
End Function

'**********************************************************************************************************
'Function Name:   Fun_SelectingRows
'Purpose :           This Function will Select Rows depending on Line Item,Lot Number,Warehouse Number, Source DI
'Parameters:      ip_objLeftGrid: Object Left Table which contains Item Lot information
'      ip_objRightGrid: Object Right Table which contains Source DI and WarehouseDetails
'      ip_strItemsToBeSelected: Expected Item Numbers to be selected EX: "5230304|L|2965"
'      ip_strSourceDI: Expected Source DI numbers to be Selected EX: "77793247-00"
'Return  value       "": If Fails
'       ROw number: If succeeds in finding a row
'Example:           Fun_SelectingRows = fun_GetLotRowNumberWithoutDetails(objLeftGrid,ip_objRightGrid, "5230304##9764|L|2965", "77793247-00")
'**********************************************************************************************************
Function Fun_SelectingRows(ByRef ip_objLeftGrid, ByRef ip_objRightGrid, ip_strItemsToBeSelected, ip_strSourceDI)
 Dim strMatchedRowCollection,arrSourceDINumCollection,intItemIndex,arrItemCollection,arrItemDetails
 Dim arrItemWarehouseDetails,intTryIndex,strUpdatedSourceDIValue,intMatchedRow,arrSourceDI
 strMatchedRowCollection = ""
 '<<< Select items based on the Source DI also along with item no and line no>>>
 arrItemCollection = Split(ip_strItemsToBeSelected, ",")
 If Trim(ip_strSourceDI) <> "" Then
   arrSourceDINumCollection = Split(ip_strSourceDI, ",")
 End If 
 For intItemIndex = 0 To UBound(arrItemCollection)   
   arrItemDetails = Split(arrItemCollection(intItemIndex), "|")
   If Instr(arrItemDetails(0), "##") Then
     arrItemWarehouseDetails = Split(arrItemDetails(0), "##")
     If Trim(ip_strSourceDI) <> "" Then
       '<<< Getting Row Number with Warehouse Details and  Source DI>>>
       '<<< Ignoring the -00, -01, -02 -03 >>>
       arrSourceDI = Split(arrSourceDINumCollection(intItemIndex), "-")
       For intTryIndex = 0 To 3
        strUpdatedSourceDIValue = arrSourceDI(0) & "-0" & CStr(intTryIndex)       
        intMatchedRow = fun_GetLotRowNumberWithDetails(ip_objLeftGrid, ip_objRightGrid, arrItemWarehouseDetails(0), arrItemDetails(2), "PO Whse #|Source DI #", arrItemWarehouseDetails(1) & "|" & strUpdatedSourceDIValue)
        If CStr(intMatchedRow) <> "" Then
         '<<< Indicates expected row with source DI is found >>>
         Exit For
        End If
       Next
     Else
       '<<< Getting Row Number with Warehouse Details and  without Source DI>>>
       intMatchedRow = fun_GetLotRowNumberWithDetails(ip_objLeftGrid, ip_objRightGrid, arrItemWarehouseDetails(0), arrItemDetails(2), "PO Whse #", arrItemWarehouseDetails(1))
     End If
   Else
     '<<< Getting Row Number without Warehouse Details with Source DI>>>
     If Trim(ip_strSourceDI) <> "" Then
       '<<< Ignoring the -00, -01, -02 -03 >>>
       arrSourceDI = Split(arrSourceDINumCollection(intItemIndex), "-")
       For intTryIndex = 0 To 3
        strUpdatedSourceDIValue = arrSourceDI(0) & "-0" & CStr(intTryIndex)       
        intMatchedRow = fun_GetLotRowNumberWithDetails(ip_objLeftGrid, ip_objRightGrid, arrItemDetails(0), arrItemDetails(2), "Source DI #", strUpdatedSourceDIValue)
        If CStr(intMatchedRow) <> "" Then
         '<<< Indicates expected row with source DI is found >>>
         Exit For
        End If
       Next
     Else
       '<<< Getting Row Number without Warehouse Details and Source DI>>>      
       intMatchedRow = fun_GetLotRowNumberWithoutDetails(ip_objLeftGrid, arrItemDetails(0), arrItemDetails(2))
     End If
   End If
   If CStr(intMatchedRow) <> "" Then
    ip_objLeftGrid.SelectCell intMatchedRow, 0
    strMatchedRowCollection = strMatchedRowCollection & "|" & Cstr(intMatchedRow)
   Else
    '<<< Expected Row with Lot and Source DI details not found >>>
    Fun_ReportFailure "Validate if Row with Lot and Source DI details found", "Validate if Row with Lot and Source DI details found", Browser("Browser Main")
    ExitComponentIteration
   End If
 Next
 Fun_SelectingRows = Right(strMatchedRowCollection, Len(strMatchedRowCollection) - 1)
End Function

QTP & QC->DB->Code for funcitons to connect to Database and exexute query.

'**********************************************************************************************************
'Function Name:   fun_OpenDBConnection(ip_objDBConnection, ip_strConnectionString)
'Description :        Used to connect to the database based upon the connection string passed
'Parameters:        ip_objDBConnection: Connection object which will be used for db connection
'        ip_strConnectionString : Actual connection string
'Return  value       0 : Indicates the successful deleteion of the file
'        Non Zero Vale: Indicates the failure and contains the error description for the failure  
'Example:            fun_OpenDBConnection(objDBConnection, "Driver={Microsoft ODBC for Oracle};Server=NALXTST2;Uid=ab4p_app01;Pwd=ab4p_app01_bibtb;")
'**********************************************************************************************************
Public Function fun_OpenDBConnection(ip_objDBConnection, ip_strConnectionString)
   On Error Resume Next
   ip_objDBConnection.ConnectionString= ip_strConnectionString
   ip_objDBConnection.Open
    If Err.Description <> "" Then
     '<<< Return the error code along with the description if function is unable to make connection >>>
     fun_OpenDBConnection = Err.Description & VBNewLine &  Err.source
     Err.Clear
     Exit Function
   Else
    '<<< Return 1 if function is able to make connection successfully >>>
    fun_OpenDBConnection = ip_objDBConnection.State
   End If  
End Function


'**********************************************************************************************************
'Function Name:   fun_ExecuteQuery(ip_objDBConnection,ip_strQuery)
'Description :        Used to execute the query to retrieve the record set
'Parameters:        ip_objDBConnection: Connection object which will be used for db connection
'        ip_strQuery : Actual query to be executed
'Return  value       Error text with error description
'          Recordset as a result of query execution
'Example:            fun_ExecuteQuery(objDBConnection, "Select * from MyTable")
'**********************************************************************************************************
Public Function fun_ExecuteQuery(ip_objDBConnection,ip_strQuery)
   On Error resume next
   Dim objRes
   '<<< Get the result set by executing the passed query >>>
    Set objRes = ip_objDBConnection.Execute(ip_strQuery)
    If Err.Description <> "" Then
     fun_ExecuteQuery = "ERROR: " & VBNewLine & Err.Description & VBNewLine &  Err.source
     Err.Clear
   Else
    '<<< ---------- Converting the Recorset to the array ---------- >>>
    Dim intTotalRows , intTotalColumns
    intTotalRows = 0
    intTotalColumns = objRes.Fields.Count - 1
    '<<< Calculating the total rows returned by the recordset >>>
    While objRes.EOF = False
      objRes.moveNext
      intTotalRows = intTotalRows + 1
    WEnd
    '<<< Forming the Array based upon t he total Rows and Total Coulns returned by the query >>>
    Dim arrCollection()
    ReDim arrCollection(CInt(intTotalRows) - 1, intTotalColumns)
   
    '<<< Get the recordset cursor to the first point >>>
    objRes.MoveFirst
    Dim intRowIndex, intColumnIndex
    intRowIndex = 0
    While objRes.EOF = False
      For intColumnIndex = 0 To objRes.Fields.Count -1  
       If  CStr(objRes.fields(intColumnIndex).Value) <> "" Then
          arrCollection(intRowIndex, intColumnIndex) = objRes.fields(intColumnIndex).Value   
       End If
       Next
      objRes.moveNext
      intRowIndex = CInt(intRowIndex) + 1
    WEnd
    '<<< Return the newly formed array >>>
    fun_ExecuteQuery = arrCollection
    objRes.Close
   End If
End Function


Example Code:-
 Set objDatabaseCon = CreateObject("ADODB.Connection")
 Set objRecordset = CreateObject("ADODB.RecordSet")

strConString="Driver={Microsoft ODBC for Oracle};Server=NALXTST2;Uid=ab4p_app01;Pwd=ab4p_app01_bibtb;")

strConnectionState =  fun_OpenDBConnection(objDatabaseCon, strConString)
    '<<< Exit the component if the connection status is not returned as 1 => connection was not successfll >>>
    If CStr(strConnectionState) <> "1" Then
     Fun_ReportFailure "Connecting to database " & vbNewLine & strConString, "Unable to make connection :" & vbNewLine & strConnectionState, Nothing
     ExitComponentIteration
    End If

    strQuery = "select * from bridge.pl2pom_logical_msg where system_id in (select system_id from bridge.pl2pom_xml_msg where correlation_id ='" & Trim(arrAllocCollection(3)) & "')"

    '<<<Collecting the result of the query in to an array>>>
    arrRecordSet =  fun_ExecuteQuery(objDatabaseCon, strQuery)
    '<<<----- Validate the return type from the function call ------ >>>
    If Not IsArray(arrRecordSet) Then
     If Instr(arrRecordSet, "ERROR") Then
      Fun_ReportFailure "Execute Query", "Unable to execute the following query due to the error :" & arrRecordSet & vbNewLine & " Query: " & strQuery, Nothing
     Else
      Fun_ReportFailure "Execute Query", "Unable to execute the following query : " & strQuery, Nothing
     End If
     ExitComponentIteration
    Else
     If UBound(arrRecordSet) < 0 Then
      Fun_ReportFailure "Execute Query", "Unable to retrieve any rows by executing the following query : " & VBNewline & strQuery, Nothing 
      ExitComponentIteration
     End If
    End If

QTP & QC ->Code for to delete the existing file at specified path

'**********************************************************************************************************
'Function Name:   fun_DeleteExistingFile(ip_strFileName)
'Description :        Used to delete the existing file at path ip_strFileName
'Parameters:        ip_strFileName: Path of the file need to be deleted
'Return  value       0 : Indicates the successful deletion of the file
'        Non Zero Vale: Indicates the failure and contains the error description for the failure  
'Example:            fun_DeleteExistingFile("C:\MyFile.xls")
'**********************************************************************************************************
Public Function fun_DeleteExistingFile(ip_strFileName)
 On Error Resume Next
 Dim objFileSystem, objFile
 Set objFileSystem = CreateObject("Scripting.FileSystemObject")
 '<<< Attempt to Delete the file only if the file exists >>>
 If (objFileSystem.FileExists(ip_strFileName)) Then
  Set objFile = objFileSystem.GetFile(ip_strFileName)
  objFile.Delete
 End If
 '<<< Any error during the Delete operation, will be captured in Err object and will be sent back to the calling the function >>>
 If Err.Description <> "" Then
   '<<< Return the error code along with the description if function is unable to delete a file >>>
   fun_DeleteExistingFile = Err.Description & VBNewLine &  Err.source
   Err.Clear
   Exit Function
 Else
  '<<< Return Zero if function is able to delete a file successfully >>>
  fun_DeleteExistingFile = 0
 End If
End Function


Example Code:-
   Parameter("p_POFilesLocation")  = "C:\EFAS_Tools\Automation\PO_Files"
   strPOFilePath = Parameter("p_POFilesLocation") & "\POMsg.txt"      
   Set objFileSys = CreateObject("Scripting.FileSystemObject")
   '<<<Delete the file if it exists in the specified path>>>
   strFileStatus= fun_DeleteExistingFile(strPOFilePath)
   If  CStr(strFileStatus) <> "0" Then
    Fun_ReportFailure "Verify the file deleted", "Text file not deleted in the specified path : ERROR : " & strFileStatus , Nothing
    ExitComponentIteration
   End If

Excel-> Functions to get the column number & Row number From Excel sheet for a paricular text

'<<<<<<Function to get the column number From Excel sheet for a paricular text>>>>
'**********************************************************************************************************
'Function Name: funGetColumnNumberInExcel(ip_objWorksheet, ip_strExpectedColumnName, ip_intRowNumber, ip_intStartColumnNumberIndex, ip_blnIsRelativeComparison)
'Description :     Used to get the column number from the Excel sheet in particular row from particular column number
'Parameters:     ip_objWorksheet: Create an object for the Excel sheet
        'ip_strExpectedColumnName: Need to enter the particular text which to you get the column number
        'ip_intRowNumber: In which row you want to start the search for the partiuclar text
        'ip_intStartColumnNumberIndex= From which column you need to start the searching for the particular text
        'ip_blnIsRelativeComparison= need to compare the full exact text or partival text, need to send the parameter as True or Fasle
'Return  value   Number: Returns the column number of the particular text
'Example:        funGetColumnNumberInExcel(objWorksheet, "Max", 4, intColumnNumber, False)
'**********************************************************************************************************
'<<<<<<Function to get the column number from the Excel sheet for a paricular text>>>>
Function funGetColumnNumberInExcel(ip_objWorksheet, ip_strExpectedColumnName, ip_intRowNumber, ip_intStartColumnNumberIndex, ip_blnIsRelativeComparison)
  '<<< Default Return value is set as empty >>>
 funGetColumnNumberInExcel = ""
 Dim strActualColumnName, intTotalColumnCount, intColumnStartIndex
 Wait 4
 intTotalColumnCount = ip_objWorksheet.UsedRange.Columns.Count
 '<<< Extract the cell value from given row number starting from the mentioned start index for column >>>
 For intColumnStartIndex = ip_intStartColumnNumberIndex To intTotalColumnCount
  strActualColumnName = ip_objWorksheet.Cells(ip_intRowNumber, intColumnStartIndex).Value
  '<<< Using Instr function for relative comparison based upon the flag value passed >>>
  If ip_blnIsRelativeComparison = True Then  
   If Instr(UCase(Trim(strActualColumnName)),UCase(Trim(ip_strExpectedColumnName))) Then
    funGetColumnNumberInExcel = intColumnStartIndex
    Exit Function
   End If
  Else
   If UCase(Trim(strActualColumnName)) = UCase(Trim(ip_strExpectedColumnName)) Then
    funGetColumnNumberInExcel = intColumnStartIndex
    Exit Function
   End If
  End If 
 Next
End Function

'<<<<<<Function to get the Row number from the Excel sheet of a paricular text>>>>
'**********************************************************************************************************
'Function Name: funGetRowNumberInExcel(ip_objWorksheet, ip_strExpectedRowValue, ip_intColumnNumber, ip_intStartRowNumberIndex, ip_blnIsRelativeComparison)
'Description :     Used to get the Row number from the Excel sheet in particular Column from particular Row number
'Parameters:     ip_objWorksheet: Create an object for the Excel sheet
        'ip_strExpectedRowValue: Need to enter the particular text which to you get the Row number
        'ip_intColumnNumber: In which Column you want to start the search for the partiuclar text
        'ip_intStartRowNumberIndex= From which Row you need to start the searching for the particular text
        'ip_blnIsRelativeComparison= need to compare the full exact text or partival text, need to send the parameter as True or Fasle
'Return  value   Number: Returns the Row number of the particular text
'Example:         funGetRowNumberInExcel(objWorksheet, "Chain Total", intStoreColumnNumber,1, True)
'**********************************************************************************************************
Function funGetRowNumberInExcel(ip_objWorksheet, ip_strExpectedRowValue, ip_intColumnNumber, ip_intStartRowNumberIndex, ip_blnIsRelativeComparison)
  '<<< Default Return value is set as empty >>>
 funGetRowNumberInExcel = ""
 Dim strActualRowValue, intTotalRowCount, intRowStartIndex
 Wait 4
 intTotalRowCount = ip_objWorksheet.UsedRange.Rows.Count
 '<<< Extract the cell value from given row number starting from the mentioned start index for column >>>
 For intRowStartIndex = ip_intStartRowNumberIndex To intTotalRowCount
  strActualRowValue = ip_objWorksheet.Cells(intRowStartIndex, ip_intColumnNumber).Value
  '<<< Using Instr function for relative comparison based upon the flag value passed >>>
  If ip_blnIsRelativeComparison = True Then  
   If Instr(UCase(Trim(strActualRowValue)),UCase(Trim(ip_strExpectedRowValue))) Then
    funGetRowNumberInExcel = intRowStartIndex
    Exit Function
   End If
  Else
   If UCase(Trim(strActualRowValue)) = UCase(Trim(ip_strExpectedRowValue)) Then
    funGetRowNumberInExcel = intRowStartIndex
    Exit Function
   End If
  End If 
 Next
End Function

QTP & QC -> Code for Select the Date from the Date Picker.

'<<< funtion will select the date from date picker based on the input data.>>>
'**********************************************************************************************************
'Function Name:  Fun_DatePicker(ip_Obj, ip_InputDate)
'Description :     Select the Date from the Date Picker on the Screen as per the User Input Date.
'Author:                Naresh Gupta. D
'Parameters:      Input date, Calendar Object
'Return  value      1 for fail
'Example:     Call Fun_DatePicker(objCalendar, Parameter("p_AR_RS_NightOfDate"))
                      '<<<Decleration of Object for Calendar>>>
       'Dim objCalendar
                      'Set objCalendar = Browser("Browser Main").Page("Run Model").Image("Calendar")
'**********************************************************************************************************
Function Fun_DatePicker(ip_Obj, ip_InputDate)
 Dim arrDateDetailsCollection, intDay, intMonth, intYear, intYearFromCalendar, intDifOfYears, intMaxWaitCount
 Fun_DatePicker = "0"
 arrDateDetailsCollection=Split(ip_InputDate, "/")
 '<<<Verifying the Month field is numeric or not>>>
 If (Not IsNumeric(arrDateDetailsCollection(0))) Then
  Fun_ReportFailure "Verify the input passed for Month", "Invalid input passed for Month field and that is : " &arrDateDetailsCollection(0), Nothing
  Fun_DatePicker = "1"
  ExitFunction
 '<<<Verifying the Month field is less than Zero or Empty or Greater than 12>>>
 ElseIf arrDateDetailsCollection(0) <= 0 Or arrDateDetailsCollection(0) = ""  Or arrDateDetailsCollection(0) > 12 Then
  Fun_ReportFailure "Verify the input passed for Month", "Invalid input passed for Month field and that is : " &arrDateDetailsCollection(0), Nothing
  Fun_DatePicker = "1"
  ExitFunction
 End If
 '<<<Verifying the Month field is numeric or not>>>
 If (Not IsNumeric(arrDateDetailsCollection(1))) Then
  Fun_ReportFailure "Verify the input passed for Date", "Invalid input passed for the Date field that is : " &arrDateDetailsCollection(1), Nothing
  Fun_DatePicker = "1"
  ExitFunction
 '<<<Verifying the Month field is less than Zero or Empty or Greater than 31>>>
 ElseIf arrDateDetailsCollection(1) <= 0 Or arrDateDetailsCollection(1) = ""   Or  arrDateDetailsCollection(1) > 31 Then
  Fun_ReportFailure "Verify the input passed for Date", "Invalid input passed for the Date field that is : " &arrDateDetailsCollection(1), Nothing
  Fun_DatePicker = "1"
  ExitFunction
 End If
 '<<<Verifying the Month field is numeric or not>>>
 If (Not IsNumeric(arrDateDetailsCollection(2))) Then
  Fun_ReportFailure "Verify the input passed for Year", "Invalid input passed for the Year filed that is : " &arrDateDetailsCollection(2), Nothing
  Fun_DatePicker = "1"
  ExitFunction
 '<<<Verifying the Month field is less than Zero or Empty>>>
 ElseIf arrDateDetailsCollection(2) <= 0 Or arrDateDetailsCollection(2) = "" Then
  Fun_ReportFailure "Verify the input passed for Year", "Invalid input passed for the Year filed that is : " &arrDateDetailsCollection(2), Nothing
  Fun_DatePicker = "1"
  ExitFunction
 End If
 intMonth=cint((arrDateDetailsCollection(0))-1)
 intDay=cint(arrDateDetailsCollection(1))
 intYear=cint(arrDateDetailsCollection(2))

 ip_Obj.Click
 Wait(intConstShortWaitTime)
 intYearFromCalendar=Browser("Calendar").Page("Calendar").Frame("topCalFrame").WebEdit("year").GetROProperty("Value")
 intDifOfYears=Trim(intYear)-Trim(intYearFromCalendar)

 '<<<If the Year in the input date is more than the present year then it will click on ">>" button and select that particular Year>>>
 If (intDifOfYears>0) then
  Do while (intDifOfYears>0)
    Browser("Calendar").Page("Calendar").Frame("topCalFrame").WebButton(">>").Click
     If intYear=  Browser("Calendar").Page("Calendar").Frame("topCalFrame").WebEdit("year").GetROProperty("Value") then
      Exit Do
    End if
   intDifOfYears=intDifOfYears-1
  Loop
 End if

 '<<<If the Year in the input date is Less than the present year then it will click on "<<" button and select that particular Year>>>
 If(intDifOfYears<0) then
  Do while (intDifOfYears<0)
    Browser("Calendar").Page("Calendar").Frame("topCalFrame").WebButton("<<").Click
     If intYear=  Browser("Calendar").Page("Calendar").Frame("topCalFrame").WebEdit("year").GetROProperty("Value") then
      Exit Do
    End if
   intDifOfYears=intDifOfYears+1
  Loop
 End if

 '<<<Select the particualr month as per the user input month>>>
 If Browser("Calendar").Page("Calendar").Frame("topCalFrame").WebList("month").Exist(intConstShortWaitTime) Then
  Browser("Calendar").Page("Calendar").Frame("topCalFrame").WebList("month").SelectIfNotNull intMonth
 End if

 '<<<Select the particualr Date as per the user input Date>>>
 Browser("Calendar").Page("Calendar").Sync
 Browser("Calendar").Page("Calendar").Frame("bottomCalFrame").Link("text:="&intDay).Click
 Browser("Calendar").Page("Calendar").Sync
 Browser("Calendar").Page("Calendar").Frame("bottomCalFrame").WebButton("Select").Click

 '<<<Do Loop for a time of  intConstShortWaitTime and get exists when the calendar window gets closed>>>
 intMaxWaitCount = intConstShortWaitTime
 Do While intMaxWaitCount > 0
  If Browser("Calendar").Page("Calendar").Exist(1) Then  
   intMaxWaitCount = CInt(intMaxWaitCount) - 1
  Else
   Exit Do
  End If
 Loop
End Function

QTP & QC-> Code for function to provide a controlled wait for synchronizing the application til the expected objects gets displayed in the screen & in exists

'****************************************************************************************************************************
'Function Name : Fun_WaitForObjectExists
'Description   This function is used to provide a controlled wait for synchronizing the application til the expected objects gets displayed in the screen
'Parameters      ip_obj : Expected object     
'         ip_MaxWaitCount : Total max wait * 2 time in sec for the expected object to get displayed
'Return Val ue     0: indicates success
'       1: indicates failure
' Ex:ample    Fun_WaitForObjectExists(ip_obj, 20)
'****************************************************************************************************************************
Function Fun_WaitForObjectExists(ip_obj, ip_MaxWaitCount)
  Fun_WaitForObjectExists = "1" 'Indicates Failure 
  Wait intConstShortWaitTime '<<< --- This wait is reuired to synch the application browser --- >>>

  '<<< ------- Keep looping until the object is found to be existing in the application -------- >>>
  Do While CInt(ip_MaxWaitCount) > 0
   If ip_obj.Exist(intConstShortWaitTime) Then    
    Fun_WaitForObjectExists = "0" '<<< --- Set the flag as success and exit the loop ----- >>>
    Exit Do
   Else
    '<<< --------- Decriment the counter by 1 and loop back -------- >>>>
    Wait intConstShortWaitTime
    ip_MaxWaitCount  = CInt(ip_MaxWaitCount ) - 1    
   End If
  Loop

  '<<< ------ If the flag is found to be still under initial status it indicates the failure of the object identification ------ >>>
  If Fun_WaitForObjectExists = "1" Then
    Fun_ReportFailure "Application sync event" , "Expected Object is not displayed in the screen within the given wait time", Browser("Browser Main")
   End If
End Function
'****************************************************************************************************************************
'Function Name : Fun_WaitForObjectInExists
'Description   This function is used to provide a controlled wait for synchronizing the application til the expected objects gets disappears from the screen
'Parameters      ip_obj : Expected object     
'         ip_MaxWaitCount : Total max wait * 2 time in sec for the expected object to get displayed
'Return Val ue     Null: If expected object gets displayed
'        Exists the component iteration if expected object does not disappears 
' Ex:ample    Fun_WaitForObjectInExists(ip_obj, 20)
'****************************************************************************************************************************
Function Fun_WaitForObjectInExists(ip_obj, ip_MaxWaitCount)
   Dim blnFlag
   blnFlag = "1"
   Wait intConstShortWaitTime '<<< --- This wait is reuired to synch the application browser --- >>>
   '<<< ------- Keep looping until the object is found to be inexisting in the application -------- >>>
   Do While CInt(ip_MaxWaitCount) > 0
    If ip_obj.Exist(intConstShortWaitTime) Then    
     '<<< --------- Decriment the counter by 1 and loop back -------- >>>>
     Wait intConstShortWaitTime
     ip_MaxWaitCount  = CInt(ip_MaxWaitCount ) - 1     
    Else    
     blnFlag = "0"  '<<< --- Set the flag as success and exit the loop ----- >>>
     Exit Do   
    End If
   Loop
   '<<< ------ If the flag is found to be still under initial status it indicates the failure of the object identification ------ >>>
   If blnFlag = "1" Then
     Fun_ReportFailure "Application synch event" , "Expected Object does not inexist in the screen within the given max wait time", Browser("Browser Main")
            End If
   Fun_WaitForObjectInExists = blnFlag
End Function