HP Quality Center – Excel Macro to Import Test Cases from QC

.

In this article, I am going to share the VBA code to download the test cases from QC in Excel Sheet. Here in this example, I will fetch very few important fields from QC but if you want more fields to be fetched then it can be easily done. I have categorized this VBA code into two main categories:

1. Download Test Cases without Test Steps

2. Download Test Cases With Test Steps

1. Download Test Cases without Test Steps

Copy and paste the below code into your excel VBA module. Make the necessary changes like your QC ID, Password, URL, path, etc. and enjoy running the code 🙂


Function EmportTestCases()
    On Error Resume Next
    Dim QCConnection
    Dim sUserName, sPassword
    Dim sDomain, sProject
    Dim TstFactory, TestList
    Dim TestCase
'Create QC Connection Object to connect to QC
    Set QCConnection = CreateObject("TDApiOle80.TDConnection")
    sUserName = "your user id"
    sPassword = "your password"
    QCConnection.InitConnectionEx "your QC URL/qcbin"
'Authenticate your user ID and Password
    QCConnection.Login sUserName, sPassword
'Quit if QC Authentication fails
    If (QCConnection.LoggedIn <> True) Then
        MsgBox "QC User Authentication Failed"
        End
    End If
    sDomain = "your domain name"
    sProject = "your project name"
'Login to your Domain and Project
    QCConnection.Connect sDomain, sProject
'Quit if login fails to specified Domain and Project
    If (QCConnection.AuthenticationToken = "") Then
        MsgBox "QC Project Failed to Connect to " & sProject
        QCConnection.Disconnect
        End
    End If
'Now successful connection is made to QC
'Get the test factory
    Set TstFactory = QCConnection.TestFactory
' Your QC Project Path for which you want to download
' the test cases.
    fpath = "your project folder" 
    Set myfilter = TstFactory.Filter()
    myfilter.Filter("TS_SUBJECT") ="^" & fpath & "^"
'Get a list of all test cases for your specified path
    Set TestList = myfilter.NewList()
'Format the header before downloading the test cases
    With ActiveSheet
        .Range("B5").Select
        With .Range("B4:F4")
            .Font.Name = "Arial"
            .Font.FontStyle = "Bold"
            .Font.Size = 10
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Interior.ColorIndex = 15
        End With
        .Cells(4, 2) = "Subject (Folder Name)"
        .Cells(4, 3) = "Test Name (Manual Test Plan Name)"
        .Cells(4, 4) = "Description"
        .Cells(4, 5) = "Status"
        Dim Row
        Row = 5 '- set the data row from 5
'loop through all the test cases.
        For Each TestCase In TestList
            .Cells(Row, 2).Value = TestCase.Field("TS_SUBJECT").Path
            .Cells(Row, 3).Value = TestCase.Field("TS_NAME")
'QC stores description in html format. So before storing it
'in to excel, StripHTML() will remove all HTML tags and put
'texts only. Also new line tag 
is replaced with new line 'character chr(10) in excel so that all the new line texts appears properly .Cells(Row, 4).Value = StripHTML(Replace(TestCase.Field("TS_DESCRIPTION"), _ "<br>", Chr(10))) .Cells(Row, 5).Value = TestCase.Field("TS_EXEC_STATUS") Row = Row + 1 Next ' Next test case End With 'Release the object Set DesignStepFactory = Nothing Set DesignStep = Nothing Set DesignStepList = Nothing Set TstFactory = Nothing Set TestList = Nothing Set TestCase = Nothing QCConnection.Disconnect MsgBox ("All Test cases are downloaded without Test Steps") End Function Function StripHTML(sInput As String) As String Dim RegEx As Object Set RegEx = CreateObject("vbscript.regexp") Dim sInput As String Dim sOut As String sInput = cell.Text With RegEx .Global = True .IgnoreCase = True .MultiLine = True .Pattern = "<[^>]+>" 'Regular Expression for HTML Tags. End With sOut = RegEx.Replace(sInput, "") StripHTML = sOut Set RegEx = Nothing End Function


 

