Pass Object Between Functions

[brianMooney]

Hi

I am tring to setup some Excel reporting around the automation framework in QTP and I am having some issues. Maybe someone can lend a hand?

I've setup the reporting but want to seperate it into three seperate functions so as to create an excel file, then write to it with a seperate function, then close it with a seperate function. My issue seems to be around successfully passing the object from one function to another.

I have attached the scripted section below and given a brief secription of the issue. Maybe its possible that someone could shine some light on what I'm doing wrong. Apologies in advance, I'm not overly familiar with vbScript or development of scripts for QTP.

Code:

Function DoTheBizz (path)

    On Error Resume Next
    
    Dim funcReturn
    Dim savePath

    savePath = path

    funcReturn = CreateExcelSht

    MsgBox "funcReturn is: " & funcReturn & " and savePath is: " & savePath

    Call WriteExcelSht(funcReturn)

    Call CloseExcelSht(funcReturn, savePath)

End Function


Function CreateExcelSht

    On Error Resume Next

    Dim excelObj
    Dim msg, title

    title = "Excel Reporting"

    Set excelObj = CreateObject("Excel.Application")
    If (Err.Number <> 0) Then
        MsgBox "Error in Create Object, Number: " & Err.Number & ". Desc: " & Err.Description & " and source: " & Err.source
        Err.Clear
        msg = "Obj set to: " & excelRptSht & " and failed"
        Reporting msg, title, micFail, DETAIL
    End If

    excelObj.Visible = True
    If (Err.Number <> 0) Then
        MsgBox "Error in show excel, Number: " & Err.Number & ". Desc: " & Err.Description & " and source: " & Err.source
        Err.Clear
        msg = "Excel visibility is: " & excelObj.Application.Visible & " and failed"
        Reporting msg, title, micFail, DETAIL
    End If

    excelObj.Workbooks.Add
    If (Err.Number <> 0) Then
        MsgBox "Error in create Workbook, Number: " & Err.Number & ". Desc: " & Err.Description & " and source: " & Err.source
        Err.Clear
        msg = "Excel workbook create"
        Reporting msg, title, micFail, DETAIL
    End If

    MsgBox "In CreateExcelSht, excelObj is: " & excelObj
    CreateExcelSht = excelObj

End Function


Function WriteExcelSht (excelObjIn)

    On Error Resume Next

    Dim excelObj
    
    excelObj = excelObjIn

    MsgBox "In WriteExcelSht, excelObjIn is: " & excelObjIn & " and excelObj (the function version) is: " & excelObj

    excelObj.Cells(1,1).Value = "Random Schtuff"
    If (Err.Number <> 0) Then
        MsgBox "Error in write to excel: " & Err.Number & ". Desc: " & Err.Description & " and source: " & Err.source
        Err.Clear
        msg = "Error trying to write"
        Reporting msg, title, micFail, DETAIL
    End If

End Function

Function CloseExcelSht (excelObj, savePath)

    excelObj.ActiveWorkbook.SaveAs savePath
    If (Err.Number <> 0) Then
        MsgBox "Error in save of excel: " & Err.Number & ". Desc: " & Err.Description & " and source: " & Err.source
        Err.Clear
        msg = "Error trying to save"
        Reporting msg, title, micFail, DETAIL
    End If

    excelObj.ActiveWorkbook.Close
    If (Err.Number <> 0) Then
        MsgBox "Error in quit of excel: " & Err.Number & ". Desc: " & Err.Description & " and source: " & Err.source
        Err.Clear
        msg = "Error trying to quit"
        Reporting msg, title, micFail, DETAIL
    End If

    Set excelObj = Nothing

End Function

Basically the issues I'm having is that when I get to the, "excelObj.Cells(1,1)...." line, I am getting a 424 error message, stating Object required. Although the MsgBox's do show the excelObjIn and the excelObjequal to the same value....