Using Quality center api to get Bug/Defect history by a Macro in Excel
Here is the code that will get you the bug history of a particular defect in Quality center..
1)To run this code ,Open Excel and Press ALT+F11 to go to Visual Basic Editor and paste this code .
2)Replace the url with your QC url
3)Replace username and password with your own username and password
Dim QCConnection
Set QCConnection = CreateObject(”TDApiOle80.TDConnection”)
QCConnection.InitConnectionEx “http://……./qcbin/”
QCConnection.login “username”, “password”
QCConnection.Connect “xxxxxxx”, “xxx”
Dim Bug, Row
Dim i As Integer
Dim k As Integer
‘ Get a list of all the defects.
Dim BugFactory, BugList
Set BugFactory = QCConnection.BugFactory
Set bugfilter = BugFactory.Filter
bugfilter.Filter(”BG_BUG_ID”) = “3442″
Set bglist = bugfilter.NewList
‘ Open a new Excel worksheet.
Row = 2
For Each Bug In bglist
‘a = bglist.Field(”BG_STATUS”)
”Dim cellC2 ‘ set the date
Cells(1, 1).Select
‘cellA1 = “=TODAY()” ‘ActiveCell.Value ‘ set date from the spreadsheet value
‘Range(”A2:H200″).Select ‘ clear the content of the spreadsheet
‘Selection.ClearContents ‘ clear the content of the spreadsheet
‘Range(”A2″).Select ’setup the first cell of a selected range
For i = 1 To bglist.Count
‘ActiveCell.Offset(1, 0).Select
Set BG = bglist.Item(i)
Set hst = BG.History
Set hstList = hst.NewList(”")
‘ActiveCell.Formula = (bg.ID)
‘ActiveCell.Offset(0, 1).Select
‘ActiveCell.Formula = (”Last Assigned To” & ” - ” & bg.AssignedTo)
For j = 1 To hstList.Count
Set hstRec = hstList.Item(j)
If hstRec.FieldName = “BG_USER_04″ Then
Sheet1.Cells(j, 1) = hstRec.Changer
Sheet1.Cells(j, 2) = hstRec.FieldName
Sheet1.Cells(j, 3) = hstRec.ChangeDate
Sheet1.Cells(j, 4) = hstRec.NewValue
Sheet1.Cells(j, 5) = 1
‘Sheet1.Cells(j, 6) = a
Else
End If
Next j
Next i
l = 1
For k = 1 To j
a = Sheet1.Cells(k, 5)
If a = 1 Then
Sheet2.Cells(l, 1) = Sheet1.Cells(k, 1)
Sheet2.Cells(l, 2) = Sheet1.Cells(k, 2)
Sheet2.Cells(l, 3) = Sheet1.Cells(k, 3)
Sheet2.Cells(l, 4) = Sheet1.Cells(k, 4)
l = l + 1
Else
End If
Next k
Rows(”2:200″).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
‘Cells.Interior.Color = yellow
Range(”A5″).Select
End With
ActiveSheet.UsedRange.Columns.AutoFit
Next
You can use this code to get a list of reopened and rejected defects in Quality center..