6

We use QC for test management tool in our project. Usually, we write test cases & test steps in spreadsheets and load into QC using 'Export to QC' excel add-in. We have a set of test cases already present in QC and they need to be updated.

Is there a way I can export the test cases (along with test steps for each test case) from QC back to excel so that I can update them to load it back to QC??

Improve this question
1
  • Is there any update for this? We are looking forward to one solution now.
    – Anne Liu
    Aug 17, 2020 at 13:09

9 Answers 9

Reset to default
4

This is how I do it:

  1. Login to Quality Center.
  2. Click Test Lab/Test Plan/Wherever you keep your test cases.
  3. After creating the test set, configure the columns to display in the desired order.
  4. Right click within the Test Set list and select Export > All.
  5. Name the Export file and click save.

Exporting a test set is a lot easier than exporting from the test plan section, I've found. So if possible, consider bundling the tests, then exporting.

However if you really want to export from the Test Plan, click View > Test Grid. Then use a column to filter the displayed tests and export as explained above.

The third option is to export using SQL within Quality Center, but I've never explored that option too much as I've always had success with the first two methods.

Improve this answer
1
  • I have attempted your solution from both the Test Lab and the Test Plan and unfortunately it does not export the Test Steps is the OP had requested. This only exports one line for the Test with Test level details.
    – Harrison
    Jan 30, 2014 at 21:50
2

Follow the steps to get the Excel:

  • Select the folder in test plan.
  • Click on the Analysis > Project Report > Tests with design steps.
  • This will generate the report in web page.
  • On the top right corner you will see "Generate" option: Generate option
  • Click that, and it will generate the HTML.
  • Right-click and Export to Microsoft Excel.
Improve this answer
1
  • Nice option! This is available in IE11, not seen it in the other browsers, and only when Excel (2016?) is installed on the system.
    – gagneet
    May 26, 2020 at 22:42
1

there's a sample .vbs (but it was uploaded as .txt) in this forum -> http://h30499.www3.hp.com/t5/Quality-Center-Support-and-News/Test-Plan-export-from-QC-to-Excel/td-p/4807563

I haven't tried it myself though, but I do hope it helps you.

Improve this answer
1
  • The link is broken, can you fix it?
    – Bence Kaulics
    Jan 29, 2021 at 19:10
0

https://sites.google.com/site/quickinfopocket/qtp-probs-solve/tools

small functionality with respect to end user requirement. Currently this tool is proving the functionality of exporting manual test cases(Bulk export) from Quality center to predefined excel file and download qtp result from QC to local drive

Improve this answer
0

Don't need to download anything below works fine:

Click Test Lab/Test Plan/Wherever you keep your test cases. After creating the test set, configure the columns to display in the desired order. Right click within the Test Set list and select Export > All. Name the Export file and click save.

Improve this answer
1
  • is there something we have missed here? we want to export test cases with the test step and description of every steps in the test cases. When i follow your advise, when i configure the column there is no option to select step name and step description. Thanks in advance
    – user8692
    Sep 5, 2014 at 10:11
0

Here's something I found on some other SQA forum while searching for the same functionality:

Qc to Excel Add-in for Excel

Just need to make sure of a couple of things for this to work:

  1. Extract this file into the path - Appdata>Microsoft>Addins

  2. Change the Macro security settings in Microsoft Excel so that macros can be enabled.

Happy testing :)

Improve this answer
0 
Sub ExportTestCasesFromALM()

Dim wd, QCConnection, sProject, sTestPlanPath, TestFolderPath, strNodeByPath

qcURL = InputBox("Please enter ALM URL", "", "http://alm.optum.com:8080/qcbin")
If qcURL = "" Then
  MsgBox ("ALMURL cannot be blank")
  Exit Sub
End If

sDomain = InputBox("Please enter your Domain" & vbNewLine & "Eg:CORE_SYSTEMS", "", "CORE_SYSTEMS")
If sDomain = "" Then
  MsgBox ("DomainName cannot be blank")
  Exit Sub
End If

sProject = InputBox("Please enter your ProjectName" & vbNewLine & "Eg:RADC;GCM(As per ALM Project)", "", "GCM")
If sProject = "" Then
  MsgBox ("ProjectName cannot be blank")
  Exit Sub
End If

sUser = InputBox("Please enter your Username" & vbNewLine & "Eg:MSID", "", "")
If sUser = "" Then
  MsgBox ("UserName cannot be blank")
  Exit Sub
End If

sPass = InputBox("Please enter your Password", "", "")
sFolderpath = InputBox("Please enter your ALM Folderpath" & vbNewLine & "<Subject\FolderStructure>", "", "Subject\RADC Project\2015\Q2_2015\May")
If sFolderpath = "" Then
  MsgBox ("FolderPath cannot be blank")
  Exit Sub
End If

Set QCConnection = CreateObject("TDApiOle80.TDConnection")

QCConnection.InitConnectionEx qcURL
QCConnection.ConnectProjectEx sDomain, sProject, sUser, sPass

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



