This is the first article of HP QC VBA Tutorial. In this article I will show you the first step which you should know before doing any automation related to Quality Center using Excel Macro. That first step is nothing but connecting to the Quality Center using excel macro. As you might have seen on the Quality Center UI (User Interface), Login to Quality Center consists of two steps:
- 1. Authenticate your User ID and Password
- 2. On Successful Authentication all the Domains and Projects appear in the below dropdown
In the same way, QC connection using VBA is also done in 2 steps:
First Authenticate your credentials (User ID and Password)
Then connect to QC using Domain and Project names.
Sub ConnectToQualityCenter()
Dim qcURL As String
Dim qcID As String
Dim qcPWD As String
Dim qcDomain As String
Dim qcProject As String
Dim tdConnection As Object
On Error GoTo err
qcURL = <QC URL> 'Example : https://<server url>/qcbin
qcID = <your User ID>
qcPWD = <Your password>
qcDomain = <Domain Name>
qcProject = <Project Name>
'Display a message in Status bar
Application.StatusBar = "Connecting to Quality Center.. Wait..."
' Create a Connection object to connect to Quality Center
Set tdConnection = CreateObject("TDApiOle80.TDConnection")
'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
'On successfull login display message in Status bar
Application.StatusBar = "........QC Connection is done Successfully"
Exit Sub
err:
'Display the error message in Status bar
Application.StatusBar = err.Description
End Sub
Note: URL which you are passing, should always be till https://<server url>/qcbin only.
Using the above code, now you are connected with the Quality center for a given Domain and project. Object tdConnection as defined above can be used to access each and every objects available in the Quality Center, like Test Cases, Requirements, Defects etc.
In the coming articles, we will learn more about the objects inside the quality center after authentication is successful.
what if the passing url is in the format:
https:///qcbin/start_a.jsp
Getting “Invalid Server Responce” error message during execution.Pls help.
Dear Shom,
As Steve Says above, you need to type the URL till /qcbin only. You need to remove start_a.jsp.
qcURL = “http://symqc.*********/qcbin”
Remove “start_a.jsp”. Should work 🙂
When I am passing an ip address, so “http://10.20.30.44/qcbin” as the parameter for InitConnectionEx in VBA, it gives an error message saying “Server not available”. However, I am able to login using the same url in the browser. Kindly help.
Hi,
Am using a URL like “http://10.1XXXXXX:8080/qcbin”, but am not able to connect, it says “QC User Authentication Failed”.
Am able to login through GUI with the same username and password, please help.
Hi Vinod,
Is initialization step is getting executed successfully? Which version of QC are you using? Is it ALM 11.0?
Hi Vishwa,
How to verify if initialization step is getting executed successfully? Please guide how to verify that and yes its ALM 11.0
Hi Vinod,
try this statement : (for initialization – Row 21)
tdConnection.InitConnection qcURL
Hi Vishwa,
Am getting a error message as “OTA version is not compatible with the ALM server:http://10.XXXX/qcbin/wcomsrv.dll.OTA version:11 OTA build number: 5934. Server version: 11, server build number: 7274″
Hello. Have you any idea how to make simple a web site mobile phone friendly?
I’m looking for a theme or plugin that could possibly resolve this issue. If you have any suggestions, please share.
Brief question that’s totally out of theme.
My web-site appearances unusual when exploring.
Hi Vishwa,
Greetings . Actually I’ve got an issue while working with Application in VBA. The Application connects to Quality Center and closes the connection at end . As of now as some enhancements has to be done ,the App is run in debugging mode many times. In case if debugging stops again new connection has to be setup, which inturn creates multiple connections. So can you tell how to release the existing connection when debugging is stopped. I’ve got the code but is there any condition in vba to identify the clicking of reset button. Help Appreciated
Thanks,
Krishna
i am getting error as “Run time Error 429: Activex component cant create object” I am using ALM 11.
Can you please help me.
first login to HP QC with valid credentials, project and domain, click–> help –> add-in –> click Register and wait for the process to complete
Hi,
I am using the above code and getting following error on “Set tdConnection = CreateObject(“TDApiOle80.TDConnection”)” – “Compile Error: Object Required”. I am using QC 11.0 and Excel 2007.Please help..
I need to enter data to a User Field in Defects “#01 Description” for the given defects. Could you please help me identify the objects for the same
I am using the above code but i was not able to connect to HP ALM. I am getting “Invalid server response”. Kindly help what might have gone wrong.
Thanks in advance.
Regards,
Manoj
How to run a Sql command from Excel vba
Please visit this page
http://learnexcelmacro.com/wp/2011/11/sql-connection-string/
The code is working for QC 10.0 but not for 11.0, could you please advise?
I am using QC 10.0 .
Set tdConnection = CreateObject(“TDApiOle80.TDConnection”) is giving error “Activex component can’t create object”
Please suggest.
Thanks
first login to HP QC with valid credentials, project and domain, click–> help –> add-in –> click Register and wait for the process to complete
Hi,
I’m able to use the code successfully. Thank you!!
However, can I know which version of QC, ALM and excel are required to use it successfully? I can use in one PC but not the other and I have the same version of excel and QC on both.
Thank you!
I mean, user authentication fails on one PC and works on other PC.
Issue is fixed with the installation of Quality Center Connectivity Add-In
Hi Vishwamitra,
While connecting to ALM 12.01 using excel macro, I’m getting an error
“Failed to get server settings”.Can you please check this?
I used the code which you had posted.
Selected OLE Automation and OTA COM type library in tools->References section.
Thanks for your help in advance
Hi Vishwa,
I have used the above vb code and modified it as per my needs. The issue is that out of 5 times when i am running this vb. 4 times my excel hangs up and the application closes and for once i was able to get the results in the excel sheet without the application being closed or hanged. Can you please help me here by sharing some knowledge how to fix this issue?
Hi, an error is occuring for below code
Set tdConnection = CreateObject(“TDApiOle80.TDConnection”)
I am using Excel 2010 abd ALM version 11.
please suggest??
please try this code
set tdConnection=New TDConnection
now with the help of tdConnection object you can initialize, authenticate and select project.
Hi,
I am having the same problem failed to create object – Set tdConnection = CreateObject(“TDApiOle80.TDConnection”)
using ALM 11.52
any suggestion?
I am getting a Method InitConnectionEx is failed. ANy help here please.
Hi,
I need VBA code to pull all the parameters of a business components from ALM to an excel sheet.
Please help.
hi,
Thanks for your tips on QC connection.
Are there examples to connect to the objects inside the quality center after authentication is successful? I am looking at dashboard reports from QC.
I tried the above code..while running I am geting error as “Failed to Login”. Need help?
Url Used:https://alm.bmwgroup.net/qcbin
Hi,
Have u added the OTA Type com component ?
If not, pls do to avoid login issue
Or, you have to give the url till QCBin
Sample mine: qcURL = “https://almcomcast1152.saas.hp.com/qcbin/”
Hi,
How to add OTA COM Type library in vba reference folder?
Thanks in advance
Encountered error in URL –> Expected: line number or label or statement or end of statement. I am using HP ALM 11
Hi,
Could you please share me the QC object model flow. I tried to get this info in Google but I didn’t get it
Regards,
Dhanunjaya
Dear All,
I am able to connect into QC now..
Whats my plan is
1. Need to connect with userid and password ( Done successfully )
2. Need to get the domain and project into listbox once login successfull.
Anyone pls help on step 2
Hi Karthika,
you can try following code to get the list of domains available for your ID and Project list for the selected Domain Name
Public Sub getdomains()
Dim domainCount As Integer
Dim i As Integer
‘ get the number of domains which are available on the TDServer
domainCount = QCConnection.VisibleDomains.Count ‘DomainsList.count
If domainCount <= 0 Then Exit Sub ' in case there are no domains ' re-allocate the array of the projects ReDim domainarray(domainCount) For i = 1 To domainCount domainarray(i) = QCConnection.VisibleDomains(i) ' DomainsList(i) Next i End Sub Public Sub getProjects(domain) Dim projectCount As Integer Dim i As Integer Dim projectList As TDAPIOLELib.List If Len(domain) <= 0 Then MsgBox ("Domain name can not be empty!") Exit Sub End If Set projectList = QCConnection.VisibleProjects(domain) ' .ProjectsListEx(relatedDomain) ' get the number of projects which are available on the TDServer projectCount = projectList.Count If projectCount <= 0 Then Exit Sub ' in case there are no projects ' re-allocate the array of the projects ReDim projectarray(projectCount) For i = 1 To projectCount projectarray(i) = projectList(i) Next i End Sub
Thanks Vishwamitra Mishra!!! I am able to get the details in a list with your above mentioned code.
Hi,
While logging into HP QC,i am getting “ActiveX Component Can’t Create Object”.I am using windows 7.Could you please help me hot to get it resolved
Hi,
I am getting error “ActiveX component cant create object” while logging in ate create object line in the code.I am using windows 7.Can you please help me how to resolve this.
Hi Srinivas,
Try installing HP Quality Center Connectivity” Addin successfully. This should resolve your problem.
Hi Vishwa,
How to add OTA COM Type library into VBA reference folder?
Thanks in advance
I am getting “ActiveX component cant create object” error at the line “tdConnection.InitConnectionEx qcURL”. Though CreateObject has been executed successfully. I am passing this as “http://****:8080/qcbin” as my url.
Can anyone help me in resolving this issue.
Hi try re-installing ALM (QC) addins for Excel (especially QC Connection client add-in). This error occurs usually when client is not installed.
Thank you for your reply and its working now. I am trying to read the values in the report in Analysis View module of ALM. I could able to traverse till the report. But I could not able to read the data from it. Can you help in this regard?
Hi,
Am using HP-ALM 12.0.1. In my Excel Macro VBA code everythig is fine. I didnt get any error and all. but QC is not opening
Hi Prakash, This will make a connection to ALM (at object/db level and then you can access everything from QC) but it will not open the QC in an explorer.
How to get QC to get open in an explorer. I am getting error as “Server not available”. Can you help me on how to get open in an explorer
Hi Vishwa
I just find your website late after work and finish half day on a Friday so I logged on and signed up. Excellently written and well explained and from a Scottish man from Glasgow high praise indeed my friend. However I digress, like Prikash I have the exact same issue.
I can code a bit but I am a software tester trying to be a Jack of all trades (is that an Indian expression? 🙂 ). It would be so time saving and productive to log into QC automatically and download all the test plan scripts with steps as in your excellent example and code on your web page. Getting round the connection problem would open up an ocean of adventure.
Cheers
Jim
Hi,
I am not able to connect to ALM, getting “failed to get server settings” Run time error at tdc.InitConnectionEx.
Set tdc = CreateObject(“TDApiOle80.TDConnection”)
qcurl = “https:///qcbin”
tdc.InitConnectionEx qcurl
I am using ALM 12.53
Kindly Help.
hi santhosh,
you sure of your URL? I think a part of URL is missing and you have a 3 “/”. qcURL is URL minus start_a.jsp
exemple:
browser => https://MONDOMAINE/qcbin/start_a.jsp
macro => https://MONDOMAINE/qcbin/
can any one help me in reading the values from data grid of a report in Analysis View? I could navigate till the required report name, but got struck in fetching the values from data grid. Any help is really helpful and appreciated.
Hi
I have wrote a macro which connects to ALM and runs some scripts…now i want my code to check the scheduler status like suppose 2 scripts are done running the 3rd scripts….but i cant find any code to check the qc scheduler status….can you pls help me with this
Hi
I am using ALM 12
Could you pleas ehelp me with the script that connects to ALM 12 and provides me the Test Case Run(from test Lab) and Defects Data(I can customize it if I have base script).
Thanks in Advance
Hi ,
I want the code to link a defect with 100 blocked test cases in HP ALM using Macro. Any Macro available for this ?
Hi,
I am trying to control Dashboard module in QC using VBA. But I failed to get the Object model for Dashboard module. Please help me to solve issue.
Thanks in advance.
Hey Viswa,
I need to do a Requirements Mapping to our test cases in Test Plan. Is there any VBA code you can advise with the Form which needs to be created on which the macro will trigger.
Hi,
I used the above code to connect.Now I need further code to extract the dashboaard results to the excel.
Please help with its code.
ALM11
I am getting below error
ActiveX component can not create object.
please help me out
Hi
Have you got any solution for this?If yes please reply asap.
Hi Vishwa,
Can you please suggest where to find reference materials to OTA to do scripting using VBA.
Regards,
naveen N
HI Viswa,
I ran the macro from VBE (ALT+F11 and inserted a module).
The macro was successfully executed but i did not see the QC opneded in internet explorer.
Please suggest.
Regatds,
Naavee N
Hi Viswa,
Need a help from you to close the manual runner window when we click on the OK button on message pop up window.
Requirement: When a test case is in checkout status, user should not be allowed to run the test case in test lab with message popup. we have successfully developed code till message pop up. Once user clicks on the OK on message box, manual runner window should close. Please help to on this..
Thanks a million in Advance,
Kiran Kasu
Hi,
I’m trying to run the script, but my execution doesn’t work.
At the step
tdConnection.InitConnectionEx qcURL
the script goes to following step
Application.StatusBar = err.Description
I’m sure of values of the variables (qcURL, qcID and so on), and I can go to my QC via browser without problems.
What is the problem?
Thank’s in advance.
Angelo
QCConnection.Login UserName, Password
Getting error as “Failed to Login” on this step. I ahev also added OTA reference but this error is not resolved. I am using ALM 12.01
Hi,
i’ve been trying to make a macro to update defects in qc,
where i’m able to change status, description and add screenshot,
but i’m not able to update comment section of the defect,
can you help?
can you please send me the code for updating the defects in qc with change status,description and screen shot.
hi i am new to VBA
i want to connect to ALM and export all the attacement to excel
When I am trying to run the above code, i am getting the following error – “ActiveX Component can not create Object”. I have installed ALM excel add-in already.
Hi,
I updated my QC 12.2 from QC 11.0.
I was using a macro for Admin Customization with v11.0
Now I am getting an error in logging in line and results Login Failed.
I had a glance at API references from the vendor site and it resides same as that.
Can anybody there to help me on this?
Hi,
How can I access the QC the, if it is not visible in internet explore?
I want to take the export of the tasks shown over the screen.
Hi,
I am not able to connect to ALM, getting “failed to get server settings” Run time error at tdc.InitConnectionEx.
Set tdc = CreateObject(“TDApiOle80.TDConnection”)
qcurl = “https:///qcbin”
tdc.InitConnectionEx qcurl
I am using ALM 12.53
Kindly Help.
Great writing – BTW , if anyone has been looking for a AL DoR A-3 , I filled out a blank version here https://goo.gl/Fe4dIW.
When I tried connecting to ALM with the above code, Faced the below error:
Runtime error 424
Object required.
Please help
Hi Vishwa, Your post is really nice. But somehow the above code is working in my Windows 8.1 + Excel 2010. The same code is not working in Win 7 (64 Bit) + Excel 2010. I see error “Active X Component cannot create object”. FYI, I added OTA COM Library in the reference and done all the steps that i searched in all the forums. But still no luck. It is really very important for me to work in win 7 + Excel 2010. Could you please let me know what am doing wrong. Thanks in advance.
Hi,
I am using HP QC 11.52 and when i trying to run the code i am getting an error – ActiveX component can’t create object.
Excel 2016 + Win 10.
does anyone has the solution?
This is my first time visit at here and i am in fact pleassant to read everthing at single place.
Hi,
I am using hp alm 12.53, I am getting the error when connecting to ALM.
“ActiveX Component can’t create object”
When I try to do click–> help –> add-in –> click Register and wait for the process to complete, After clicking on add-in, the page is not getting loaded for me to register. Is there any other way I could handle this in my code.
Thanks
Raja
Hello Views,
Thank you for posting a good article related HP ALM. But I don’t know where to execute the code.because I am new to VB /Macro scripting .So can you please let me know where can I execute the code ( means on excel then how to execute it).
Krishna
Hi Viswa,
Greetings for a day !
Thanks for posting valuable code but I am getting error while running the code using notepad.vbs.showing output line 2 char 11 Error : Expected end of statement .code 800A0401.
Source – Microsoft VB script compilation error
kindly check and resolve the issue.
Hello,
I am using Win10 + Excel 2016 + HP ALM QC 12.53.
Like other comments above, I am getting the message “ActiveX component cant create object”.
I have already performed the following steps:
– run from browser https:///qcbin/start_a.jsp?common=true and wait process to be completed
– installed MS Excel Add-In successfully
– installed HP Quality Center Connectivity successfully
Even done those steps, I am still getting this error message.
Could anyone please suggest a solution for this?
Hello –
While trying to connect ALM using VBA, I am getting 429 error “ActiveX component cannot create Object” and its failing. Any idea how to resolve this issue.
Thank You
Hi –
I have used the same above code for ALM 12.55 version and geting QC sucessfully connected message but ALM is not opened or entered any values like username,password. What is the next step ,I am trying to update test case status in test lab