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
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.
Hi can any one help to pull only the test set name and count of test cases from qc?
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
Hi,
How to import testcases which are executed with a particular date range?
Thanks
The code is returning an error..syntax wrong at first line
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
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
I got the same result. Could you please explain what to do next.
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.
Vishwamitra,
I fetched field TS_VC_CUR_VER of test cases but got blank.
Hi,
I am getting “QC User Authentication Failed” Error. But my url and id and password is correct. please help me
Hi Karthika,
I Hope.. this issue is resolved !! If yes, please share the trick with the world, if not do let me know.
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
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.
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.
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
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 🙂
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
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
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
This issue is still not resolved. I am still getting QC User Authentication Failed””
What is the change in login syntax ?
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.
I am getting Runtime Error ‘429’ ActiveX component can’t create object
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
Now to use SQL query in VBA to pull details from QC
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.
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.
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?
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??
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
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
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.
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 StringDim 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
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…:)
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.
Set tdConnection = CreateObject(“TDApiOle80.TDConnection”)
I am getting error on this line. Can some please explain why?
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”))
Hi can any one help by giving code for “storing run attachments (reports) of a test case in local from alm ?
Hi can any one help by giving code to store run attachments(report after run) in local from qc?
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
Keep fPath as Blank and then run the code. It should work.
Desccription, StepDescription, Expected result are not getting downloaded. please help.
Can you provide the code for extract the test scripts status from test lab?
Can I have the solution for Path as it is not taking expected path. Please provide me solution ALM 12.01 used for testing
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
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
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”
Active X Component Error while running the above code. Please help me out of this
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
@Viswa: Good Day. For Each Tcase i8n TestList. This statement throwing error saying like Type Mismatch.
Just need your suggestion to fix this issue
For Each TestCase In TestList –> Facing Type Mismatch error. Kindly suggest