We are doing the same in our company but avoiding a double login just in case the user is already logged into Clarity.
In order to implement this functionality our VBA code uses OLE automation with Internet Explorer to check if the current user is logged into Clarity. You can do the same if you are able to automate your browser from VBA.
If the user is not already logged, our VBA code shows a login form to get the username and password for Clarity.
But If the user is already logged, we read the cookie session from Internet Explorer and send its content when making SOAP requests, so no additional login is needed. We also use this approach to get some url params from Clarity (object id, action, etc) to be able to make SOAP requests with filters automatically without user intervention. So the user perceives that after downloading an excel file from Clarity, the excel file is executed automatically downloading information from Clarity related to the screen he/she was seeing and showing corresponding data lists and charts.
Below you can see the VBA functions we are using to get needed information from Internet Explorer (session, object id, action, etc).
You should notice that in order to enable OLE automation with IE, your VBA project has to include references to these libraries (if I remember properly): "OLE Automation", "Microsoft Internet Controls" and "Microsoft HTML Object Library".
I hope this helps:
'Private Const c_urlPattern As String = "/niku/app?action="
Private Const c_UrlPattern As String = "/niku/"
Private Const c_AUTHCOOKIE_NAME As String = "sessionId"
Private Const c_cookieSep As String = ";"
Private Const c_urlparamSepsIni As String = "=:"
Private Const c_urlparamSepEnd As String = "&"
Private Function GetUrlparam(Curl As String, cUrlparamName As String, Optional cUrlparamSepsIni As String = c_urlparamSepsIni, Optional cUrlparamSepEnd As String = c_urlparamSepEnd) As String
Dim nIni As Integer, nFin As Integer
nIni = VBA.InStr(1, Curl, cUrlparamName, vbTextCompare)
If nIni > 0 Then
nIni = nIni + Len(cUrlparamName)
' comprueba si a continuación de cUrlparamName viene un carácter de cUrlparamSepsIni
If VBA.InStr(1, cUrlparamSepsIni, VBA.Mid$(Curl, nIni, 1), vbTextCompare) > 0 Then
nIni = nIni + 1
End If
nFin = VBA.InStr(nIni, Curl & cUrlparamSepEnd, cUrlparamSepEnd, vbTextCompare)
' si el valor es string delimitado por comillas las quitamos
If VBA.Mid$(Curl, nIni, 1) = """" Then
nIni = nIni + 1
nFin = nFin - 1
End If
GetUrlparam = VBA.Mid$(Curl, nIni, nFin - nIni)
Else
GetUrlparam = ""
End If
End Function
Public Function GetClarityInfo(cUrlPrefix As String, ByRef cSessionId As String, ByRef nId As Long, ByRef cAction As String, ByRef cView As String, Optional cCookieName As String = c_AUTHCOOKIE_NAME, Optional cIdParamName As String = "id") As Integer
'find the right internet explorer webpage
Dim allExplorerWindows As New SHDocVw.ShellWindows
Dim IEwindow As SHDocVw.InternetExplorer
Dim foundFlag As Boolean
10 On Error GoTo ERROR_EXCEL
20 Set allExplorerWindows = New SHDocVw.ShellWindows
30 foundFlag = False
40 For Each IEwindow In allExplorerWindows
50 If InStr(IEwindow.LocationURL, cUrlPrefix) <> 0 Then
60 foundFlag = True
70 Exit For 'found the right IE window URL
80 End If
90 Next
100 If Not foundFlag Then
'DisplayBox = MsgBox("Could not find an open instance of ***." & vbNewLine & vbNewLine & " " & vbOKOnly + vbMsgBoxSetForeground, "Please try again.")
110 GetClarityInfo = -1
120 Exit Function
130 End If
'end find
'get the session from cookie
cSessionId = GetUrlparam(IEwindow.document.cookie, cCookieName, "=", ";")
'get the id url param
nId = VBA.val(GetUrlparam(IEwindow.LocationURL, cIdParamName, c_urlparamSepsIni, c_urlparamSepEnd))
'get the action url param
cAction = GetUrlparam(IEwindow.LocationURL, "action", c_urlparamSepsIni, c_urlparamSepEnd)
'get the view url param
cView = GetUrlparam(IEwindow.LocationURL, "odf_view", c_urlparamSepsIni, c_urlparamSepEnd)
FIN:
970 Set allExplorerWindows = Nothing
980 Set IEwindow = Nothing
985 GetClarityInfo = 0
990 Exit Function
ERROR_EXCEL:
310 GetClarityInfo = -1
320 If Err.Number <> 0 Then
330 Call LogErrorToSheet("GetClarityInfo")
340 End If
350 Resume FIN
End Function