sTestPlanPath = "Enter QC Test PlanFolder Path"
sTestPlanPath = sTestPlanPath & (Chr(13) & Chr(10))
sTestPlanPath = sTestPlanPath & sFolderpath
'TestFolderPath = InputBox(sTestPlanPath, "Folder Path")

'Displays the Test Plan Folder Path
'MsgBox TestFolderPath
TestFolderPath = sFolderpath
'Call ExporttestCases Funtion and Pass Folder Path parameter
'Call ExportTestCases(TestFolderPath)
strNodeByPath = TestFolderPath

 Dim Excel, Sheet


    Set Excel = CreateObject("Excel.Application") 'Open Excel
    'Excel.
    'Excel.WorkBooks.Add() 'Add a new workbook
    'Get the first worksheet.
    Set Sheet = ActiveSheet

    'sheet name as Tests
    'Sheet.Name = "Tests"

'Specify the Excel Sheet Properties
 With Sheet.Range("A1:H1")
        .Font.Name = "Cambria"
        .Font.FontStyle = "Bold"
        .Font.Size = 12
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Interior.ColorIndex = 15  'Light Grey'
    End With

'Excel Sheet Column Header Values
    Sheet.Cells(1, 1) = "Subject"
    Sheet.Cells(1, 3) = "Description"
    Sheet.Cells(1, 2) = "Test Name"
    Sheet.Cells(1, 4) = "Step Name"
    Sheet.Cells(1, 5) = "Step Description"
    Sheet.Cells(1, 6) = "Expected Result"
    Sheet.Cells(1, 7) = "Level"
    Sheet.Cells(1, 8) = "Designer"
    Sheet.Cells(1, 9) = "Type"
    Sheet.Cells(1, 10) = "TestType"
    Sheet.Cells(1, 11) = "Status"
    Sheet.Cells(1, 12) = "Automation Status"
    Sheet.Cells(1, 13) = "Active Status"
   ' Sheet.Cells(1, 9) = "Application ID"
    ' Sheet.Cells(1, 10) = " Project Identifier"
    ' Sheet.Cells(1, 11) = "Count"
    'Sheet.Cells(1, 3) = "Description"
   ' Sheet.Cells(1, 4) = "Designer "
    'Sheet.Cells(1, 5) = "Status"


'Designer
    'Call PrintFields(TestFactory)
    Dim TreeMgr, TestTree, TestFactory, TestList

    'Connection to TreeManager which Represents the System tree and containing a subject tree and all hierarchial field trees
    Set TreeMgr = QCConnection.TreeManager

    'Specify the folder path in TestPlan, all the tests under that folder will be exported.

    'Gets the node at specified TreePath and strNodeByPath is a a Subject\.... folder path
    Set TestTree = TreeMgr.NodeByPath(strNodeByPath)

    'To Get the a list from all from Node, NewList will bring all the testcases from the perticular node
    Set TestFactory = TestTree.TestFactory
    Set TestList = TestFactory.NewList("")

    'Specify Array to contain all nodes of subject tree.
    Dim NodesList()

    'To Protect all the Nodes specify as Preserve
    ReDim Preserve NodesList(0)

    'Assign root node of subject tree as NodeByPath node.
    NodesList(0) = TestTree.Path

    ' Call sub Getnodes list which will gets subnodes and return list in array NodesList
    Call GetNodesList(TestTree, NodesList)

    Dim Row, Node, TestCase, Counter
   'Starts saving Testcases from the 2nd Row
    Row = 2
    Counter = 1
    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("")
                    Sheet.Cells(Row, 1).Value = TestCase.Field("TS_SUBJECT").Path
                    Sheet.Cells(Row, 3).Value = stripHTML(TestCase.Field("TS_DESCRIPTION"))
                    Sheet.Cells(Row, 2).Value = TestCase.Field("TS_NAME")
                    Sheet.Cells(Row, 7).Value = TestCase.Field("TS_USER_01")
                    Sheet.Cells(Row, 9).Value = TestCase.Field("TS_TYPE")
                    Sheet.Cells(Row, 10).Value = TestCase.Field("TS_USER_02")
                    Sheet.Cells(Row, 11).Value = TestCase.Field("TS_STATUS")
                    Sheet.Cells(Row, 12).Value = TestCase.Field("TS_User_04")
                    Sheet.Cells(Row, 13).Value = TestCase.Field("TS_User_03")
           ' Sheet.Cells(Row, 3).Value = stripHTML(TestCase.Field("TS_DESCRIPTION"))
           ' Sheet.Cells(Row, 9).Value = TestCase.Field("TS_User_02")
           ' Sheet.Cells(Row, 10).Value = TestCase.Field("TS_User_05")
          '   Sheet.Cells(Row, 11).Value = Counter
             Counter = Counter + 1
            If DesignStepList.Count = 0 Then
                'Save a specified set of fields.
                 Sheet.Cells(Row, 1).Value = TestCase.Field("TS_SUBJECT").Path
                 Sheet.Cells(Row, 3).Value = Trim(TestCase.Field("TS_DESCRIPTION"))
                Sheet.Cells(Row, 2).Value = TestCase.Field("TS_NAME")


               'Sheet.Cells(Row, 4).Value = Trim(TestCase.Field("TS_DESCRIPTION"))
                 Sheet.Cells(Row, 7).Value = TestCase.Field("TS_USER_01")
                Sheet.Cells(Row, 8).Value = TestCase.Field("TS_RESPONSIBLE")
               ' Sheet.Cells(Row, 5).Value = TestCase.Field("TS_STATUS")
               'Sheet.Cells(Row, 9).Value = TestCase.Field("TS_User_02")
               'Sheet.Cells(Row, 10).Value = TestCase.Field("TS_User_05")

          Row = Row + 1
            Else
            Dim replaceString
            replaceString = vbNewLine & vbNewLine
                For Each DesignStep In DesignStepList
                   'Save a specified set of fields.
                   Sheet.Cells(Row, 1).Value = TestCase.Field("TS_SUBJECT").Path
                   'Sheet.Cells(Row, 2).Value = TestCase.Field("TS_DESCRIPTION")
                    Sheet.Cells(Row, 4).Value = stripHTML(DesignStep.StepName)
                    'Sheet.Cells(Row, 4).Value = Trim(stripHTML(DesignStep.StepDescription))
                    Sheet.Cells(Row, 5).Value = Replace(Trim(stripHTML(DesignStep.StepDescription)), replaceString, "")
                     'Sheet.Cells(Row, 4).Value = Trim(Sheet.Cells(Row, 4).Value)
                    'Save the specified design steps.
                    'Sheet.Cells(Row, 5).Value = stripHTML(DesignStep.StepExpectedResult)
                    Sheet.Cells(Row, 6).Value = Replace(Trim(stripHTML(DesignStep.StepExpectedResult)), replaceString, "")
                  'Sheet.Cells(Row, 7).Value = TestCase.Field("TS_USER_01")
                    Sheet.Cells(Row, 8).Value = TestCase.Field("TS_RESPONSIBLE")
                    Row = Row + 1

                Next
            End If
        Next
    Next

    'Call PrintFields(DesignStepFactory)

    'Excel.Columns.AutoFit

    'Set the Column width for the following columns.
    'Excel.Columns("C").ColumnWidth = 80 'Description
    'Excel.Columns("G").ColumnWidth = 80 'Step Description(Action)
    'Excel.Columns("H").ColumnWidth = 80 'Expected Result

    '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

    'Save the newly created workbook and close Excel.
   ' Excel.ActiveWorkbook.SaveAs ("C:\" & sProject & "_TESTCASES.xls")
   ' Excel.Quit

   ' Set Excel = Nothing
    Set DesignStepList = Nothing
    Set DesignStepFactory = Nothing
    Set TestList = Nothing
    Set TestFactory = Nothing
    Set TestTree = Nothing
    Set TreeMgr = Nothing
