Using ClearQuest API from VB 6 or VBA

On my current project, I found myself exporting my team's list of activities from clearquest to excel over and over again. This made me look at what clearquest offers in terms of an API. Luckily enough, there was an API available, however whatever documentation that google threw at me, was pants.

I created an excel VBA macro to connect to clearquest and pull whatever data I wanted. Another useful use of the API is wto create some kind of integration tool thing between subversion and clearquest. I haven't had time to do this though.

You can download the excel workbook which contains my vba macro from here. You need access to a clearquest database and have the clearquest client installed for this to work.

Here's an explanation of the code which is contained in the "ThisWorkbook" object if you open the Visual Basic editor ( Tools > Macro > Visual Basic Editor) .

I would recommend that you have the Rational ClearQuest API Reference open while you're going through this.

The first thing to do is to import the module clearquest.bas from C:\Program Files\Rational\Clearquest or wherever cq is installed. This module contains a whole load of useful contstants that you may need later on.

Next open Tools>References and look in the Available references list and make sure "ClearQuestOLEServer" is selected.

Now for some code. You need to create a Session object to connect to clearquest. You need to supply a username, password and name of the clearquest database you are connecting to

'LOGIN - Create CQ Session
'==========================
Dim cqsession As Session
Set cqsession = New Session

Call cqsession.UserLogon(strUsername, strPassword, strCQDB, AD_SHARED_SESSION, "")

'Create Query
'===============
Set querydef = cqsession.BuildQuery(strRecordType)


'Fields to display
For Each displayField In displayFields
querydef.BuildField (displayField)
Next displayField

displayFields is simly an array of strings where each string is a name of a field.

Next the filters for the query need to be added.
This creates an AND operator (the constant AD_BOOL_OP_AND is from the module that was imported)

Set Operator = querydef.BuildFilterOperator(AD_BOOL_OP_AND)

This filter added below specifies that the value of the given field (strFieldFilterName) must be one of the values in the array filterValues

Call Operator.BuildFilter(strFilterFieldName, AD_COMP_OP_IN, filterValues)

Now you can execute your query and get back some results

This creates and populats a ResultSet object from the query

Dim rsltset As Object
Set rsltset = cqsession.BuildResultSet(querydef)
Call rsltset.Execute

Now you can iterate through rsltset using rsltset.MoveNext and calling rsltset.GetColumnValue(Column) to get the values.

At the end call cqsession.SignOff so it closes the clearquest session cleanly.

That's it. Looking at the example in the workbook you should be able to create and execute your own queries. This will work in VB 6 too.

The source code for everything here can be downloaded from here.

If you just want the code without downloading the excel workbook , download from here.

Further reading:

Rational ClearQuest API Reference