Dear LEM Readers,
In previous article, we learnt How to connect to HP QC using Excel Macro. In this article, we are going to learn How to create folder structure in HP QC Test Lab. As you must be knowing – To show the test execution in Test Lab you should follow the below steps:
- Create folder structure in Test Lab (most of the time same as in Test Plan but not necessary)
- Now Pull the corresponding test cases from Test Plan to Test Lab. Refer Pic 1
Pic 1-HP QC - Mapping Test Cases in Test Lab
Create Folder Structure in Test Lab and Pull test Cases from Test Plan
In this article, you are going to learn how to create folder structure in the Test lab and Mapping or pull test cases from Test Plan to Test Lab.
In order to do so, copy and paste the below code into any of the modules in your VBA code and follow the comments which are written within the code to understand.
Below code will make exactly the same folder structure as Test Plan in the Test lab and It will also map corresponding test cases in the test lab from Test Plan.
Sub Pull_Test_Cases_in_Lab()
On Error Resume Next
Dim tdConnection
Dim qcUser, qcPassword
Dim qcDomain, qcProject
Dim qcURL
Dim myFolder As String
Dim treeMgr As Variant
Dim myTestFact As Variant
Dim myTestFilter As Variant
Dim myTestList As Variant
'Provide all details
qcUser = ""
qcDomain = ""
qcProject = ""
qcURL = ""
'Create QC connection object
Set tdConnection = CreateObject("TDApiOle80.TDConnection")
'Initialise the Quality center connection
tdConnection.InitConnectionEx qcURL
'Authenticate username and password
tdConnection.Login qcUser, qcPassword
'login to the domain and project
tdConnection.Connect qcDomain, qcProject
Set treeMgr = tdConnection.treemanager
Set myTestFact = tdConnection.TestFactory
Set myTestFilter = myTestFact.Filter
' Apply filter on the last known node
' Suppose you want to map all the test cases
' of your Project Folder XYZ then provide
' the complete path till XYZ
' For example: "^\Subject\XYZ\^"
myFolder = "Subject\Test Project 1"
'Get the complete test list in the main folder
Set myTestList = myTestFact.newList(myTestFilter.Text)
myTestFilter.Filter("TS_SUBJECT") = "^\" & myFolder & "^"
'Exit if no test cases found in the above folder
If myTestList.Count = 0 Then
MsgBox "Zero Test Case Found "
Exit Sub
End If
' get all the tets cases available in the
' above main folder
tcCount = myTestList.Count
' traverse through each test cases
' in testList array you got
For Each tc In myTestList
' Get the Subject Folder Node
Set myTCNode = tc.Field("TS_Subject ")
' Get Complete path of test case without Test Name
myPath = myTCNode.path
'Create Folder stucture and pull the
' test cases in to test set in test lab
' from test plan
map_TestCase myPath, tc
Next
Set myTestList = Nothing
Set myTestFilter = Nothing
Set myTestFact = Nothing
End Sub
Sub map_TestCase(myPath, myTC)
Dim tcMgr As Variant
Dim myRoot As Variant
Dim newTSTest As Variant
Dim TSFact As Variant
Dim TSFilter As Variant
On Error Resume Next
Set tcMgr = tdConnection.TestSetTreeManager
' Split path for loop
subjectArray = Split(myPath, "\")
' in Test Plan folder structure starts with Subject
' while in Test Lab it is "Root ". So we need to change
' the path
NewPath = "Root "
OldPath = ""
For iFolder = 1 To UBound(subjectArray)
'Assign old to new path
OldPath = NewPath
'get current folder
CurrentSubName = subjectArray(iFolder)
'build new path
NewPath = Trim(NewPath) & "\" & CurrentSubName
'search Folder
Set newNode = tcMgr.NodeByPath(NewPath)
'create folder if it does not exist
If newNode Is Nothing Then
Set tcMgr = Nothing
Set tcMgr = tdConnection.TestSetTreeManager
If iFolder = 1 Then
Set myRoot = tcMgr.Root
Else
Set myRoot = tcMgr.NodeByPath(OldPath)
End If ' iFolder'
Set newNode = myRoot.AddNode(CurrentSubName)
newNode.Post
End If 'new Node
' if the current folder is the last folder of the array
' then create the testset
If iFolder = UBound(subjectArray) Then
Set TSFact = newNode.TestSetFactory
Set TSFilter = TSFact.Filter
TSFilter.Filter("CY_FOLDER_ID ") = newNode.Nodeid
TSFilter.Filter("CY_CYCLE ") = CurrentSubName
Set TSList = TSFact.newList(TSFilter.Text)
'If no testset found in test plan then TestSet folder will
'not be created. If you want to do so uncomment the below
'if else conditions
'If TSList.Count = 0 Then
'Set TestSet1 = TSFact.AddItem(Null)
'TestSet1.Name = CurrentSubName
'TestSet1.Status = "Open "
'TestSet1.Post
'Else
Set TestSet1 = TSList.Item(1)
'End If
Set TSTestFact = TestSet1.TSTestFactory
Set TSTestList = TSTestFact.newList("")
'Initilize the variable to check if any Test case found
foundTCs = 0
If TSTestList.Count > 0 Then
For Each myTSTest In TSTestList
If myTSTest.TestID = Trim(CurrentTest.ID & "") Then
foundTCs = 1
End If
Next
End If
If foundTCs = 0 Then
Set newTSTest = TSTestF.AddItem(CurrentTest.ID)
newTSTest.Post
End If
End If
Set newTSTest = Nothing
Set myTSTest = Nothing
Set TSFilter = Nothing
Set TSTestF = Nothing
Set TSTestList = Nothing
Set TSFilter = Nothing
Set TSFact = Nothing
Set newNode = Nothing
Next
On Error GoTo 0
End Sub
Hello, Mr. Mishra,
I ran your VBA code but no folder structure created in Test Lab. Please help. Thanks.
Daniel Ho
Hi Daniel,
Are you getting any error? Can you please specify?
i didn’t get any error ….but nothing was created in test lab.
Hi, Mr. Mishra, I did not get any error, but no folder structure created in the Test Lab Module.
Question: why tcCount is 283 but not 1? Since there is only one test in
"SubjectAVP CX ProjectsTest(AVI9999)AVI9999-2013_10_24"
Please help. Thanks.
Daniel
Sub Pull_Test_Cases_in_Lab()
On Error Resume Next
Dim tdConnection
Dim qcUser, qcPassword
Dim qcDomain, qcProject
Dim qcURL
Dim myFolder As String
Dim treeMgr As Variant
Dim myTestFact As Variant
Dim myTestFilter As Variant
Dim myTestList As Variant
'Provide all details
qcUser = "ho_dan"
qcPassword = "ho_dan"
qcDomain = "BDC"
qcProject = "AVP"
qcURL = "http://10.170.93.98:8080/qcbin/"
'Create QC connection object
Set tdConnection = CreateObject("TDApiOle80.TDConnection")
'Initialise the Quality center connection
tdConnection.InitConnectionEx qcURL
'Authenticate username and password
tdConnection.login qcUser, qcPassword
'login to the domain and project
tdConnection.Connect qcDomain, qcProject
If (tdConnection.LoggedIn True) Then
MsgBox "QC User Authentication Failed"
Exit Sub
End If
Set treeMgr = tdConnection.TreeManager
Set myTestFact = tdConnection.TestFactory
Set myTestFilter = myTestFact.Filter
'Apply filter on the last known node
'Suppose you want to map all the test cases of your Project Folder XYZ
'then provide the complete path till XYZ. For example: "^SubjectXYZ^"
myFolder = "SubjectAVP CX ProjectsTest(AVI9999)AVI9999-2013_10_24"
'Get the complete test list in the main folder
Set myTestList = myTestFact.NewList(myTestFilter.Text)
myTestFilter.Filter("TS_SUBJECT") = "^" & myFolder & "^"
'Exit if no test cases found in the main folder
If myTestList.Count = 0 Then
MsgBox "Zero Test Case Found "
Exit Sub
End If
'Get all the test cases available in the above main folder
tcCount = myTestList.Count
MsgBox "Value " & tcCount & myTestFilter.Filter("TS_SUBJECT")
'Traverse through each test cases in testList array you got
For Each tc In myTestList
'Get the Subject Folder Node
Set myTCNode = tc.field("TS_Subject")
'Get Complete path of test case without Test Name
myPath = myTCNode.Path
'Create Folder structure and pull the test cases into test set in test lab from test plan
map_TestCase myPath, tc
Next
Set myTestFilter = Nothing
Set myTestFact = Nothing
MsgBox "Value " & " " & tcCount & " " & myTCNode & " " & myPath & " " & myTestList.Count
Set myTestList = Nothing
MsgBox "Done "
End Sub
'Create Folder stucture and pull the test cases into test set in test lab from test plan
Sub map_TestCase(myPath, myTC)
Dim tcMgr As Variant
Dim myRoot As Variant
Dim newTSTest As Variant
Dim TSFact As Variant
Dim TSFilter As Variant
On Error Resume Next
Set tcMgr = tdConnection.TestSetTreeManager
'Split path for loop
subjectArray = Split(myPath, "")
'in Test Plan folder structure starts with Subject
'while in Test Lab it is "Root ". So we need to change the path
NewPath = "RootAVP Test"
OldPath = ""
For iFolder = 1 To UBound(subjectArray)
'Assign old to new path
OldPath = NewPath
'Get current folder
CurrentSubName = subjectArray(iFolder)
'Build new path
NewPath = Trim(NewPath) & "" & CurrentSubName
'Search Folder
Set newNode = tcMgr.NodebyPath(NewPath)
'Create folder if it does not exist
If newNode Is Nothing Then
Set tcMgr = Nothing
Set tcMgr = tdConnection.TestSetTreeManager
If iFolder = 1 Then
Set myRoot = tcMgr.Root
Else
Set myRoot = tcMgr.NodebyPath(OldPath)
End If 'iFolder
Set newNode = myRoot.AddNode(CurrentSubName)
newNode.Post
End If 'new Node
'If the current folder is the last folder of the array then create the test set
If iFolder = UBound(subjectArray) Then
Set TSFact = newNode.TestSetFactory
Set TSFilter = TSFact.Filter
TSFilter.Filter("CY_FOLDER_ID ") = newNode.Nodeid
TSFilter.Filter("CY_CYCLE ") = CurrentSubName
Set TSList = TSFact.NewList(TSFilter.Text)
'If no test set found in test plan then Test Set folder will
'not be created. If you want to do so uncomment the below if else conditions
'If TSList.Count = 0 Then
' Set TestSet1 = TSFact.AddItem(Null)
' TestSet1.Name = CurrentSubName
' TestSet1.Status = "Open "
' TestSet1.Post
'Else
' Set TestSet1 = TSList.Item(1)
'End If
Set TSTestFact = TestSet1.TSTestFactory
Set TSTestList = TSTestFact.NewList("")
'Initilize the variable to check if any Test case found
foundTCs = 0
If TSTestList.Count > 0 Then
For Each myTSTest In TSTestList
If myTSTest.TestID = Trim(CurrentTest.ID & "") Then
foundTCs = 1
End If
Next
End If
If foundTCs = 0 Then
Set newTSTest = TSTestF.AddItem(CurrentTest.ID)
newTSTest.Post
End If
End If
Set newTSTest = Nothing
Set myTSTest = Nothing
Set TSFilter = Nothing
Set TSTestF = Nothing
Set TSTestList = Nothing
Set TSFilter = Nothing
Set TSFact = Nothing
Set newNode = Nothing
Next
On Error GoTo 0
End Sub
Hi, Mr. Mishra,
I got type mismatch on the line below:
Set myTCNode = tc.Field("TS_Subject ")
Mr. Mishra,
I got type mismatch on the line below:
Set myTCNode = tc.Field(“TS_Subject “)
as attached. Thanks.
Daniel
Sub Pull_Test_Cases_in_Lab()
Dim tdConnection
Dim qcUser, qcPassword
Dim qcDomain, qcProject
Dim qcURL
Dim tcMgr As Variant
Dim myFolder As String
Dim treeMgr As Variant
Dim myTestFact As Variant
Dim myTestFilter As Variant
Dim myTestList As Variant
'Provide all details
qcUser = "ho_dan"
qcPassword = "ho_dan"
qcDomain = "BDC"
qcProject = "AVP"
qcURL = "http://10.170.93.98:8080/qcbin/"
'Create QC connection object
Set tdConnection = CreateObject("TDApiOle80.TDConnection")
'Initialise the Quality center connection
tdConnection.InitConnectionEx qcURL
'Authenticate username and password
tdConnection.login qcUser, qcPassword
'login to the domain and project
tdConnection.Connect qcDomain, qcProject
If (tdConnection.LoggedIn True) Then
MsgBox "QC User Authentication Failed"
Exit Sub
End If
Set treeMgr = tdConnection.TreeManager
Set myTestFact = tdConnection.TestFactory
Set myTestFilter = myTestFact.Filter
'Apply filter on the last known node
'Suppose you want to map all the test cases of your Project Folder XYZ
'then provide the complete path till XYZ. For example: "^SubjectXYZ^"
myFolder = "SubjectAVP CX ProjectsTest (AVI9999)AVI9999-2013_10_24"
'Get the complete test list in the main folder
Set myTestList = myTestFact.NewList(myTestFilter.Text)
myTestFilter.Filter("TS_SUBJECT") = "^" & myFolder & "^"
'Exit if no test cases found in the main folder
If myTestList.Count = 0 Then
MsgBox "Zero Test Case Found "
Exit Sub
End If
'Get all the test cases available in the above main folder
tcCount = myTestList.Count
'Traverse through each test cases in testList array you got
For Each tc In myTestList
'Get the Subject Folder Node
Set myTCNode = tc.field("TS_Subject")
'Get Complete path of test case without Test Name
Set myPath = myTCNode.Path
'Create Folder structure and pull the test cases into test set in test lab from test plan
If (StrComp(myPath, myFolder, 1) = 0) Then
MsgBox myPath & " " & myFolder
map_TestCase myPath, tc
MsgBox "Done "
End If
Next
Set myTestFilter = Nothing
Set myTestFact = Nothing
Set myTestList = Nothing
End Sub
'Create Folder stucture and pull the test cases into test set in test lab from test plan
Sub map_TestCase(myPath, myTC)
Dim tcMgr As Variant
Dim myRoot As Variant
Dim newTSTest As Variant
Dim TSFact As Variant
Dim TSFilter As Variant
On Error GoTo errHandler
Set tcMgr = tdConnection.TestSetTreeManager
'Split path for loop
subjectArray = Split(myPath, "")
'in Test Plan folder structure starts with Subject while in Test Lab it is "Root ". So we need to change the path
newPath = "RootAVP Test"
OldPath = ""
MsgBox newPath
For ifolder = 1 To UBound(subjectArray)
'Assign old to new path
OldPath = newPath
'Get current folder
CurrentSubName = subjectArray(ifolder)
'Build new path
newPath = Trim(newPath) & "" & CurrentSubName
'Search Folder
MsgBox ifolder & " " & newPath
Set newNode = tcMgr.NodebyPath(newPath)
'Create folder if it does not exist
If newNode Is Nothing Then
Set tcMgr = Nothing
Set tcMgr = tdConnection.TestSetTreeManager
If ifolder = 1 Then
Set myRoot = tcMgr.Root
Else
Set myRoot = tcMgr.NodebyPath(OldPath)
End If 'iFolder
Set newNode = myRoot.AddNode(CurrentSubName)
newNode.Post
End If 'new Node
'If the current folder is the last folder of the array then create the test set
If ifolder = UBound(subjectArray) Then
Set TSFact = newNode.TestSetFactory
Set TSFilter = TSFact.Filter
TSFilter.Filter("CY_FOLDER_ID ") = newNode.Nodeid
TSFilter.Filter("CY_CYCLE ") = CurrentSubName
Set TSList = TSFact.NewList(TSFilter.Text)
'If no test set found in test plan then Test Set folder will
'not be created. If you want to do so uncomment the below if else conditions
If TSList.Count = 0 Then
Set TestSet1 = TSFact.AddItem(Null)
TestSet1.Name = CurrentSubName
TestSet1.Status = "Open "
TestSet1.Post
Else
Set TestSet1 = TSList.Item(1)
End If
Set TSTestFact = TestSet1.TSTestFactory
Set TSTestList = TSTestFact.NewList("")
'Initilize the variable to check if any Test case found
foundTCs = 0
If TSTestList.Count > 0 Then
For Each myTSTest In TSTestList
If myTSTest.TestID = Trim(CurrentTest.ID & "") Then
foundTCs = 1
End If
Next
End If
If foundTCs = 0 Then
Set newTSTest = TSTestF.AddItem(CurrentTest.ID)
newTSTest.Post
End If
End If
Set newTSTest = Nothing
Set myTSTest = Nothing
Set TSFilter = Nothing
Set TSTestF = Nothing
Set TSTestList = Nothing
Set TSFilter = Nothing
Set TSFact = Nothing
Set newNode = Nothing
Next
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
End Sub
Daniel,
Remove the extra space that is present after TS_SUBJECT. It worked for me
Set myTCNode = tc.Field(“TS_Subject”)
I tried the same code given by you; however it was having minor issues which I have fixed.
I am facing issue while it is trying to create the folder in test lab:
Set tcMgr = tdConnection.TestSetTreeManager
tcMgr is always remaining empty
Can you please guide where I am missing?
Below is my code that I am using:
Public Sub Pull_Test_Cases_in_Lab()
On Error Resume Next
Dim tdConnection
Dim myFolder As String
Dim treeMgr As Variant
Dim myTestFact As Variant
Dim myTestFilter As Variant
Dim myTestList As Variant
Set tdConnection = CreateObject("TDApiOle80.TDConnection")
tdConnection.InitConnectionEx main.qcURL
tdConnection.Login main.qcUser, main.qcPassword
tdConnection.Connect main.qcDomain, main.qcProject
Set treeMgr = tdConnection.treemanager
Set myTestFact = tdConnection.TestFactory
Set myTestFilter = myTestFact.Filter
'myFolder = "SubjectApplicationsXXXYYY AAA ZZZ"
myTestFilter.Filter("TS_SUBJECT") = "^" & main.myFolder & "^"
Set myTestList = myTestFact.newList(myTestFilter.Text)
If myTestList.Count = 0 Then
MsgBox "Zero Test Case Found "
Exit Sub
End If
tcCount = myTestList.Count
For Each tc In myTestList
Set myTCNode = tc.Field("TS_Subject")
myPath = myTCNode.Path
map_TestCase myPath, tc
Next
Set myTestList = Nothing
Set myTestFilter = Nothing
Set myTestFact = Nothing
End Sub
Public Sub map_TestCase(myPath, myTC)
Dim tcMgr As Variant
Dim myRoot As Variant
Dim newTSTest As Variant
Dim TSFact As Variant
Dim TSFilter As Variant
On Error Resume Next
Set tcMgr = tdConnection.TestSetTreeManager
' Split path for loop
subjectArray = Split(myPath, "")
' in Test Plan folder structure starts with Subject
' while in Test Lab it is "Root ". So we need to change
' the path
NewPath = "Root"
OldPath = ""
For IFolder = 1 To UBound(subjectArray)
'Assign old to new path
OldPath = NewPath
'get current folder
CurrentSubName = subjectArray(IFolder)
'build new path
NewPath = Trim(NewPath) & "" & CurrentSubName
'search Folder
Set newNode = tcMgr.NodeByPath(NewPath)
'create folder if it does not exist
If newNode Is Nothing Then
Set tcMgr = Nothing
Set tcMgr = tdConnection.TestSetTreeManager
If IFolder = 1 Then
Set myRoot = tcMgr.Root
Else
Set myRoot = tcMgr.NodeByPath(OldPath)
End If ' iFolder'
Set newNode = myRoot.AddNode(CurrentSubName)
newNode.Post
End If 'new Node
' if the current folder is the last folder of the array
' then create the testset
If IFolder = UBound(subjectArray) Then
Set TSFact = newNode.TestSetFactory
Set TSFilter = TSFact.Filter
TSFilter.Filter("CY_FOLDER_ID") = newNode.Nodeid
TSFilter.Filter("CY_CYCLE") = CurrentSubName
Set TSList = TSFact.newList(TSFilter.Text)
'If no testset found in test plan then TestSet folder will
'not be created. If you want to do so uncomment the below
'if else conditions
'If TSList.Count = 0 Then
'Set TestSet1 = TSFact.AddItem(Null)
'TestSet1.Name = CurrentSubName
'TestSet1.Status = "Open "
'TestSet1.Post
'Else
Set TestSet1 = TSList.Item(1)
'End If
Set TSTestFact = TestSet1.TSTestFactory
Set TSTestList = TSTestFact.newList("")
'Initilize the variable to check if any Test case found
foundTCs = 0
If TSTestList.Count > 0 Then
For Each myTSTest In TSTestList
If myTSTest.TestID = Trim(CurrentTest.ID & "") Then
foundTCs = 1
End If
Next
End If
If foundTCs = 0 Then
Set newTSTest = TSTestF.AddItem(CurrentTest.ID)
newTSTest.Post
End If
End If
Set newTSTest = Nothing
Set myTSTest = Nothing
Set TSFilter = Nothing
Set TSTestF = Nothing
Set TSTestList = Nothing
Set TSFilter = Nothing
Set TSFact = Nothing
Set newNode = Nothing
Next
On Error GoTo 0
End Sub
Hi Bhavin,
Do have still have the correct code to create test lab structure as like test plan pull the test cases from test plan to test lab.?
could youplease forward me the same on dkmitkari@gmail.com
Hi,
Thanks for this code! I got a few questions:
– in TSList.Count I'm getting a lot but my test cases are only 130.
– What object is tc in the loop of myTestList since object is not recognize thus I'm not getting the myPath, tc variables.
Thanks,
melvin
Hello Melvin,
Can you please share your code
Hi,
I was looking for a code snippet to:
1. Upload test cases from excel to QC
2. Check for existing custom fields added to the test plan test factory
3. Add a new custom field to the test plan test factory
Hi Vishwa,
I have tried the above code and getting subject is empty error can you please post the working code here since m trying this from long time.
I request some one to post the working code for me.
Thanks,
DIVA
Hi Vishwa,
Can you please post the working code here. I request some one to post it if you have it.
Thanks
I am trying to write a VBA excel macro code to upload attachment to a specific test script in Test Lab. I am able to connect to Quality center but not able to identify specific test case in Test Lab
How can we achieve this?
Hi,
could you please help me in creating a macro that uploads the results pass fail from excel to ALM 11. This is of great help for our project with 1000s of test case whose results have to manually updated.
Thanks,
Tina
Hi Tina. Were you able to find/create a macro to do that for you? If yes, would mind sharing it with me?
HI
I am also looking for code which will attached result to test case and pass it. would you mind sharing it with me.
Hi Vishwa,
I need your help to create excel macros which will export the Test case names and their execution status(Pass, fail, No Run etc) from QC Test-lab to Excel sheet.
Could you please help on this.
Thanks a lot in advance.
I need code to upload test scripts into Quality center…Could you please help me on this…
Hi Vishwa,
I am able to connect QC but inned to open QC in Explorer and i want s to go defect Tab from that i needs to get the Linked defects deatils.
Please help me to get the solution for this issue.
Hi,
I have to open ALM in explorer and after i needs to get the linked defects in Defects TAB. please help to get the solution for this qns….
even me too, i could not crate finally the test sets.
I want to fetch release and requirement details from QC with VBA Macro
but the below line is not working
folderFactory = QCConnection.ReleaseFactory
Error = “Wrong number of argument or invalid property assignment”
Please help.
I am unable to execute the above mentioned code and the error is – “Pure virtual function calling”. Please help.
Hi everyone,
please post code for executing testcases in QC for a specific folder using excel macro