QCConnection.Disconnect

'If the Export is successful you will get the message Completed.
MsgBox "TestCase Export Completed successfully"

'Logout the QC Session
QCConnection.Logout
QCConnection.ReleaseConnection
End Sub





Sub 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 Sub


'Option Explicit
''
'Strips all the HTML tags from a string.
'
'@param:    strHTML A string with HTML tagges embedded.
'
'@return:               A string with all HTML tags stripped.
Function stripHTML(strHTML)
    'Strips the HTML tags from strHTML

    Dim 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, "<br>", vbLf)


    'Replace all HTML tag matches with the empty string
    If Left(strOutput, 6) = "<html>" Then
        strOutput = objRegExp.Replace(strOutput, "")
    End If


    'Replace all &lt;, &gt;, and &quot; with <, >, and "
    strOutput = Replace(strOutput, "&lt;", "<")
    strOutput = Replace(strOutput, "&gt;", ">")
    strOutput = Replace(strOutput, "&quot;", Chr(34))

    Set objRegExp = Nothing

    stripHTML = strOutput    'Return the value of strOutput
End Function



''
'Truncates a string to 32,767 characters for excel.
'
'@param:    strText String to be truncated.
'
'@return:               Truncated string.
Function Truncate(strText)
    'Excel Max Cell Length = 32,767
    Dim sNotice
    sNotice = vbLf & "Contents Truncated..."

    If Len(strText) > 32767 Then
        strText = Left(strText, 32767 - Len(sNotice))
        strText = strText & sNotice
    End If

    Truncate = strText
End Function
Improve this answer
1
  • 1
    Please add some explanatory text to your code.
    – Kate Paulk ♦
    Sep 20, 2016 at 11:48
0

There isn't an easy to use manual method for exporting from HP ALM to Excel.

The only easy to use manual method for exporting will give you a HP report which is hard to use for your purposes.

Those steps are:

  1. Go to Test Plan view
  2. Select a test folder
  3. Go to the top menu and selected Analysis > Project Report > Tests with Design Steps

Unfortunately you'll need to use a technical method or tool for creating an excel file with the test cases.

HP ALM provides a technical method where you can run SQL by going to Dashboard > Analysis View > New Excel Report

enter image description here

Improve this answer
0

Using SQL you can use the following to get you tests out of the DB

select * from 
TEST t,
DESSTEPS d

where 
 t.ts_test_id = d.ds_test_id

 order by 
 t.ts_test_id, d.ds_step_order
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.