2. Download Test Cases with Test Steps


Function EmportTestCases()
    On Error Resume Next
    Dim QCConnection
    Dim sUserName, sPassword
    Dim sDomain, sProject
    Dim TstFactory, TestList
    Dim TestCase
'Create QC Connection Object to connect to QC
    Set QCConnection = CreateObject("TDApiOle80.TDConnection")
    sUserName = "your user id"
    sPassword = "your password"
    QCConnection.InitConnectionEx "your QC URL/qcbin"
'Authenticate your user ID and Password
    QCConnection.Login sUserName, sPassword
'Quit if QC Authentication fails
    If (QCConnection.LoggedIn <> True) Then
        MsgBox "QC User Authentication Failed"
        End
    End If
    sDomain = "your domain name"
    sProject = "your project name"
'Login to your Domain and Project
    QCConnection.Connect sDomain, sProject
'Quit if login fails to specified Domain and Project
    If (QCConnection.AuthenticationToken = "") Then
        MsgBox "QC Project Failed to Connect to " & sProject
        QCConnection.Disconnect
        End
    End If
'Now successful connection is made to QC
'Get the test factory
    Set TstFactory = QCConnection.TestFactory
' Your QC Project Path for which you want to download
' the test cases.
    fpath = "your project folder" 
    Set myfilter = TstFactory.Filter()
    myfilter.Filter("TS_SUBJECT") = "^" & fpath & "^"
'Get a list of all test cases for your specified path
    Set TestList = myfilter.NewList()
'Format the header before downloading the test cases
    With ActiveSheet
        .Range("B5").Select
        With .Range("B4:H4")
            .Font.Name = "Arial"
            .Font.FontStyle = "Bold"
            .Font.Size = 10
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Interior.ColorIndex = 15
        End With
        .Cells(4, 2) = "Subject (Folder Name)"
        .Cells(4, 3) = "Test Name (Manual Test Plan Name)"
        .Cells(4, 4) = "Description"
        .Cells(4, 5) = "Status"
        .Cells(4, 6) = "Step Name"
        .Cells(4, 7) = "Step Description(Action)"
        .Cells(4, 8) = "Expected Result"
        Dim Row
        Row = 5 '- set the data row from 5
'loop through all the test cases.
        For Each TestCase In TestList
            .Cells(Row, 2).Value = TestCase.Field("TS_SUBJECT").Path
            .Cells(Row, 3).Value = TestCase.Field("TS_NAME")
'QC stores description in html format. So before storing it
'in to excel, StripHTML() will remove all HTML tags and put
'texts only. Also new line tag 
is replaced with new line 'character chr(10) in excel so that all the new line texts appears properly .Cells(Row, 4).Value = StripHTML(Replace(TestCase.Field("TS_DESCRIPTION"), _ "<br>", Chr(10))) .Cells(Row, 5).Value = TestCase.Field("TS_EXEC_STATUS") 'Get the DesignStepFactory for the this testcase Dim DesignStepFactory, DesignStep, DesignStepList Set DesignStepFactory = TestCase.DesignStepFactory Set DesignStepList = DesignStepFactory.NewList("") 'Check if design steps exists for the test If DesignStepList.Count <> 0 Then 'loop for all the steps for this test case For Each DesignStep In DesignStepList .Cells(Row, 6).Value = DesignStep.StepName .Cells(Row, 7).Value = StripHTML(Replace(DesignStep.StepDescription, _ "<br>", Chr(10))) .Cells(Row, 8).Value = StripHTML(Replace(DesignStep.StepExpectedResult, _ "<br>", Chr(10))) Row = Row + 1 Next 'next Step End If ' release the design step objects Set DesignStepFactory = Nothing Set DesignStep = Nothing Set DesignStepList = Nothing Next ' Next test case End With 'Release the object Set DesignStepFactory = Nothing Set DesignStep = Nothing Set DesignStepList = Nothing Set TstFactory = Nothing Set TestList = Nothing Set TestCase = Nothing QCConnection.Disconnect MsgBox ("All Test cases are downloaded with Test Steps") End Function Function StripHTML(sInput As String) As String Dim RegEx As Object Set RegEx = CreateObject("vbscript.regexp") Dim sInput As String Dim sOut As String sInput = cell.Text With RegEx .Global = True .IgnoreCase = True .MultiLine = True .Pattern = "<[^>]+>" 'Regular Expression for HTML Tags. End With sOut = RegEx.Replace(sInput, "") StripHTML = sOut Set RegEx = Nothing End Function

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

51 Comments

  1. Sabuhi

    Hi, I like all your coding for HP QC. Can you also share a VBA code to import test cases or defects from Excel sheet to HP QC. I know we can do it using the Excel add in but it is still a lengthy procedure and my team feels reluctant to go through all that. So I wanted something which can process immediately from excel to QC with a single click.

    Reply
  2. gowthami

    Hi can any one help to pull only the test set name and count of test cases from qc?

    Reply
    • Jagan Shirsath

      Hi,

      Actually, i just trying to connect with HP QC with below code.

      Function ConnectToQC()

      On Error Resume Next
      Dim QCConnection
      ‘Create QC Connection Object to connect to QC
      Set QCConnection = CreateObject(“TDApiOle80.TDConnection”)

      Dim sUserName, sPassword, sDomain, sProject
      sUserName = “”
      sPassword = “”
      sDomain = “”
      sProject = “”
      QCConnection.InitConnectionEx “”
      ‘Authenticate your user ID and Password
      QCConnection.Login sUserName, sPassword
      ‘Quit if QC Authentication fails
      If (QCConnection.LoggedIn True) Then
      MsgBox “QC User Authentication Failed”
      End
      End If
      ‘Login to your Domain and Project
      QCConnection.Connect sDomain, sProject
      ‘Quit if login fails to specified Domain and Project
      If (QCConnection.AuthenticationToken = “”) Then
      MsgBox “QC Project Failed to Connect to ” & sProject
      QCConnection.Disconnect
      End
      End If

      End Function

      Reply
  3. Vinod

    Hi,

    How to import testcases which are executed with a particular date range?
    Thanks

    Reply
  4. nithin mohanan

    The code is returning an error..syntax wrong at first line

    Reply
  5. Alien

    Hi,

    does this script work for exporting test cases from QC 8.2 version.
    Please let me know if not, and also suggest what need to be change to export from QC 8.2.

    Thanks & Regards,
    Alien

    Reply
  6. Muthu

    Hi ,

    i excuted the script that download test cases with steps.It prompts the message stating all test cases downloaded with steps.But i see the only the header not the test cases / steps downloaded. Could you please check and post the updated code

    Reply
    • vikash

      I got the same result. Could you please explain what to do next.

      Reply
  7. RB

    Hi,

    In the above code ..how should i put in the path.

    In my Originsation, there is root folder under which we have sub folders which have subfolders under them.

    I need to get the TC’s name ,Execution status from the lower most folder.

    Reply
  8. Daniel

    Vishwamitra,
    I fetched field TS_VC_CUR_VER of test cases but got blank.

    Reply
  9. Karthika

    Hi,
    I am getting “QC User Authentication Failed” Error. But my url and id and password is correct. please help me

    Reply
    • Vishwamitra Mishra

      Hi Karthika,
      I Hope.. this issue is resolved !! If yes, please share the trick with the world, if not do let me know.

      Reply
      • Amitk

        Hi Karthica,

        I am also getting QC User Authentication Failed” Error. But my url and id and password is correct. please help me

        Thanks,
        Amit

        Reply
  10. Dinesh

    I want to export test cases with steps from QC and the above code is working fine . But the steps which contains hyperlink like “‘Call ” fails here. These code is displaying the blank value for this type of step. Please suggest how to proceed here for such scenarios. Thanks all for your help.

    Reply
    • Vishwamitra Mishra

      Hi Dinesh,
      There must be some property where such links are stored in StepsFactory. I do not know about this. Let us know if you find solution around this.

      Reply
  11. Kiran

    Im not that PRO in vb and using your code :), but received an error message.

    Duplicate declaration in current scope. Appreciate if you can help me out. this one is with the second code. tst cases with steps

    Reply
    • Vishwamitra Mishra

      Hi,

      Run the code in Debug mode and see which variable is declared more than once. This error comes for more than once declaration of a variable.

      You can read articles from my tutorial page for for beginners to understand the basics of VBA 🙂

      Reply
  12. dgarg

    Using above script, when I am trying to connect to HPQC 12.01, getting ‘QC User Authentication failed’ error .
    Credentials & link provided are correct.
    Please confirm that above script is also valid for HPQC 12.01 version

    Reply
    • Vishwamitra Mishra

      Hi Dinesh,

      This script should be valid for 12.01 version as well. There is change in login Syntax. At what step are you getting the error from the following 3 statements?

      Initialise the Quality center connection
      tdConnection.InitConnectionEx qcURL
      ‘Authenticating with username and password
      tdConnection.Login qcID, qcPWD
      ‘connecting to the domain and project
      tdConnection.Connect qcDomain, qcProject

      Reply
      • dgarg

        I am getting error in first statement.

        QConnection.InitConnectionEx “http://vvvvvv.xxxx.com:8080/qcbin”
        If (QCConnection.InitConnectionEx True) Then
        MsgBox “QC Conection Failed”
        End
        End If

        I have replaced my HPQC url with vvvvv.xxxxx

        Reply
      • Dinesh

        This issue is still not resolved. I am still getting QC User Authentication Failed””

        What is the change in login syntax ?

        Reply
      • Makaranda

        Hi,

        Can you share the code with which you were able to successfully run the code?
        I am getting “QC Authentication failed” message even when i had provided all correct details.

        Reply
  13. Jay

    I am getting Runtime Error ‘429’ ActiveX component can’t create object

    Reply
  14. Pankaj

    When I run the code it is just giving me the header created but steps are not downloading. Do we need to add anything extract to chis code?? I am usng HP ALM QC 11.0

    Reply
  15. Chandru

    Now to use SQL query in VBA to pull details from QC

    Reply
  16. Ranjeeth

    Hi Mishra,
    I used the code with Steps to download with QC12 and its working fine. Only issue is Steps description and steps expected result is not downloaded. I removed the steps with strip html and ran it. Steps description and steps executed resulted is now downloaded with html tags. How to remove the html tags or any modification required. Pls advise.

    Reply
    • Naren

      Hi Pankaj,

      Can you share the code with which you were able to successfully run the code?
      I am getting “QC Authentication failed” message even when i had provided all correct details.

      Reply
  17. Anantha Sairam

    There is no error in my code, but am getting a pop-up showing QC Project failed to connect to “MyProjectName”. What’s the issue?

    Reply
  18. Mari

    I am usng HP ALM QC 11.0. When I run this code it is just giving the header created but steps are not downloading. Do we need to add anything extract to this code??

    Reply
  19. Kanhaiya

    Hi,
    Thanks for your comments. Its really helpfull and I am learning with your post regularly.
    Just need a small favor.
    How to extract Design step which are written in Template (which directs to some other test with many steps inside).
    Please help

    Reply
  20. Parasuraman

    Hi, I am getting “QC User Authentication Failed”. I have given credential are correct.

    I am using below version of QC.

    HP Quality Center
    Quality Center Enterprise Edition 11.52.341

    Internal Components Build

    OTA Client- 11.52.341.0
    User Interface -11.52.341.0
    WebGate Client -11.52.341.0
    Test Run Scheduler -11.52.341.0
    Site Administration Client-Not Available
    Sprinter -11.50.341
    Analysis Extension – 11.50.341
    Quality Center – 11.50.341

    Reply
  21. Gaddy

    Getting values in step description and expected result as html tags. When verified in QC, corresponding step description has some text bolded and underlined.

    Getting description without html tags when text is not bolded and underlined.

    Reply
    • Vishwamitra Mishra

      Dear Gaddy,

      You can use use the “StrpHTML” function to get rid of HTML Tags. This is a user defined function for which you can find the code here in this article:

      http://learnexcelmacro.com/wp/2011/12/strip-html-how-to-remove-html-tags-from-a-string-in-vba/

      Function StripHTML(sInput As String) As String
      Dim RegEx As Object
      Set RegEx = CreateObject("vbscript.regexp")

      Dim sOut As String
      With RegEx
      .Global = True
      .IgnoreCase = True
      .MultiLine = True
      .Pattern = "<[^>]+>" 'Regular Expression for HTML Tags.
      End With

      sOut = RegEx.Replace(sInput, "")
      StripHTML = sOut
      Set RegEx = Nothing
      End Function

      Reply
  22. Vinay

    I am able to connect the ALM connection. But data was not downloaded as required. It was created only header part. i thought it was permission issue but i took the permission for ALM database and tried same. It was not working. As per my knowledge the issue is “TS_SUBJECT” filter. Could please explain me what will be issue. Thanks in advance…:)

    Reply
  23. Benedict Erenrich

    Thank you, I have recently been searching for facts about this subject matter for ages and yours is the best I’ve located so far.

    Reply
  24. Arpita

    Set tdConnection = CreateObject(“TDApiOle80.TDConnection”)

    I am getting error on this line. Can some please explain why?

    Reply
  25. rajasekhar

    Hi Vishwa,

    I Have written a macro to get the status count of a test set (i.e how many got passed and failed )

    I’m unable to get exact status of test cases please help in this
    using : LCase(tlistc.Item(ix).Field(“TS_EXEC_STATUS”))

    Reply
  26. deepa

    Hi can any one help by giving code for “storing run attachments (reports) of a test case in local from alm ?

    Reply
  27. deepa

    Hi can any one help by giving code to store run attachments(report after run) in local from qc?

    Reply
  28. Kunal Khandagale

    Hi ,

    The above code is working fine but I need to extract all the test cases which are present in QC project. This code only requires the specified path but what input should i give in path for full test cases extract in project.

    Regards
    Kunal

    Reply
    • Vishwamitra Mishra

      Keep fPath as Blank and then run the code. It should work.

      Reply
  29. Sai

    Desccription, StepDescription, Expected result are not getting downloaded. please help.

    Reply
  30. Thiyagarajan

    Can you provide the code for extract the test scripts status from test lab?

    Reply
  31. Sagar

    Can I have the solution for Path as it is not taking expected path. Please provide me solution ALM 12.01 used for testing

    Reply
  32. Mahe

    Hello There,

    That’s good work. I am trying to use your code, but it just loads the Header no testcases.

    I am using the Path : fpath = “SUBJECT\Folder 1\Folder 2\Folder 3\Folder 4” , under Folder 4 , I have many subfolders with testcases.
    QC Version is 12+

    Where am I wrong? Please help me. Please email also.
    Maheswar81@yahoo.com

    Reply
  33. KV

    Hi,

    I am unable to see the downloaded file..I have copied the code and saved with .vbs and i tried running it. It didnt throw any results but am unable to view any file is getting downloaded

    Reply
  34. monali

    I am trying to use your code, but it just loads the Header no testcases.
    Please explain…

    I am using path: path=”ProjectName\SIT\PreSIT”

    Reply
  35. Mounish

    Active X Component Error while running the above code. Please help me out of this

    Reply
  36. Mark

    For those people who are getting error – QC User Authentication Failed, I was also getting same error and was struggling to understand why was the case. I could resolve this by logging in to my project and domain in HP ALM menu from excel

    Reply
  37. Vaidhiswaran S

    @Viswa: Good Day. For Each Tcase i8n TestList. This statement throwing error saying like Type Mismatch.
    Just need your suggestion to fix this issue

    Reply
  38. Vaidhiswaran S

    For Each TestCase In TestList –> Facing Type Mismatch error. Kindly suggest

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest