Highlighted
Absent Member.
Absent Member.
11156 views

Export Test cases from ALM using Excel macros

Hi,

 

I am using below code to export test cases from ALM, but in Excel file i am gettin only header.

I am running this code in Excel.

Can you please suggest solution i am trying it from several days.

PFB 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 = "User name"      --- changing
   sPassword = "Password"        ---- changing
   QCConnection.InitConnectionEx "ALM link"
'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 = "DOMAIN"--- --- changing
    sProject = "PROJECT"    --- changing
'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 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 = "Path of project"  '----------------------- I am entering here path of test set. Is it correct?
    Set myfilter = TstFactory.Filter()
  myfilter.Filter("TS_SUBJECT") = "^" & fpath & "^" '----------------here should be changed anything?
'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

 

 

ALM should be open or not? when we connect to ALM using above code?

This code is running successfully but in Active excel sheet i am getting only heaaders but test cases are not exported.

I am entering the test set path in test path and user name id, password, project ,domain.

Do i need to change anything else?

 

 

0 Likes
7 Replies
Highlighted
Fleet Admiral
Fleet Admiral

This code is for exporting tests from Test Plan, not test sets from Test Lab. So your fpath variable should look something like "Subject\Folder1\Folder2".
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
0 Likes
Highlighted
Absent Member.
Absent Member.

Hi,

 

Thanks for suggestion. i changed the path in the code but still i am getting only headers name in the excel file.

Can u please suggest what else should be change.?

0 Likes
Highlighted
Fleet Admiral
Fleet Admiral

My guess is your testlist is still empty. It could be because the path is still not correct.

Why do you want to run this with macro code? Wouldn't it be easier to export to Excel directly from the Test Grid in Test Plan? You can set a filter on the Subject to get only the folders you are interested in.
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
0 Likes
Highlighted
Absent Member.
Absent Member.

I know this way.. but i want that user dont need to open the ALM manually and just enter the required path and run the code then all the required test cases will be in Excel sheet.

I checked test list is not empty...

 

I am also working to find out test cases which dont cover any requirment. PFB the code

 

Public Sub UnneededTests()

' Get the list of tests that do not cover any requirement.

    Dim reqF As ReqFactory
    Dim testF As TestFactory
    Dim ReqFilter As TDFilter
    Dim testFilter As TDFilter
    Dim aTest As Test
    Dim testL As List

    Set reqF = tdc.ReqFactory
    Set ReqFilter = reqF.Filter
    'Exclude the top-level folder
    ReqFilter.Filter("RQ_REQ_ID") = ">= 0"          --- what is meaning of this step?

    ' Set exclusive XFilter: Filter for items that
    ' match primary filter and do not match cross filter.
    Set testF = tdc.TestFactory
    Set testFilter = testF.Filter
    testFilter.SetXFilter "TEST-REQ", False, ReqFilter.Text                     --- I also dont understand this section.
    Debug.Print testFilter.Text
    Set testL = testF.NewList(testFilter.Text)

    For Each aTest In testL
        Debug.Print aTest.ID & ": " & aTest.Name
    Next

' 'To get the tests that DO cover some requirement use this code:
' TestFilter.SetXFilter "TEST-REQ", True, ReqFilter.Text
' Debug.Print TestFilter.Text
' Set testL = testF.NewList(TestFilter.Text)
'
' For Each aTest In testL
' Debug.Print aTest.ID & ": " & aTest.Name
' Next

 

Can you please provide your input on this code as well.? while running this code in Excel i am not getting anything.

Please suggest what Parameters and what steps need to change in code.

0 Likes
Highlighted
Fleet Admiral
Fleet Admiral

Comment out the On Error Resume Next in the beginning of the code. You should always do that when debugging. You are probably getting some error that is suppressed now.

 

I suggest you open a new thread with your other problem. Otherwise this discussion will be very confused.

Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
0 Likes
Highlighted
Cadet 3rd Class
Cadet 3rd Class

HI,

Try commenting out

'sInput = cell.Text in function  StripHTML(sInput As String) and also delete  Dim sinput As String from the same fuction so the updated code will look some thing like this:

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

Give it a try since it worked for me and let me know if it works for you as well.

0 Likes
Highlighted
Absent Member.
Absent Member.

The above code did not return any data except headers

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.