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 filterValuesCall Operator.BuildFilter(strFilterFieldName, AD_COMP_OP_IN, filterValues)
Now you can execute your query and get back some resultsThis 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
works great - thanks! Is there a way to get a pick-list in Excel of the available ClearQuest queries.
ReplyDeleteYou can call the GetWorkSpace method on your Session object , and from there call GetAllQueriesList to get all the public quries as an array
ReplyDeleteTo 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
This tool would be really handy for me and I appreciate your work. Thank you for publishing this for others to use.
ReplyDeleteI 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!
This works .. This is just too good. Especially since it returns the complete long description.
ReplyDeleteand 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 )
Try ReportsDNA at http://www.reportsdna.com
ReplyDeleteIt 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.
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
ReplyDelete,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.
to enter a range in filter fields
ReplyDeletehow can we insert a range of data in search filter
ReplyDeleteHave a look at this link http://publib.boulder.ibm.com/infocenter/cqhelp/v7r0m0/index.jsp?topic=/com.ibm.rational.clearquest.apiref.doc/r_querydef_buildfilterop.htm
ReplyDeleteIt shows an example of using the less than operator.
what is the equivalent for Tools-References in Excel 2007.
ReplyDeleteI 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
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.
ReplyDeleteHI, 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
ReplyDeletethanking you a lot again
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.
ReplyDeletethis is great!
ReplyDeletebut 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
yaay..! got it all done... lemme know if anyone needs any help.
ReplyDeletekeyurpatel87@gmail.com
This comment has been removed by the author.
ReplyDeleteKeyur,
ReplyDeleteI am also working on CQ web.
Can you please let me know how to implement the connection between excel and CQ web
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 ?
ReplyDeleteHi,
ReplyDeleteThank 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)
ClearQuestOleServer is missing. Where can get it
ReplyDeleteHello,
ReplyDeleteI 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?
Hello,
ReplyDeleteI 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?
Hello,
ReplyDeleteI 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?
Hello,
ReplyDeleteI 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?
Hello,
ReplyDeleteI 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?
Hello,
ReplyDeleteI 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?
Hello,
ReplyDeleteI 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?
Hello,
ReplyDeleteI 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?
Hello,
ReplyDeleteI 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?
Hello,
ReplyDeleteI 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?
Hello,
ReplyDeleteis 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
Hello,
ReplyDeletei found it. It was BuildEntity.
Is it possible, that i See the ID (not entity.GetDbId), before I commit the entity?
Hi all,
ReplyDeleteI 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
Hi,
ReplyDeleteCan 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
Hi,
ReplyDeleteI 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.
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.
ReplyDeleteThanks in advance
Visesh,
ReplyDeleteI have also faced the same error, I found that the CQ DB name was not correct in my case.
Do you have Clearquest Desktop client exe file?
ReplyDeleteI cannot find the ClearQuest client that must be installed in order for this to be functional
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteI am new to Clearquest and excel data fetch
ReplyDeletePlease 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
---------------------------
Hello!
ReplyDeleteI run into an error when I try to run the line "Set cqsession = New Session".
The error message states "Automation Error. The specified module could not be found." Any advice?
Thanks!
Did you follow these two steps?
ReplyDeleteThe 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.