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?

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?

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?