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.