Wednesday, 2 July 2008

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

41 comments:

  1. works great - thanks! Is there a way to get a pick-list in Excel of the available ClearQuest queries.

    ReplyDelete
  2. You can call the GetWorkSpace method on your Session object , and from there call GetAllQueriesList to get all the public quries as an array

    To execute one of those queries , on your Session object call OpenQueryDef

    Are use this querydef instead of building one.

    Look at the clearquest api documentation for more info on
    the WorkSpace object:

    http://publib.boulder.ibm.com/infocenter/cqhelp/v7r0m0/index.jsp?topic=/com.ibm.rational.clearquest.apiref.doc/default_topic.htm

    ReplyDelete
  3. This tool would be really handy for me and I appreciate your work. Thank you for publishing this for others to use.

    I downloaded your code and am trying to run a macro in excel. I get a visual basic error message that just says "toolbars". To confirm that this wasn't becuase of an error in my part of creating the macro, I downloaded your xls file. I got the same error.

    Do you have any suggestions? I'm new to VBS. Thanks!

    ReplyDelete
  4. This works .. This is just too good. Especially since it returns the complete long description.

    and for this issue-
    I get a visual basic error message that just says "toolbars" , check
    - ensure Must have a reference to ClearQuestOLEServer
    '(Go to Tools>References and look in the Available references list)
    - use the sql names for the CQ entities ( display fields , search filters )

    ReplyDelete
  5. Try ReportsDNA at http://www.reportsdna.com

    It creates great reports from ClearQuest as well as other sources. It can even automatically email the report, post it Sharepoint and print out copies. I use it religiously.

    ReplyDelete
  6. Beg your help, I want to connect to the database schema of CQ, the name of database schema is: test_schema, the use name is: test
    ,the database on the another computer, the ip address is: 192.168.0.3,when i run my code(italic), VBA run fail. my code:
    ------------------------
    Dim CQObject as object
    Dim varCQDbs as variant
    CQObject = CreateObject("CLEARQUEST.SESSION")
    For Each varCQDbs In CQObject.GetAccessibleDatabases("192.168.0.3\test_schema", test, "")
    ....
    Next
    -------------------
    How can i connect to ClearQuest Database on another computer, thanks.

    ReplyDelete
  7. to enter a range in filter fields

    ReplyDelete
  8. how can we insert a range of data in search filter

    ReplyDelete
  9. what is the equivalent for Tools-References in Excel 2007.

    I am having difficulites to do the following... in excel 2007

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

    Thanks
    Saran

    ReplyDelete
  10. HI, your feed worked wonder. thanks a lot. I am running public query through VBA using CQ API's, one of my two Queries has dynamic filter, a dynamic filter pop up appears when query is executed through CQ. how can i get that pop up in VBA or how to send values to that pop from my macro. IMP:- i do not have permision to creat or edit the query.

    ReplyDelete
  11. HI, your feed worked wonder. thanks a lot. I am running public query through VBA using CQ API's, one of my two Queries has dynamic filter, a dynamic filter pop up appears when query is executed through CQ. how can i get that pop up in VBA or how to send values to that pop from my macro. IMP:- i do not have permision to creat or edit the query

    thanking you a lot again

    ReplyDelete
  12. Excellent, it didn't work initially, then I realised there wasn't an option to enter DBSet name, I have modified the macro to include DBSet as well and then it worked like a dream.

    ReplyDelete
  13. this is great!
    but does this also work with Clear Quest Web.
    I need to fetch a few of my queries on CQ Web to excel. but need to automate this since it is required to be done regularly.

    -keyur
    keyurpatel87@gmail.com

    ReplyDelete
  14. yaay..! got it all done... lemme know if anyone needs any help.
    keyurpatel87@gmail.com

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Keyur,

    I am also working on CQ web.

    Can you please let me know how to implement the connection between excel and CQ web

    ReplyDelete
  17. It did not work for me it pop ups and windows with "red x " any idea what the error is about ? i have give userid, password, dbname, recordtype name. Does it need dbsetname too ?

    ReplyDelete
  18. Hi,
    Thank you very much, it works fine for me.
    I wonder how to extract all the field of a given record type (w/o any filter)
    the aim is to generate metrics using exel pivot table
    Thanks again
    Stephane
    (France)

    ReplyDelete
  19. ClearQuestOleServer is missing. Where can get it

    ReplyDelete
  20. Hello,
    I see In ClearQuestOleServer the Function EditEntity. Is it possible to add ja new Entity (Finding, Change Request and so one) in Clear Quest with VBA?

    ReplyDelete
  21. Hello,
    I see In ClearQuestOleServer the Function EditEntity. Is it possible to add ja new Entity (Finding, Change Request and so one) in Clear Quest with VBA?

    ReplyDelete
  22. Hello,
    I see In ClearQuestOleServer the Function EditEntity. Is it possible to add ja new Entity (Finding, Change Request and so one) in Clear Quest with VBA?

    ReplyDelete
  23. Hello,
    I see In ClearQuestOleServer the Function EditEntity. Is it possible to add ja new Entity (Finding, Change Request and so one) in Clear Quest with VBA?

    ReplyDelete
  24. Hello,
    I see In ClearQuestOleServer the Function EditEntity. Is it possible to add ja new Entity (Finding, Change Request and so one) in Clear Quest with VBA?

    ReplyDelete
  25. Hello,
    I see In ClearQuestOleServer the Function EditEntity. Is it possible to add ja new Entity (Finding, Change Request and so one) in Clear Quest with VBA?

    ReplyDelete
  26. Hello,
    I see In ClearQuestOleServer the Function EditEntity. Is it possible to add ja new Entity (Finding, Change Request and so one) in Clear Quest with VBA?

    ReplyDelete
  27. Hello,
    I see In ClearQuestOleServer the Function EditEntity. Is it possible to add ja new Entity (Finding, Change Request and so one) in Clear Quest with VBA?

    ReplyDelete
  28. Hello,
    I see In ClearQuestOleServer the Function EditEntity. Is it possible to add ja new Entity (Finding, Change Request and so one) in Clear Quest with VBA?

    ReplyDelete
  29. Hello,
    I see In ClearQuestOleServer the Function EditEntity. Is it possible to add ja new Entity (Finding, Change Request and so one) in Clear Quest with VBA?

    ReplyDelete
  30. Hello,
    is it possible to enter a new Entity (Change Request, Finding...).
    Until now i only edit a entity with session.EditEntity

    I not found the function do enter a entity in ClearQuest.

    Regards

    ReplyDelete
  31. Hello,
    i found it. It was BuildEntity.

    Is it possible, that i See the ID (not entity.GetDbId), before I commit the entity?

    ReplyDelete
  32. Hi all,
    I am trying to use Macro in Excel 2010 to query data from ClearQuest, but after follow the instruction from this link,
    I am still getting some runtime error, saying that "Run-time Error '429', ActiveX component can't create object". As you have got this work, can you provide me some guidance?
    Btw, are you using Excel 2003-2007 or Excel 2010?

    Thanks a lot,
    YL

    ReplyDelete
  33. Hi,

    Can you please let me know what do you mean by "The first thing to do is to import the module clearquest.bas from C:\Program Files\Rational\Clearquest or wherever cq is installed"

    I have a file with clearquest.bas do I have to import it in excel or what should i do with it.

    Can you also let me know what should we put in "record type" in excel

    ReplyDelete
  34. Hi,

    I tried to run the file but i am currently getting an Run Time error saying :Run Time Error :13224
    The database 'DWP_BPM' belonging to dbset 'DWP_BPM' is not registered.

    ReplyDelete
  35. I have enter the fields and then there is error screen with "RED CROSS" and no other comment in the error message. Please help me the same asap.
    Thanks in advance

    ReplyDelete
  36. Visesh,
    I have also faced the same error, I found that the CQ DB name was not correct in my case.

    ReplyDelete
  37. Do you have Clearquest Desktop client exe file?

    ReplyDelete
  38. I cannot find the ClearQuest client that must be installed in order for this to be functional

    ReplyDelete
  39. It has been some time since I visited website with such high quality information. Thank you so much for providing such helpful information. This is really informative and I will for sure refer my friends the same. Thanks.excel vba consultant

    ReplyDelete
  40. I am new to Clearquest and excel data fetch
    Please help me

    I am getting below error

    ---------------------------
    Microsoft Visual Basic
    ---------------------------
    The database "dbname" belonging to the dbset "dbsetname" is not registered.

    Please first make sure you entered the correct database name and the correct dbset name, and then make sure the database and the dbset are registered.
    ---------------------------
    OK Help
    ---------------------------

    ReplyDelete