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....

[jsknight1969]

You still need to set your object. It's a variant type by default which will not have a .Cells method.

Code:

Function WriteExcelSht (excelObjIn)

    On Error Resume Next

    Dim excelObj
    [color=#FF4500]Set excelObj = CreateObject("Excel.Application")[/color]
    excelObj = excelObjIn

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

[brianMooney]

Beautiful stuff thanks, I'll try that....

---

Still seems to be giving the same error as previously stated.

[jsknight1969]

here is you script and it works

Code:

DoTheBizz "c:\test.xls"

Function DoTheBizz (path)
    On Error Resume Next
    Dim funcReturn
    Dim savePath

    savePath = path

     Set funcReturn = CreateExcelSht    '<====Changed this
    
    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
    Set CreateExcelSht = excelObj   '<====changed this

End Function


Function WriteExcelSht (ByVal excelObjIn)

    On Error Resume Next

    Dim excelObj
    
    Set excelObj = excelObjIn   '<===Changed this
    
    MsgBox "In WriteExcelSht, excelObjIn is: " & excelObjIn & " and excelObj (the function version) is: " & excelObj

    excelObj.Visible = true
    excelObj.Workbooks.Add
    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

In my previous post, I said you had to set your object type, which is correct in some scenerios/languages, but in this one you just needed a few Set commands to pass the object ByVal instead of ByRef (default).

---

oops left some test code in the script.

Code:

'excelObj.Visible = true
    'excelObj.Workbooks.Add
    excelObj.Cells(1,1).Value = "Random Schtuff"

please comment or remove the extra visible and Add lines. The code still works without errors.