Excel and Atom Feeds

[arkusM]

Does anyone know how to access Atom data feeds through Excel? The data I am trying to get at is on a secure server, so I also need authentication, but I am having trouble finding examples through a google search.

Cheers

 

[arkusM]

I suppose, it is possible. Could a company firewall or, possibly, your antivirus software blocking this request? Check that, please.

Also make sure that the URL in

xmlReq.Open method was copied properly. Try to change it to ​

https://www.google.com/ , will this work?

I cannot seem to get to google either same error message.
Not sure that I wil get support from our IT group, they are reluctant modify any setting for stuff like this.
I tried to google the error but our corp filter will not allow me to view many of the results.

 

[poolhall]

That's very unfortunate.

I'm not sure if it's going to help, but please try this: add a reference to Microsoft WinHTTP Services 5.1. and change all instances of ServerXMLHTTP60 in the code to WinHttpRequest.

Have you tried it from home?

 

[arkusM]

That's very unfortunate.

I'm not sure if it's going to help, but please try this: add a reference to Microsoft WinHTTP Services 5.1. and change all instances of ServerXMLHTTP60 in the code to WinHttpRequest.

No Luck

I will try this at home, and see if I get the same issues.
No sure if it matters, I am on Win XP, running Office 2003.


Thank you again for all you effort.

 

[poolhall]

You're welcome. I tested it with Excel 2002/WinXP and Excel 2007/Win 7 64bit, it works (sending and receiving data from the server) on both systems.

 

[arkusM]

Ok, tried this in a DMZ here at work

Got this response in the Immediate window:

https://secure.ngx.com/sso/login;jsessionid=1A8231EDA25AB1FB1827677F0ED38A9C?service=https%3A%2F%2Fsecure.ngx.com%3A443%2Fngxcs%2Fj_spring_cas_security_check%3Bjsessionid%3D8A50ADC364C66CD4CF0CADAA82C79866
1A8231EDA25AB1FB1827677F0ED38A9C
_cD78AAD73-4455-E877-4B13-0F069E147E73_k21B5533F-3BEC-0658-06A2-B9FAC081EA68

So I am assuming that it is working.
What would be my next steps in getting data from the sources?
I apprecaite that you cannot see the data I am after but is there some generic methods I can play with?
Do I need to specifiy the data I am interested in the header? You may have realized that I am cmplete noob regarding xml. LOL

Cheers,

Mark

 

[poolhall]

This is a two step macro. 1st step retrieves those parameters needed for the 2nd step, when procedure submits them back to the server along with username and password and retrieves the next server response and then records it to a string variable named

servResp. Then macro copies servResp to the Windows clipboard. Please paste it to a text file. If the login attempt was successful, what you have in a text file would be the same as you log into the website in browser and display the source code of the webpage. In other words, please study the servResp content to see if the login was successful. If it was NOT, the servResp would be similar to something like this:

​

HTTP/1.1 302 Moved Temporarily
Date: Fri, 22 Jun 2012 16:20:06 GMT
Server: Apache/2.2.3 (Red Hat)
Expires: Fri, 22 Jun 2012 16:20:16 GMT
Cache-Control: no-cache
Cache-Control: public, max-age=10, s-maxage=0
Location: https://secure.ngx.com:443/sso/logi...e.ngx.com:443/sso/j_spring_cas_security_check
Content-Length: 0
X-Cnection: close
Content-Type: text/plain; charset=UTF-8
X-WA-Info: [S10206.C85026.A13710.RA13673.U0].[OT/plaintext.OG/documents].[P/0.0].[O/0.0].[EH0/0].[DH0/0].[C/0]

 

[arkusM]

What I get is the source of the Login page itself, it does not appear to seem to have logged in.
I have tried to change the .Open Url to go to the destination page, but same result.

 

[poolhall]

I will try to see tonight where there might be a mistake in my code.

 

[poolhall]

I made some changes. Please try the new 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 ServerXMLHTTP60
Dim servResp As String
Dim pos As Long


Set xmlReq = New ServerXMLHTTP60


xmlReq.Open "GET", "https://secure.ngx.com/ngxcs/home.html"
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 ServerXMLHTTP60
Dim postBody As String


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


Set xmlReq = New ServerXMLHTTP60
xmlReq.Open "GET", sess.FormActionURL
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", "Content-Type: application/x-www-form-urlencoded"
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

 

[poolhall]

I noticed a typo in the code

xmlReq.setRequestHeader "Content-Type", "Content-Type: application/x-www-form-urlencoded"

should be

xmlReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"

</pre>