How to export HP Quality Center test cases to excel

[Blair1000]

We have many test cases in Quality Center 11.0. How to export HP Quality Center test cases to excel file?

Any one can help us to do this?

Thanks

I find a query is listed below, but I get error "OLE error 800403EA"

SELECT
DS_STEP_NAME as Step_Name,
DS_DESCRIPTION as Description,
DS_EXPECTED as Expected,
TS_NAME as Testname,
TS_SUBJECT as Subject,
TS_USER_04 as Application,
TS_Status as Status,
TS_TEST_ID as Test_ID,
TS_USER_21 as User_Group,
TS_RESPONSIBLE as Designer,
TS_DESCRIPTION as Details_DESCRIPTION

FROM DESSTEPS, TEST
WHERE DS_TEST_ID = TS_TEST_ID (+)

AND TS_NAME = 'here your test case name that you want take or remark this row to export all testcases '

[Venkat457]

Hello,

You can go to testplan -->View -->Pick test grid--> then put a filter with your unique identifier like project or application which will pull out all your test cases.Then select all and right click all test cases to export to an .xls.

Be advised while using this process will not pull design steps.In your query i see you were using + and TS_NAME which shouldnt be,just use and TS_NAME = ' '

[Blair1000]

Thank you Venkat457. I can not see test steps in grid view. I want to export test cases with steps. The same error message after remove  +

[WW479296]

I use this VBA code to export the test scripts with their steps:

Option Explicit
Public QCConnection, sSubject, sDomain, sPrj, sSubj, WriteFile

'==========================================================================
'
' Quality Center Test Case Exporter
'
' COMMENT:
' Exports test cases with design steps to Excel
'
'==========================================================================
Public Sub DriverTestSets()

Dim sEID, sPW, sList, sItem As String
Dim Message, Title

    'Get the login info
    Title = "Get Login Info"    ' Set title.

    ' Display message, title
    Message = "Enter your EID"    ' Set prompt.
    sEID = InputBox(Message, Title)
    Message = "Enter your Password"    ' Set prompt.
    sPW = InputBox(Message, Title)

'Return the TDConnection object.
Set QCConnection = CreateObject("TDApiOle80.TDConnection")

QCConnection.InitConnectionEx "http://<<server>>/qcbin" '<-- Change me.
QCConnection.Login sEID, sPW

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

    'Get the lroject name and root folder
    Title = "Get Project Name"    ' Set title.

    ' Display message, title
    Message = "Enter QC Project Name"    ' Set prompt.
    sPrj = InputBox(Message, Title)

'Dim sDomain, sPrj
sDomain = <<"doman name">>   '<-- Change me.
'sPrj = <<"project name">> '<-- Change me.

QCConnection.Connect sDomain, sPrj

If (QCConnection.Connected <> True) Then
    MsgBox "QC Project Failed to Connect to " & sPrj
    Quit
End If

Next_Export:

    ' Display message, title
    Message = "Enter Folder Name"    ' Set prompt.
    sSubj = InputBox(Message, Title)

sSubject = "Subject\" & sSubj
Call ExportTestCases(sSubject)

 Dim Msg, Style, Help, Ctxt, Response, MyString

   'All done or more pre-reads?
    Msg = WriteFile & " ihas been exported.  " _
          & "Click OK to generate another export, or Cancel to Exit " 'Define message.
    Style = vbOKCancel    ' Define buttons.
    Title = sSubj & " Export Complete"                   ' Define title.
            ' Display message.
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbOK Then    ' User chose Yes.
        GoTo Next_Export
    Else    ' User chose to exit
        GoTo Exit_Sub
    End If
   
Exit_Sub:

QCConnection.Disconnect
QCConnection.Logout
QCConnection.ReleaseConnection

MsgBox "All Done"

End Sub

'Export test cases for the Test Lab node.
'
'@param:    strNodeByPath   String for the node path in Test Lab.
'
'@return:                   No return value.
Function ExportTestCases(strNodeByPath)
    Dim Excel, Sheet
    Set Excel = CreateObject("Excel.Application") 'Open Excel
    Excel.Workbooks.Add        '() 'Add a new workbook
    'Get the first worksheet.
    Set Sheet = Excel.ActiveSheet
   
    Sheet.Name = "Tests"
   
    With Sheet.Range("A1:U1")
        .Font.Name = "Arial"
        .Font.FontStyle = "Bold"
        .Font.Size = 14
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Interior.ColorIndex = 34 'Light Turquoise
    End With
'-------------------------------------------------------------------------------
'  List of field names that export to Excel
'  Change the names to your project's field names 
'-------------------------------------------------------------------------------    Sheet.Cells(1, 1) = "Service Line"
    Sheet.Cells(1, 2) = "Service"
    Sheet.Cells(1, 3) = "Process"
    Sheet.Cells(1, 4) = "Sub-Process"
    Sheet.Cells(1, 5) = "Activity"
    Sheet.Cells(1, 6) = "Test ID"
    Sheet.Cells(1, 7) = "Test Name"
    Sheet.Cells(1, 8) = "Type"
    Sheet.Cells(1, 9) = "Destription"
    Sheet.Cells(1, 10) = "Designer (Owner)"
    Sheet.Cells(1, 11) = "Template"
    Sheet.Cells(1, 12) = "Subject (Folder Name)"
    Sheet.Cells(1, 13) = "Attachment"
    Sheet.Cells(1, 14) = "Step Name"
    Sheet.Cells(1, 15) = "Step Description"
    Sheet.Cells(1, 16) = "Expected Result"
    Sheet.Cells(1, 17) = "Action"
    Sheet.Cells(1, 18) = "Object Name"
    Sheet.Cells(1, 19) = "Object Type"
    Sheet.Cells(1, 20) = "Value"
    Sheet.Cells(1, 21) = "Additional Info"

       
    Dim TreeMgr, TestTree, TestFactory, TestList
    Set TreeMgr = QCConnection.TreeManager

    'Specify the folder path in TestPlan, all the tests under that folder will be exported.
    Set TestTree = TreeMgr.NodeByPath(strNodeByPath)
    Set TestFactory = TestTree.TestFactory
    Set TestList = TestFactory.NewList("") 'Get a list of all from node.

    'Specify Array to contain all nodes of subject tree.
    Dim NodesList()
    ReDim Preserve NodesList(0)
    'Assign root node of subject tree as NodeByPath node.
    NodesList(0) = TestTree.Path
   
    'Gets subnodes and return list in array NodesList
    Call GetNodesList(TestTree, NodesList)

    Dim Row, Node, TestCase
    Row = 2
    For Each Node In NodesList
        Set TestTree = TreeMgr.NodeByPath(Node)
        Set TestFactory = TestTree.TestFactory
        Set TestList = TestFactory.NewList("") 'Get a list of all from node.

        'Iterate through all the tests.
        For Each TestCase In TestList
            Dim DesignStepFactory, DesignStep, DesignStepList
            Set DesignStepFactory = TestCase.DesignStepFactory
            Set DesignStepList = DesignStepFactory.NewList("")
           
            'Debug.Print DesignStepList.Fields.Count
            'Dim ctr As Integer
            'For ctr = 1 To DesignStepList.Fields.Count
            '    Debug.Print DesignStepList.Fields(ctr), Name
            'Next ctr
