Introduction to HP Quality Center OTA API and Connecting to QC by VBA

        Today I am going to talk about HP Quality Center OTA API. Now for the newbies, HP Quality Center(QC) is a test management tool. There's a good tutorial here for the theoretical part  and manual management of QC. But Manual is Boring!
        So here we are, trying to automate various tedious job, usually done manually. It is a time-consuming process and there is always a possibility of human error. Since we all like to have our deliverables finished by EOD, automating QC is a great start.
        Now QC is basically a web based software, and like all web based soft-wares, it has its own logic set up as URL requests. However, for automating QC in a desktop environment, we are provided with an Object Oriented API (Application Programming Interface).
        Open Test Architecture or OTA is available in HP QC/ALM(Application Lifecycle Management). Download this by following these simple steps:

        1) Log on to HP ALM
        2) Go to Help -> ALM Tools -> HP ALM Connectivity -> Download HP ALM Connectivity
        3) A pop up appears to download TDConnect.exe
        4) Save the file on desktop and Double-Click to run this installer

        And your API Library is installed in your desktop, available to be used in your coding. Now we begin coding for connecting your VBA Project to QC. First I'll explain about the API. The main OTA Library package for ALM is referenced by the name TDAPIOLELib. Every usable class/interface can referenced from the same. Many people have the habit of initializing variables for a QC Project as Object. But this complicates things unnecessarily. If every variable is set as an Object, we would not be able to use the code completion features of VB Editor. Hence, a newbie will not know where to go from a given line of code as there will be no suggestions to help them. Plus if in future, the code was to be updated, the developer might have forgotten what the heck he had written in the past!
        Neways, coming back to our topic at hand that is Connecting to QC, we have to use the TDConnection Class. First we begin a connection with the Server URL of the QC. Now QC can be installed in a local machine, running on local host or on a web server. The logic remains the same. Then after a connection is initiated, we send in the login credentials, that is the user name and password. Once the user is logged in, we have an option of retrieving the Domain and the Project mapped to the user and give the user an option to select from the list. Or we can just take that as an input and directly connect to the project. Once this  is done, we are ready for other CRUD operations using objects/database commands, provided the user has access to the same. Here is the VBA Code:

Dim TDConn as TDAPIOLELib.TDConnection : TDConn = Nothing
Dim QCUrl, QCUsername, QCPassword, QCDomain, QCProject as String

' I am taking them as preset values. 
' User input with required validations is a better way to handle this.
QCUrl = "http://XXXX/qcbin"
QCUsername = "MyUser"
QCPassword = "MyPwd"
QCDomain = "MyDomain" 
QCProject = "MyProject"

' Now the real code begins.
'Add an Error Handler for bugs
On Error goto ERRHANDLER
' Create an instance/object of the TDConnection class
Set TDConn = CreateObject("TDApiOle80.TDConnection") 
' I quite haven't figured out why its TDApiOle80 and not TDAPIOLELib.
' But its TDApiOle80 for VBA and TDAPIOLELib for VB.Net
TDConn.InitConnectionEx QCUrl
If TDConn.Connected then
    TDConn.Login QCUsername, QCPassword
    If TDConn.IsLoggedIn then
        TDConn.Connect QCDomain, QCProject
        If TDConn.ProjectConnected then
            MsgBox "QC is Connected!"
        Else
            err.raise vbObject+1, "QC Project could not be connected!", "QC Project"
        End If
    Else
        err.raise vbObject+1, "Login Failed! Please check the Username/Password","Login"
    End If
Else
    err.raise vbObject+1, "Could not connect to the Server!", "Server Error"
End If

ERRHANDLER:
    msgbox err.Number & " : " & err.Description

        Well there you go. You now have the code to connect to QC. Have Fun exploring the possibilities! For details about the flags I have used, like Connected, IsLoggedIn and ProjectConnected refer to the OTA Reference available in QC/ALM Help.

Thanks and keep visiting!