How to export HP Quality Center test cases to excel (100403 Views)
Reply
Frequent Advisor
Posts: 59
Registered: ‎04-16-2008
Message 1 of 60 (100,403 Views)

How to export HP Quality Center test cases to excel

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 '

Esteemed Contributor
Posts: 391
Registered: ‎05-03-2010
Message 2 of 60 (100,258 Views)

Re: How to export HP Quality Center test cases to excel

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 = ' '

Venkat
Frequent Advisor
Posts: 59
Registered: ‎04-16-2008
Message 3 of 60 (100,251 Views)

Re: How to export HP Quality Center test cases to excel

Thank you  +

Advisor
Posts: 21
Registered: ‎10-15-2008
Message 4 of 60 (100,247 Views)

Re: How to export HP Quality Center test cases to excel

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

 


 

Occasional Advisor
Posts: 7
Registered: ‎06-14-2012
Message 5 of 60 (95,268 Views)

Re: How to export HP Quality Center test cases to excel

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'.

Occasional Advisor
Posts: 7
Registered: ‎06-14-2012
Message 6 of 60 (95,266 Views)

Re: How to export HP Quality Center test cases to excel

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.

Occasional Advisor
Posts: 7
Registered: ‎06-14-2012
Message 7 of 60 (95,256 Views)

Re: How to export HP Quality Center test cases to excel

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. 

Frequent Advisor
Posts: 81
Registered: ‎04-03-2009
Message 8 of 60 (95,186 Views)

Re: How to export HP Quality Center test cases to excel

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

Occasional Visitor
Posts: 3
Registered: ‎08-01-2012
Message 9 of 60 (92,509 Views)

Re: How to export HP Quality Center test cases to excel

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?

Trusted Contributor
Posts: 147
Registered: ‎07-03-2012
Message 10 of 60 (92,455 Views)

Re: How to export HP Quality Center test cases to excel

[ Edited ]

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

Please note :- Any solution or queries expressed in my postings are mine alone, and do not reflect the opinions of my employer.
Occasional Visitor
Posts: 3
Registered: ‎08-01-2012
Message 11 of 60 (92,390 Views)

Re: How to export HP Quality Center test cases to excel

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?

Occasional Visitor
Posts: 1
Registered: ‎08-07-2012
Message 12 of 60 (91,984 Views)

Re: How to export HP Quality Center test cases to excel

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

 

Advisor
Posts: 27
Registered: ‎06-22-2012
Message 13 of 60 (89,850 Views)

Re: How to export HP Quality Center test cases to excel

Hi Team,

 

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

 

Thanks in adv

 

Regards,

Shinelog

Occasional Visitor
Posts: 3
Registered: ‎01-10-2013
Message 14 of 60 (80,546 Views)

Re: How to export HP Quality Center test cases to excel

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

Occasional Visitor
Posts: 2
Registered: ‎02-14-2013
Message 15 of 60 (76,248 Views)

Re: How to export HP Quality Center test cases to excel

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

Occasional Visitor
Posts: 2
Registered: ‎02-14-2013
Message 16 of 60 (76,216 Views)

Re: How to export HP Quality Center test cases to excel

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

Occasional Advisor
Posts: 8
Registered: ‎03-12-2013
Message 17 of 60 (72,859 Views)

Re: How to export HP Quality Center test cases to excel

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

Advisor
Posts: 11
Registered: ‎04-11-2008
Message 18 of 60 (64,702 Views)

Re: How to export HP Quality Center test cases to excel

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

Occasional Visitor
Posts: 1
Registered: ‎06-24-2013
Message 19 of 60 (59,932 Views)

Re: How to export HP Quality Center test cases to excel

Thanks Venkat,

 

it helped.

Occasional Visitor
Posts: 2
Registered: ‎06-19-2013
Message 20 of 60 (59,896 Views)

Re: How to export HP Quality Center test cases to excel

Hi Hiya,

 

I am trying to download testcases from testplan from your code.

when I ran your code it is prompting for all the credentials, url for the QC and it is getting authenticated.

but when i click on the box download testcases to workbook ,work book is getting hanged. And also it is not asking for the path.

Aprricate your help me in this

Regular Advisor
Posts: 127
Registered: ‎12-14-2010
Message 21 of 60 (59,597 Views)

Re: How to export HP Quality Center test cases to excel

You can use the attached VBS to download the test cases.

Occasional Visitor
Posts: 1
Registered: ‎07-03-2013
Message 22 of 60 (58,355 Views)

Re: How to export HP Quality Center test cases to excel

I am trying to use the tool in Excel 2010.

 

However, I am getting some compilation error when i click on authenticate button.

 

Would you please help me on this.

 

 

Visitor
Posts: 3
Registered: ‎07-18-2013
Message 23 of 60 (55,993 Views)

Re: How to export HP Quality Center test cases to excel

I'm very new to ALM and am trying to automate, with VBA, the download of some counts of Blocked, Passed, etc. tests per test name. 

 

I've been going in manually and opening a graph, which I switch to Data Grid, then Save Graph Data to an Excel sheet.

 

Will the extensive code you've posted give me the raw, unaggregated test names and results so I can do the counts in Excel or use SQL behind the scenes?  Or can I download the aggregated counts?  Either way would work for me.

 

I believe I've gotten the authentication sequence working - passing in my username, password, project, etc. - simply because I have not gotten an error message saying that it's failed; I can't actually see the ALM page once I've stepped through the authentication code lines.

 

I appreciate any help you can provide.

Visitor
Posts: 1
Registered: ‎08-20-2012
Message 24 of 60 (51,762 Views)

Re: How to export HP Quality Center test cases to excel

Hi Alok,

 

The QC Download Tool is a thing of beauty - just what I have been looking for for many years. A couple of questions:

1. I also get the same error that someone else reported on the test set spreadsheet - Root\Test set path is not a valid integer value. How to I resolve that error?

2. How would I add additional fields - I would like to add the Actual Result field to the Test Set spreadsheet.

 

thanks,

 

Reneau

Honored Contributor
Posts: 657
Registered: ‎03-02-2009
Message 25 of 60 (51,096 Views)

Re: How to export HP Quality Center test cases to excel

Hi all,

 

here are 3 reports that could be useful for exporting TestCase from TestPlan, Execution Reports and Trend of Defects: http://motu4qc-en.jimdo.com/how-to-tips-tricks/excel-reports/  (English)

http://www.motu4qc.it/how-to-tips-tricks/reportistica-excel/ (Italian)

 

 

Have a nice day,

Massimo.

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.