HP Quality Center – Connect to HP QC using Excel VBA

.

Dear Reader,

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
    • Quality Center Login (Authentication)

      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.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Convert PDF Tables to Excel with VBA | Step-by-Step Guide
Convert PDF Tables to Excel with VBA | Step-by-Step Guide

Convert PDF Tables to Excel with VBA | Step-by-Step Guide How to Convert PDF Tables to Excel Using Excel VBA (Macros) Extracting data from PDF tables into Excel can be tedious, but with Excel VBA (macros), you can automate this process and save time. This guide will...

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

86 Comments

  1. shom

    what if the passing url is in the format:
    https:///qcbin/start_a.jsp

    Getting “Invalid Server Responce” error message during execution.Pls help.

    Reply
    • Vishwamitra Mishra

      Dear Shom,

      As Steve Says above, you need to type the URL till /qcbin only. You need to remove start_a.jsp.

      Reply
  2. Steve

    qcURL = “http://symqc.*********/qcbin”

    Remove “start_a.jsp”. Should work 🙂

    Reply
    • Kumanavel

      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.

      Reply
  3. Vinod

    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.

    Reply
    • Vishwamitra Mishra

      Hi Vinod,

      Is initialization step is getting executed successfully? Which version of QC are you using? Is it ALM 11.0?

      Reply
      • Vinod

        Hi Vishwa,

        How to verify if initialization step is getting executed successfully? Please guide how to verify that and yes its ALM 11.0

        Reply
        • Vishwamitra Mishra

          Hi Vinod,

          try this statement : (for initialization – Row 21)

          tdConnection.InitConnection qcURL

          Reply
          • Vinod

            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″

  4. wenona4330

    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.

    Reply
  5. Krishna

    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

    Reply
  6. Kumar

    i am getting error as “Run time Error 429: Activex component cant create object” I am using ALM 11.
    Can you please help me.

    Reply
    • shivanand

      first login to HP QC with valid credentials, project and domain, click–> help –> add-in –> click Register and wait for the process to complete

      Reply
  7. Rashmi

    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..

    Reply
  8. Riyas

    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

    Reply
  9. manoj

    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

    Reply
  10. nishit

    How to run a Sql command from Excel vba

    Reply
  11. Deb

    The code is working for QC 10.0 but not for 11.0, could you please advise?

    Reply
  12. PG

    I am using QC 10.0 .
    Set tdConnection = CreateObject(“TDApiOle80.TDConnection”) is giving error “Activex component can’t create object”

    Please suggest.
    Thanks

    Reply
    • shivanand

      first login to HP QC with valid credentials, project and domain, click–> help –> add-in –> click Register and wait for the process to complete

      Reply
  13. Kottam

    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!

    Reply
    • Kottam

      I mean, user authentication fails on one PC and works on other PC.

      Reply
      • Kottam

        Issue is fixed with the installation of Quality Center Connectivity Add-In

        Reply
  14. Amrit

    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

    Reply
  15. Supratik

    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?

    Reply
  16. Rahul Dhoke

    Hi, an error is occuring for below code
    Set tdConnection = CreateObject(“TDApiOle80.TDConnection”)

    I am using Excel 2010 abd ALM version 11.

    please suggest??

    Reply
    • Shashank

      please try this code

      set tdConnection=New TDConnection

      now with the help of tdConnection object you can initialize, authenticate and select project.

      Reply
    • Shay

      Hi,

      I am having the same problem failed to create object – Set tdConnection = CreateObject(“TDApiOle80.TDConnection”)

      using ALM 11.52

      any suggestion?

      Reply
  17. Tina

    I am getting a Method InitConnectionEx is failed. ANy help here please.

    Reply
  18. jaiser

    Hi,

    I need VBA code to pull all the parameters of a business components from ALM to an excel sheet.

    Please help.

    Reply
  19. Narayan

    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.

    Reply
    • Karthika

      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/”

      Reply
      • Gyanesh

        Hi,

        How to add OTA COM Type library in vba reference folder?

        Thanks in advance

        Reply
  20. Ann

    Encountered error in URL –> Expected: line number or label or statement or end of statement. I am using HP ALM 11

    Reply
  21. Dhanunjaya

    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

    Reply
  22. Karthika

    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

    Reply
    • Vishwamitra Mishra

      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

      Reply
      • SK

        Thanks Vishwamitra Mishra!!! I am able to get the details in a list with your above mentioned code.

        Reply
  23. srinivas

    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

    Reply
  24. srinivas

    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.

    Reply
    • Vishwamitra Mishra

      Hi Srinivas,

      Try installing HP Quality Center Connectivity” Addin successfully. This should resolve your problem.

      Reply
      • Gyanesh

        Hi Vishwa,

        How to add OTA COM Type library into VBA reference folder?

        Thanks in advance

        Reply
  25. Nag

    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.

    Reply
    • Vishwamitra Mishra

      Hi try re-installing ALM (QC) addins for Excel (especially QC Connection client add-in). This error occurs usually when client is not installed.

      Reply
      • Nag

        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?

        Reply
  26. Prakash krishnan

    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

    Reply
    • Vishwamitra Mishra

      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.

      Reply
      • uma

        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

        Reply
        • Jim Coyle

          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

          Reply
      • santhosh

        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.

        Reply
  27. Nag

    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.

    Reply
  28. priya

    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

    Reply
    • Sam

      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

      Reply
    • NIVETHA

      Hi ,

      I want the code to link a defect with 100 blocked test cases in HP ALM using Macro. Any Macro available for this ?

      Reply
  29. Ajith

    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.

    Reply
  30. Pankaj Kumar

    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.

    Reply
  31. hiten

    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

    Reply
  32. vidya

    I am getting below error
    ActiveX component can not create object.
    please help me out

    Reply
    • paras

      Hi
      Have you got any solution for this?If yes please reply asap.

      Reply
  33. Naveen N

    Hi Vishwa,
    Can you please suggest where to find reference materials to OTA to do scripting using VBA.

    Regards,
    naveen N

    Reply
  34. 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

    Reply
  35. Kiran Kasu

    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

    Reply
  36. Angelo

    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

    Reply
  37. Annie

    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

    Reply
  38. Rashi

    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?

    Reply
    • rakesh

      can you please send me the code for updating the defects in qc with change status,description and screen shot.

      Reply
  39. rakesh

    hi i am new to VBA
    i want to connect to ALM and export all the attacement to excel

    Reply
  40. Anu

    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.

    Reply
  41. Chandru

    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?

    Reply
  42. Chetana

    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.

    Reply
  43. Santhosh

    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.

    Reply
  44. CHRISTEL AULD

    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.

    Reply
  45. keerthana

    When I tried connecting to ALM with the above code, Faced the below error:
    Runtime error 424
    Object required.

    Please help

    Reply
  46. Naresh

    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.

    Reply
  47. Shay

    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?

    Reply
  48. kaos oblong murah

    This is my first time visit at here and i am in fact pleassant to read everthing at single place.

    Reply
  49. Raja Krishnamurthy

    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

    Reply
  50. Krishna

    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

    Reply
  51. 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.

    Reply
  52. Fernando

    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?

    Reply
  53. Biswarup

    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

    Reply
  54. Pranavi

    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

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest