Excel and Atom Feeds

poolhall said:

There's no point in trying it on another computer. If you're receiving the login page, it means the server didn't like something in the request and redirect the client back to the login page. It means there must be something wrong with the request, but I am not sure what, sorry. Will take another look at it later.

If I can't figure it out, then it totally makes sense doing it another way, by IE automation.

Click to expand...

poolhall said:

Mark, there was a big mistake in my code. The second request should've been POST, of course, not GET. I am re-posting the entire corrected code.

Code:

Public Type Ngx_Session
    OldSessionID As String
    SessionID As String
    FormActionURL As String
    LtValue As String
End Type

Sub Test_login()
Dim mySession As Ngx_Session
    Ngx_EstSession mySession
'    Debug.Print mySession.FormActionURL
'    Debug.Print mySession.SessionID
'    Debug.Print mySession.LtValue
    Ngx_Login mySession, "myusername123", "mypassword123"
End Sub

Private Sub Ngx_EstSession(sess As Ngx_Session)
Dim xmlReq As ServerXMLHTTP
Dim servResp As String
Dim pos As Long

Set xmlReq = New ServerXMLHTTP

xmlReq.Open "GET", "https://secure.ngx.com/ngxcs/home.html", False
xmlReq.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E)"
xmlReq.setRequestHeader "Connection", "keep-alive"
xmlReq.setRequestHeader "Host", "secure.ngx.com/"
xmlReq.send

If xmlReq.Status <> 200 Then MsgBox "Error occured: " & xmlReq.statusText: Exit Sub

servResp = xmlReq.responseText

sess.SessionID = Split(Replace(xmlReq.getResponseHeader("Set-Cookie"), "JSESSIONID=", ""), ";")(0)

pos = InStr(1, servResp, "action=""", 1) + 8
sess.FormActionURL = "https://secure.ngx.com" & Mid(servResp, pos, InStr(pos, servResp, Chr(34), 1) - pos)
pos = 0

pos = InStr(1, servResp, "name=""lt"" value=""", 1) + 17
sess.LtValue = Mid(servResp, pos, InStr(pos, servResp, Chr(34), 1) - pos)

pos = InStr(1, servResp, "action=""/", 1) + 8
sess.OldSessionID = Right(Mid(servResp, pos, InStr(pos, servResp, Chr(34), 1) - pos), 32)

End Sub


Private Sub Ngx_Login(sess As Ngx_Session, usrName As String, pwd As String)
Dim xmlReq As ServerXMLHTTP
Dim postBody As String

postBody = "username=" & usrName & "&" & _
            "password=" & pwd & "&" & _
            "lt=" & sess.LtValue & _
            "&_eventId=submit&submit=Login"

Set xmlReq = New ServerXMLHTTP
xmlReq.Open "POST", sess.FormActionURL, , False
xmlReq.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E)"
xmlReq.setRequestHeader "Connection", "keep-alive"
xmlReq.setRequestHeader "Referer", "https://secure.ngx.com/sso/login?service=https%3A%2F%2Fsecure.ngx.com%3A443%2Fngxcs%2Fj_spring_cas_security_check%3Bjsessionid%3D" & sess.OldSessionID
xmlReq.setRequestHeader "Cookie", "JSESSIONID=" & sess.SessionID
xmlReq.setRequestHeader "Host", "secure.ngx.com/"
xmlReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
xmlReq.setRequestHeader "Content-Length", Len(postBody)
xmlReq.send postBody

If xmlReq.Status <> 200 Then MsgBox "Error occured: " & xmlReq.statusText: Exit Sub

servResp = xmlReq.responseText

Dim DataObj As New MSForms.DataObject
DataObj.SetText servResp
DataObj.PutInClipboard

End Sub

Don't forget to change username and password values in 'Test_login' sub.

Click to expand...