'-------------------------------------------------------------------------------
'  Change the field names to your project's field names 
'-------------------------------------------------------------------------------               
            If DesignStepList.Count = 0 Then
                Sheet.Cells(Row, 1).Value = Trim(TestCase.Field("TS_USER_09"))
                Sheet.Cells(Row, 2).Value = Trim(TestCase.Field("TS_USER_03"))
                Sheet.Cells(Row, 3).Value = Trim(TestCase.Field("TS_USER_07"))
                Sheet.Cells(Row, 4).Value = Trim(TestCase.Field("TS_USER_04"))
                Sheet.Cells(Row, 5).Value = Trim(TestCase.Field("TS_USER_05"))
                Sheet.Cells(Row, 6).Value = Trim(TestCase.Field("TS_TEST_ID"))
                Sheet.Cells(Row, 7).Value = Trim(TestCase.Field("TS_NAME"))
                Sheet.Cells(Row, 8).Value = Trim(TestCase.Field("TS_TYPE"))
                Sheet.Cells(Row, 9).Value = Trim(TestCase.Field("TS_DESCRIPTION"))
                Sheet.Cells(Row, 10).Value = Trim(TestCase.Field("TS_RESPONSIBLE"))
                Sheet.Cells(Row, 11).Value = Trim(TestCase.Field("TS_TEMPLATE"))
                Sheet.Cells(Row, 12).Value = Trim(TestCase.Field("TS_SUBJECT").Path)
                Row = Row + 1
            Else
                For Each DesignStep In DesignStepList
                    'Save a specified set of fields.
                Sheet.Cells(Row, 1).Value = Trim(TestCase.Field("TS_USER_09"))
                Sheet.Cells(Row, 2).Value = Trim(TestCase.Field("TS_USER_03"))
                Sheet.Cells(Row, 3).Value = Trim(TestCase.Field("TS_USER_07"))
                Sheet.Cells(Row, 4).Value = Trim(TestCase.Field("TS_USER_04"))
                Sheet.Cells(Row, 5).Value = Trim(TestCase.Field("TS_USER_05"))
                Sheet.Cells(Row, 6).Value = Trim(TestCase.Field("TS_TEST_ID"))
                Sheet.Cells(Row, 7).Value = Trim(TestCase.Field("TS_NAME"))
                Sheet.Cells(Row, 8).Value = Trim(TestCase.Field("TS_TYPE"))
                Sheet.Cells(Row, 9).Value = Trim(TestCase.Field("TS_DESCRIPTION"))
                Sheet.Cells(Row, 10).Value = Trim(TestCase.Field("TS_RESPONSIBLE"))
                Sheet.Cells(Row, 11).Value = Trim(TestCase.Field("TS_TEMPLATE"))
                Sheet.Cells(Row, 12).Value = Trim(TestCase.Field("TS_SUBJECT").Path)
               
                    'Save the specified design steps.
                    Sheet.Cells(Row, 13).Value = Trim(DesignStep.Field("DS_ATTACHMENT"))
                    Sheet.Cells(Row, 14).Value = Trim(DesignStep.Field("DS_STEP_NAME"))
                    Sheet.Cells(Row, 15).Value = Trim(DesignStep.Field("DS_DESCRIPTION"))
                    Sheet.Cells(Row, 16).Value = Trim(DesignStep.Field("DS_EXPECTED"))
                    Sheet.Cells(Row, 17).Value = Trim(DesignStep.Field("DS_USER_01"))
                    Sheet.Cells(Row, 18).Value = Trim(DesignStep.Field("DS_USER_02"))
                    Sheet.Cells(Row, 19).Value = Trim(DesignStep.Field("DS_USER_03"))
                    Sheet.Cells(Row, 20).Value = Trim(DesignStep.Field("DS_USER_04"))
                    Sheet.Cells(Row, 21).Value = Trim(DesignStep.Field("DS_USER_07"))
                    Row = Row + 1
                Next
            End If
        Next
    Next
   

    'Excel.Columns.AutoFit
    Excel.Columns("A:U").ColumnWidth = 12
   
    'Set Auto Filter mode.
    If Not Sheet.AutoFilterMode Then
        Sheet.Range("A1").AutoFilter
    End If
   
    'Freeze first row.
    Sheet.Range("A2").Select
    Excel.ActiveWindow.FreezePanes = True
   
    'sSubj = Right(sSubj, 32)

'-------------------------------------------------------------------------------
'  Change the folder path/filename to suit your file system
'-------------------------------------------------------------------------------       
    WriteFile = "C:"My Documents\My QC Exports\" _
                     & sPrj & "_" & sSubj & "_TestCases.xls"
   
    'Save the newly created workbook and close Excel.
    Excel.ActiveWorkbook.SaveAs (WriteFile)
    Excel.Quit
   
    Set Excel = Nothing
    Set DesignStepList = Nothing
    Set DesignStepFactory = Nothing
    Set TestList = Nothing
    Set TestFactory = Nothing
    Set TestTree = Nothing
    Set TreeMgr = Nothing
End Function

''
'Returns a NodesList array for all children of a given node of a tree.
'
'@param:    Node        Node in a Test Lab tree.
'
'@param:    NodesList   Array to store all children of a given node of a tree.
'
'@return:   No explicit return value.
Function GetNodesList(ByVal Node, ByRef NodesList)
    Dim i
    'Run on all children nodes
    For i = 1 To Node.Count
        Dim NewUpper
        'Add more space to dynamic array
        NewUpper = UBound(NodesList) + 1
        ReDim Preserve NodesList(NewUpper)
       
        'Add node path to array
        NodesList(NewUpper) = Node.Child(i).Path
       
        'If current node has a child then get path on child nodes too.
        If Node.Child(i).Count >= 1 Then
            Call GetNodesList(Node.Child(i), NodesList)
        End If
    Next
End Function

[flemingkr]

In the code above, the connection string includes the prefix "\Subject'" and it's purpose was to export design test cases...so I'm thinking the comments referring to 'Test Lab' should really read 'Test Plan'.

[flemingkr]

I wonder if someone can help me with the following two lines of code from the VBA example above:

'Dim sDomain, sPrj
sDomain = <<"doman name">>   '<-- Change me.
'sPrj = <<"project name">> '<-- Change me.

When I'm connected to QC I shown the port number in the address as well as a bit of a path. I'm not sure if the connection requires just domain or actually needs the entire URL. For example, when connected I have:

http://server01:8080/qcbin/start_a.htm

I'm guessing sDomain for the connection in VBA actually needs "server01:8080".

Second question for project name: I'm thinking that if the tree looks like:

SUBJECT

MyProject

Folder_1

Folder_2

then I'll set sPrj = "MyProject", and then provide the remainder of the path when prompted.

I apologize for my VBA (and general) ignorance. Any help with this is appreciated. I'm using 10.0 in case it's obvious this approach will not work.

[flemingkr]

Okay. Got that all sorted and working. As I'm sure everyone else knew already, 'Domain' and 'Project' in the code refers to the QC domain and project displayed or input on the logon page--nothing to do with internet domain or any Task Plan folder name.

Once the Connectivity add-in was installed (and the literal values corrected) the code above worked perfectly. 

[Barry L. Daniel]

if your a member of TDForums search for "QC download tool". It's an excel app that works like a champ.  

[dsrocks]

Hi, 

I am a novice user of VBA. I needed the utility to download the Test cases from QC into excel. I took the above VBA code and plugged in the details like QC URL, Domain, Project and Test case path. When I try to run the macro, I get a compliation Error that "Sub or Function is not defined". It is referencing to the function 'Public Sub DriverTestSets()'. How do I fix this?

[visit_alok]

Hiya,

You can generate an Excel Report using the below SQL query and this should resolve the issue.

SELECT
 TEST.TS_TEST_ID as "Test ID", /*Test.Test ID*/
 TEST.TS_NAME as "Test Name", /*Test.Test Name*/
 TEST.TS_DESCRIPTION as "Test Desc.", /*Test.Description*/
 DESSTEPS.DS_STEP_NAME as "Step Name", /*Design Step.Step Name*/
 DESSTEPS.DS_DESCRIPTION as "Step Desc.", /*Design Step.Description*/
 DESSTEPS.DS_EXPECTED as "Expected Result", /*Design Step.Expected Result*/
 TEST.TS_STATUS as "Test Status", /*Test.Status*/
 TEST.TS_TYPE "Test Type", /*Test.Type*/
 TEST.TS_RESPONSIBLE as "Test Designer", /*Test.Designer*/
 TEST.TS_USER_03 as "Test Priority", /*Test.Priority*/
 TEST.TS_USER_04 as "Test Reviewer" /*Test.Reviewer*/

FROM
 Test,
 DESSTEPS

WHERE
 DS_TEST_ID = TS_TEST_ID

Also find attached for a tool through which you can download the required data.

If this resolves your question, please mark it as resolved. 

Thanks,

Alok

[dsrocks]

Hi,

I have been evaluating the 'QC Download Tool v5.1' tool. The Test case download part works fine but there is some problem with Test Set download logic. If I select the Test set path, this populates the fields a) Test Set path and b) Test set name in the Excel. When I click on download, I get an error like "Root\Test set path is not a valid integer value". Attached is the screen shot.

Note: I haven't literally mentioned the word 'Root' in the path. I just selected the path through the Tree view. 

Can you correct this for me?

[dpeel]

Alok,

Thanks for the script and the download, I am finally getting somewhere!  The script got me partially there, but I need to incorporate the test steps and not just the cases, so I thought I would try the tool you suggested.  I downloaded the QC Download Tool and it looks like it has just what I need, but it won't allow me to authenticate.  Any suggestions would be greatly appreciated!

Thanks!

Deanna

[Shinelog]

Hi Team,

Can you please help me in finding right link to download this tool?

Thanks in adv

Regards,

Shinelog

[DGP013]

When i tried to use this tool , i am not able to login to proceed further . though using same credential, i am able to login to Quality center. Not sure why i am not able to login

[Vina]

You nee to enter the the url without "start_a.jsp" for authentication to work.

[SN0001]

Hi

Im sorry i desparately need this tool working for me today.  Fortunattely i got to this link where I found the tool.  I had tried the QC url with out start_a.jsp, but still it didnt work.  Can you please help?

your help is much appreciated

Thanks

SN

[adip]

Hi,

What can i say is that this is a great tool  but can you please send me the password to the code -- i need to modify to extract other custom fileds from the QC

Email apetre83@gmail.com

[Lorin Rowe]

Does anyone know the author or creator of the 'QC download tool v5.1' or the password to enable editing the tool?