Thanks jmonty. I'e tried that as you suggested but it did not work. Every
time I click the button the session object has to be set and logon run and
the Boolean is empty.

The code I have is :
In the Workbook general declarations.

Public blnConnected As Boolean
Public cqSession As Session

In the Button

Dim queryDef As OAdQuerydef
Dim resultSet As OAdResultset
Dim entity As OAdEntity
Set curCell = Worksheets("Linked").Cells(1, 2)
UserName = curCell.Value
Set curCell = Worksheets("Linked").Cells(1, 4)
PassName = curCell.Value
If UserName = "" Or PassName = "" Then
If UserName = "" Then
MsgBox "Please enter your User ID."
Else
MsgBox "Please enter your Password"
End If
Else
Set curCell = Worksheets("Linked").Cells(2, 2)
cqid = curCell.Value
If cqid = "" Then
MsgBox "Please enter a defect ID"
Else
If blnConnected = False Then
Set cqSession = CreateObject("CLEARQUEST.SESSION")
cqSession.UserLogon UserName, PassName, "CSR", AD_PRIVATE_SESSION,
"CSR_Live"
blnConnected = True
End If

Set curCell = Worksheets("Linked").Cells(2, 3)
curCell.Value = ""
Worksheets("Linked").Range("A4:G500").Clear
Set curCell = Worksheets("Linked").Cells(2, 2)
CREATE SQL
sqlString = ??????????
Set resultSetObj = cqSession.BuildSQLQuery(sqlString)
resultSetObj.EnableRecordCount
resultSetObj.Execute

Count = resultSetObj.RecordCount
If Count 0 Then
For RowCount = 1 To Count
fetchStatus = resultSetObj.MoveNext
For Countfield = 2 To 8
colvalue = resultSetObj.GetColumnValue(Countfield)
Set curCell = Worksheets("Linked").Cells(RowCount + 3,
Countfield - 1)
curCell.Value = colvalue

Next
Next
Else
Set curCell = Worksheets("Linked").Cells(2, 3)
curCell.Value = "No Links Found"
End If
End If
End If

"jmonty" wrote:

Declare another gloabl variable:

Global blnConnected as Boolean

Somewhere in the beginning of the vba code in the button (macro)


If blnConnected = false then
'Make connection
blnConnected = True
end if
'Run query here



jmonty


"Alvin" wrote:

I have a global variable declared as "Public cqSession As Session" On the
worksheet I have a button which when clicked connects to Rational Clearquest
and executes a query. within the button it connects using :
Set cqSession = CreateObject("CLEARQUEST.SESSION")
cqSession.UserLogon UserName, PassName, "CSR", AD_PRIVATE_SESSION, "CSR_Live"

This connection/logon takes a long time to process each time the button is
pressed.
Is there anyway I can do the connect/logon the first time the button is
pressed and leave the connection open until the spreadsheet is closed?