Download defects from QC to Excel using QC OTA

Dim tdc
Set tdc = CreateObject("TDApiOle80.TDConnection")

Dim sUserName, sPassword
sUserName = "" '<-- br="" change="" me.="">sPassword = "" '<-- br="" change="" me.="">

Dim sDomain, sProject
sDomain = ""   '<-- br="" change="" me.="">sProject = "" '<-- change="" me.="" p="">
tdc.InitConnectionEx "http://qc..com/qcbin" '<-- br="" change="" url="">tdc.Login sUserName, sPassword
tdc.Connect sDomain, sProject

If (tdc.LoggedIn <> True) Then
    MsgBox "QC User Authentication Failed"
    'WScript.Quit
End If

Fn_Defects_To_Excel()

'========================================
 'To download defects from QC to excel sheet
'=========================================
Sub Fn_Defects_To_Excel()
Set BugFact = tdc.BugFactory

'If want to search defects with filter criteria, use below
'Set BugFilter = BugFact.Filter
'BugFilter.Filter("BG_STATUS") = "New"
'BugFilter.Order("BG_PRIORITY") = 1
'Set BugList = BugFilter.NewList("")

Set BugList = BugFact.NewList("")
'msgbox BugList.count
ExcelPath = ".xls"
'Set Excel = FnCreateExcel(ExcelPath)
Set Excel = CreateObject("Excel.Application") 'Open Excel
Excel.WorkBooks.Add() 'Add a new workbook
'Get the first worksheet.
Set Sheet = Excel.ActiveSheet
'Set Sheet = Excel.ActiveWorkbook.Worksheets(1)
Excel.DisplayAlerts  = False
'Set Sheet = Excel.workbooks.open(ExcelPath).Worksheets(1)

Sheet.Cells(1, 1) = "Bug ID"
Sheet.Cells(1, 2) = "Summary"
Sheet.Cells(1, 3) = "Description"
Excel.Columns(3).ColumnWidth = 60
Sheet.Cells(1, 4) = "Priority"
Sheet.Cells(1, 5) = "Status"
Sheet.Cells(1, 6) = "Detected By"
Sheet.Cells(1, 7) = "Responsibility"

Row = 2
For Each Bug in BugList
Sheet.Cells(Row, 1) = Bug.Field("BG_BUG_ID")
Sheet.Cells(Row, 2) = Bug.Field("BG_SUMMARY")
Description = FnFormatHTML(Bug.Field("BG_DESCRIPTION"))
Sheet.Cells(Row, 3) = Trim(Description)
'Sheet.Cells(Row+1, 3) = Bug.Field("BG_DESCRIPTION")
Sheet.Cells(Row, 4) = Bug.Field("BG_PRIORITY")
Sheet.Cells(Row, 5) = Bug.Field("BG_STATUS")
Sheet.Cells(Row, 6) = Bug.Field("BG_DETECTED_BY")
Sheet.Cells(Row, 7) = Bug.Field("BG_RESPONSIBLE")
Row = Row + 1
Next

Excel.ActiveSheet.UsedRange.EntireColumn.Autofit()
'Excel.workbooks.Save
Excel.ActiveWorkbook.SaveAs ExcelPath
Excel.workbooks.Close
Set Sheet = Nothing
Set Excel = Nothing
MsgBox "Done!"
End Sub

'===========================================
'Formats an HTML text to normal String
'==========================================
Function FnFormatHTML(strHTML)
    'Strips the HTML tags from strHTML
    Dim objRegExp, strOutput
    Set objRegExp = New Regexp
    objRegExp.IgnoreCase = True
    objRegExp.Global = True
    objRegExp.Pattern = "<(.|\n)+?>"
    'Replace all line breaks with VB line breaks
    strOutput = Replace(strHTML, "

", vbLf)
    strOutput = Replace (strOutput ,"
",vbLf)
    'Replace all HTML tag matches with the empty string
    strOutput = objRegExp.Replace(strOutput, "")
    'Replace all <, >, and " with <, >, and "
    strOutput = Replace(strOutput, "<", "<")
    strOutput = Replace(strOutput, ">", ">")
    strOutput = Replace(strOutput, """, Chr(34))
    strOutput= Replace( strOutput,"",Chr(8))
    strOutput = Replace(strOutput,"",Chr(8))
    strOutput = Replace(strOutput," ","")
    strOutput= Replace(strOutput,"
-",chr(13)& "-")
    strOutput = Replace (strOutput ,"
" ,"")
    strOutput = Replace (strOutput,"" ,"")
    'Replaces double blank lines to blank, but cant replace one single blank line
    strOutput = Replace (strOutput,vbLf&vbLf,"")
    strOutput = Replace (strOutput,chr(13),"")
    Set objRegExp = Nothing
    FnFormatHTML = strOutput    'Return the value of strOutput
End Function


'Note: I have listed few fields from the defect tab of QC, here are some fields from QC defect tab which you may need -
'BG_BUG_ID, BG_STATUS, BG_RESPONSIBLE, BG_PROJECT
'BG_SUMMARY, BG_DESCRIPTION, BG_SEVERITY, BG_SUBJECT
'BG_PRIORITY, BG_DETECTED_BY, BG_DETECTION_DATE, BG_CLOSING_DATE

ActiveX component can't create object - TDApiOle80.TDConnection

Some times we get error as "Windows7 64 bit - ActiveX component can't create object TDApiOle80.TDConnection" while running Quality Center related VBScript files on WIndows 7, 64Bit machine...




If you are on 64bit machine, by default QC OTA API script will run with the 64 bit version of wscript.exe.
But QC is a 32 bit app, so you need to run it from a 32 bit version of wscript.exe which is located at c:\Windows\SysWOW64\wscript.exe.

You can run the .vbs file from the start -> run as-
c:\Windows\SysWOW64\wscript.exe "path of QC script file\.vbs"

can't save the mapping name while exporting data from excel to quality canter(windows 7 OS)

1. Ensure that User control accounts setting is disabled.

in the Windows globe>search for program and files> type UAC click on the link to bring up the UAC.

Move the slider down to never notify.

Restart the computer.

2. Disable Data Execution prevention (DEP) In the Windows globe>search for program and files > type CMD Right click on the link and run as administrator.

In the command prompt type: bcdedit /set {current} nx AlwaysOff 

Restart the computer.

 Now you can open/browse quality center and try exporting the excel sheet and you can save the mapping name too without fail.

CTDServer error while exporting test cases from excel sheet to Quality Center

A) You need to uninstall QC Excel add-in first from Add/Remove Programs(only if it is installed on your machine).
B) Install QC Excel Add-in along with QC Connectivity Add-in & QC Client Registration.
NOTE -
1) You can download & install these add-ins from below URL’s.
2) For client registration, right click on the Internet Explorer icon and click on run as administrator and open the add ins page to register the QC ALM.
3) the URL for add-ins page is - http://qc/qcbin