OTA : Test Case Upload in QC

Public oWorkbook
Public oSheet
Public sWorkbook
Public intShtCnt
Public wrkbk
Public intArrCnt
Public wrkbk1
Public strTestCaseDescription
Public strDesSteps(10)
Public StrExpRes(10)
Public strStepCnt(10)
Public iRowCnt2
Public iRowCnt1
Public strExcelPath
Public strTestCaseName

Public strConfg(10)
Public td
Public tf
Public tstCase
Public dsf
Public ds

iRowCnt1 = 2
iRowCnt2 = 8

'*****************************************************************************************************
' Create the Excel Object required for reference
'*****************************************************************************************************'

public Function create()
Set app = createobject ("Excel.Application")
Set create = app
End Function

'*****************************************************************************************************
' Open the Excel File using the object created
'*****************************************************************************************************'

Public Function open()
Set wrkbk = create.Workbooks.Open(strExcelPath)
Set oSheet = wrkbk.WorkSheets
Set open = wrkbk
Set oSheet = Nothing
Set wrkbk = Nothing
End Function



'*****************************************************************************************************
'Kill all the open Excel Process
'*****************************************************************************************************'

Public Function Kill_Executable(Exe_Name)
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = '"&Exe_Name&"'")
For Each objProcess in colProcessList
objProcess.Terminate()
Next
Set colProcessList = Nothing
Set objWMIService = Nothing
End Function


'*****************************************************************************************************
'Terminate all the object created
'*****************************************************************************************************'

Public Function Class_Terminate()

Set app = Nothing
Set oSht = Nothing
Set oSheet = Nothing
Set wrkbk = Nothing
Call Kill_Executable("EXCEL.EXE")


End Function

'*****************************************************************************************************
'Read data from excel file and create an input array with all expected results
'*****************************************************************************************************'

Public Function readFromExcel()
strExcelPath = strConfg(5)
intArrCnt = 0
Dim strStepValue
Set oSht = open.Sheets(2)
strTestCaseName = (oSht.Cells(iRowCnt1,2))
Do while strTestCaseName <> ""
strTestCaseDescription = (oSht.Cells(3,2))
strTestCasePath = (oSht.Cells(4,2))
strStepValue = (oSht.Cells(iRowCnt2,1))
Do while strStepValue <> ""
strStepCnt(intArrCnt) = (oSht.Cells(iRowCnt2,1))
strDesSteps(intArrCnt) = (oSht.Cells(iRowCnt2,2))
StrExpRes(intArrCnt) = (oSht.Cells(iRowCnt2,3))
iRowCnt2 = iRowCnt2 + 1
intArrCnt = intArrCnt + 1
strStepValue = (oSht.Cells(iRowCnt2,1))
Loop
Set oSht = Nothing
Exit Do
Loop
End Function


'*****************************************************************************************************
'Read data from text file regarding the QC URL and Login Details and Excel Folder Path
'*****************************************************************************************************'

Function readTextFile()
Const ForReading = 1
Dim i
Dim strTextArr
i = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
("c:\testTq.txt", ForReading)
Do Until objTextFile.AtEndOfStream
strTextVal = objTextFile.Readline
strTextArr = Split(strTextVal,"=")
strConfg(i) = trim(strTextArr(1))
i = i + 1
Loop
End Function


'*****************************************************************************************************
'Upload the array elemenrts obtained from the Excel into the Quality Center.
'*****************************************************************************************************'

Function uploadQC()

Set td=createobject("TDApiOle80.TDConnection.1")
td.InitConnectionEx strConfg(0)
td.ConnectProjectEx strConfg(1),strConfg(2),strConfg(3),strConfg(4)

Set tstMgr = td.TreeManager
Set tsttr = tstMgr.NodeByPath(strTestCasePath)

'Creste testFactory object
Set tsetFact = tsttr.TestFactory

'Create new test with the sheet name
Set tstCase = tsetFact.AddItem(strTestCaseName)
tsetFact.Field("TS_DESCRIPTION") = strTestCaseDescription
'Post the new test case
tstCase.Post

'Create design steps factory object
Set dsf = tstCase.DesignStepFactory
'Iterate for the number of steps in the excel sheet
For m = 1 to intArrCnt-1

'Add individual steps into design steps along with step number, step description and expected result
Set ds = dsf.AddItem(Null)

ds.Field("DS_STEP_NAME") = strStepCnt(m)
ds.Field("DS_DESCRIPTION") =strDesSteps(m)
ds.Field("DS_EXPECTED") = StrExpRes(m)
ds.Post

Next

End Function


'*****************************************************************************************************
'Function Calls to Upload manual test cases from excel into QC
'*****************************************************************************************************'

Call readTextFile()
Call Class_Terminate()
Call readFromExcel()
Call Class_Terminate()
Call uploadQC()