How to pass values between different tests in QTP

[Brian.Osborne]

I was able to get this accomplished by using excel.
Inputs:
1. I needed a random customer(s) that had a minimum FICO score for 3 different lines of business. Each business would return a different FICO in our test environment.

Outputs: During the QTP script, I would capture various information for use later.

1. FICO. I would update the 3 columns of the spreadsheet with a new value in case the FICO has changed since it was last pulled.
2. Application Number. This is submitting an application for a loan for these customers and I want to capture this number for downstream testing.

I then just run the next QTP script pointing to the Application numbers sheet I just captured and I am able to use outputs from one QTP script as input for another. And the data is available for viewing and review later.

Here is the code:

Code:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.WorkBooks.Open("Z:\Path\Test.xlsx")
Set objDriverSheet = objWorkbook.Worksheets("FirstStep")
BusinessLine = DataTable("BUS", dtGlobalSheet)
NumberApplicants = DataTable("APPLICANTS", dtGlobalSheet)
Do Until FICO1 > 700
    randomize
    upperbound = 1206    
    lowerbound = 2
    Row1 = Int(( upperbound - lowerbound + 1 ) * Rnd + lowerbound )
    If BusinessLine = "PCM" Then
        FICO1 = objDriverSheet.Cells(Row1, "B").Value
    ElseIf BusinessLine = "HE" Then
        FICO1 = objDriverSheet.Cells(Row1, "C").Value
    ElseIf BusinessLine = "WFAF" Then
        FICO1 = objDriverSheet.Cells(Row1, "D").Value
    End If
Loop
If NumberApplicants = 2 Then
    Do Until FICO2 > 700
        Row2 = Int(( upperbound - lowerbound + 1 ) * Rnd + lowerbound )
        If BusinessLine = "PCM" Then
            FICO2 = objDriverSheet.Cells(Row2, "B").Value
        ElseIf BusinessLine = "HE" Then
            FICO2 = objDriverSheet.Cells(Row2, "C").Value
        ElseIf BusinessLine = "WFAF" Then
            FICO2 = objDriverSheet.Cells(Row2, "D").Value
        End If
    Loop
End If
DataTable("FICO1", dtGlobalSheet) = FICO1
DataTable("FICO2", dtGlobalSheet) = FICO2
DataTable("Row1", dtGlobalSheet) = Row1
DataTable("Row2", dtGlobalSheet) = Row2

'This is where the script would capture the FICOs and App number normally - it has been removed for testing
FICO1 = DataTable("NewFICO1", dtGlobalSheet)
FICO2 = DataTable("NewFICO2", dtGlobalSheet)
APPNUMBER = DataTable("NewAppNumber", dtGlobalSheet)

If BusinessLine = "PCM" Then
    objDriverSheet.Cells(Row1, "B").Value = FICO1
ElseIf BusinessLine = "HE" Then
    objDriverSheet.Cells(Row1, "C").Value = FICO1
ElseIf BusinessLine = "WFAF" Then
    objDriverSheet.Cells(Row1, "D").Value = FICO1
End If
If NumberApplicants = 2 Then
    If BusinessLine = "PCM" Then
        objDriverSheet.Cells(Row2, "B").Value = FICO2
    ElseIf BusinessLine = "HE" Then
        objDriverSheet.Cells(Row2, "C").Value = FICO2
    ElseIf BusinessLine = "WFAF" Then
        objDriverSheet.Cells(Row2, "D").Value = FICO2
    End If
End If
objWorkbook.Save
objWorkbook.Close
objExcel.quit
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.WorkBooks.Open("G:\Path\Test.xlsx")
Set objDriverSheet = objWorkbook.Worksheets("SecondStep")
APPSPACE = 1
APPROW = 1
Do Until APPSPACE = ""
    DataTable("APPSPACE", dtGlobalSheet) = objDriverSheet.Cells(APPROW, "A").Value
    APPSPACE = DataTable("APPSPACE", dtGlobalSheet)
    APPROW = APPROW + 1
Loop
APPROW = APPROW - 1
objDriverSheet.Cells(APPROW, "A").Value = APPNUMBER
objWorkbook.Save
objWorkbook.Close
objExcel.quit

This may seem like a lot, but I tried a lot of other solutions and none would do what I wanted consistently and without error.
I was using a SharePoint site to house the excel doc, but that caused too many issues with checking the doc in, saving, etc..

Hope this helps.

Brian