Dear LEM Reader’s,
 
In this article I am going to share 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 in to two main category:

1. Download Test Cases without Test Steps
2. Download Test Cases With Test Steps

 

Download Test Cases without Test Steps

Copy and paste the below code in 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


 

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