Copy all QC defects to an Excel Sheet

'***********************************************************************
'Purpose: This script copies all QC defects into an Excel file
'This example you can run from .vbs run-time or from QTP.
'Scripted by: Uday
'***********************************************************************

QCServerURL="Server Name"
QCUserLogInName="User Name"
QCUserPwd="User Password"
QCDomain="Domain Name"
QCProject="Project Name"

Set QCConnObj=createobject("TDApiole80.TDConnection")

'These are the functions for Connect to QC, Copy defects to Excel and Disconnect from QC.
ConnectToQCProj()
WriteDefectsIntoExcel()
DisConnectProj()

Public Function ConnectToQCProj
'User Login to QC
QCConnObj.InitConnectionEx QCServerURL
QCConnObj.Login QCUserLogInName,QCUserPwd

If QCConnObj.LoggedIn<>TRUE Then
msgbox "QC User Authentication has failed"
WScript.Quit
End If

'User connect to QC
QCConnObj.Connect QCDomain,QCProject
End function


Public function WriteDefectsIntoExcel()

Set BugFactoryObj=QCConnObj.BugFactory
Set BugList=BugFactoryObj.NewList("")

Set XLObj=createobject("Excel.application")
XLObj.Workbooks.Add()
Set XLSheet=XLObj.ActiveSheet


XLSheet.Cells(1, 1).Value = "Bug Id"
XLSheet.Cells(1, 2).Value = "Summary"
XLSheet.Cells(1, 3).Value = "Detected By"
XLSheet.Cells(1, 4).Value = "Priority"
XLSheet.Cells(1, 5).Value = "Status"
XLSheet.Cells(1, 6).Value = "Assigned To"

RowNo=2

For each bug in BugList
XLSheet.Cells(RowNo, 1).Value = Bug.Field("BG_BUG_ID")
XLSheet.Cells(RowNo, 2).Value = Bug.Summary
XLSheet.Cells(RowNo, 3).Value = Bug.DetectedBy
XLSheet.Cells(RowNo, 4).Value = Bug.Priority
XLSheet.Cells(RowNo, 5).Value = Bug.Status
XLSheet.Cells(RowNo, 6).Value = Bug.AssignedTo
RowNo=RowNo+1
Next

XLObj.ActiveWorkbook.SaveAs("C:\QC_Defects.xls")
XLObj.Quit

Set XLObj=nothing

End Function



Public Function DisConnectProj()
QCConnObj.Disconnect
QCConnObj.Logout
QCConnObj.ReleaseConnection
Set QCConnObj=Nothing
End function