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

 

[poolhall]

Accessing and reading ATOM feed example, it's the same as for other XML feeds:

Sub ReadAtomXML()
Dim xmlReq As ServerXMLHTTP60
Dim xmlDoc As MSXML2.DOMDocument
Dim root As IXMLDOMElement, myNode As IXMLDOMElement
Dim i As Long, n As Long

Set xmlReq = New ServerXMLHTTP60
xmlReq.Open "GET", "http://allboxing.ru/atom.xml"
xmlReq.send

If xmlReq.Status <> 200 Then
    MsgBox xmlReq.statusText: Exit Sub
Else
    Set xmlDoc = New MSXML2.DOMDocument
    xmlDoc.LoadXML xmlReq.responseText
End If

If xmlDoc.parseError.ErrorCode <> 0 Then
    MsgBox xmlDoc.parseError.reason
Else
    Set root = xmlDoc.DocumentElement
End If

For i = 0 To root.getElementsByTagName("item").Length - 1
    Set myNode = root.getElementsByTagName("item").Item(i)
    For n = 0 To myNode.ChildNodes.Length - 1
        Debug.Print myNode.ChildNodes(n).Text
    Next n
Next i

End Sub

As for logging in to a secure website, it's hard to give a direction without looking at a particular website. I usually do this kind of task by using the same ServerXMLHTTP60 as you see above to send a POST request with username/email and password, then get a session ID from the server response; then attaching the session ID in ServerXMLHTTP60.setRequestHeader method to keep the session so that the web server won't redirect you back to the login page.

 

[arkusM]

Accessing and reading ATOM feed example, it's the same as for other XML feeds:

As for logging in to a secure website, it's hard to give a direction without looking at a particular website. I usually do this kind of task by using the same ServerXMLHTTP60 as you see above to send a POST request with username/email and password, then get a session ID from the server response; then attaching the session ID in ServerXMLHTTP60.setRequestHeader method to keep the session so that the web server won't redirect you back to the login page.

Wow, you make it sound so simple, but I am out of my depth here for sure.
This is the documentation that is provided not sure if it is helpful

[h=2]Authentication[/h]Web service requests must be made over HTTPS. Requests made to web services over HTTP will be redirected to HTTPS.
Web service requests are authenticated using HTTP Basic Authentication. Typical basic authentication interaction involves the server "challenging" any un-authenticated requests that do not include the "Authorization" header on the request. However, NGX web service requests that do not include a basic authentication "Authorization" header will not receive a challenge response. Instead such a request is redirected to a form based login page. This behaviour exists to force human users using a web browser to authenticate using form based authentication.

In order for your web service client to authenticate using basic authentication, the first HTTPS request must include the "Authorization" header specifying the account credentials to authenticate with. The header for a such a request would look like this:
GET /ngxcs/trade.xml HTTP/1.0 Host: secure.ngx.com Authorization: Basic QWxhZGRpbjpvcGVuIHNlc2FtZQ== </PRE>

Alternatively, a request can be made that forces the basic authentication challenge to occur by adding the request parameter "authType=basic". The header for a such a request would look like this:
GET /ngxcs/trade.xml?authType=basic HTTP/1.0 Host: secure.ngx.com </PRE>
This request would result in a basic authentication challenge that your web service client could then respond to.
[h=2]Atom Feeds[/h]
NGX also provides access to data through Atom feeds. To access the Atom feed for a resource list use the ".atom" extension in the url. For example, to access the Atom feed of trade data use the url: https://secure.ngx.com/ngxcs/trade.atom

Not sure if you can help me beyond this, but thanks what you have provided I appreciate it.
Thanks for your help

 

[poolhall]

Can you provide the webpage login URL?

 

[arkusM]

Can you provide the webpage login URL?

Sorry I meant to earlier.

https://secure.ngx.com

 

[poolhall]

Without login credentials, I cannot test it if this is successful, you please do it

This requires VBE references set to Microsoft XML, v6.0 and Microsoft Forms 2.0 Object Library.
Put the code into a standard module. In sub Test_login change "mypassword123" and "myusername123" to your actual login name and password and run "Test_login".
The last sub will put a server response (webpage source code) to the clipboard. Paste it into a text editor and see if the login attempt was successful. Please let me know

Dim SessionID As String, LtValue As String


Public Type Ngx_Session
    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/sso/login?service=https%3A%2F%2Fsecure.ngx.com%3A443%2Fngxcs%2Fj_spring_cas_security_check%3Bjsessionid%3D8A50ADC364C66CD4CF0CADAA82C79866"
xmlReq.setRequestHeader "User-Agent", "Excel 2002 :)"
xmlReq.setRequestHeader "Connection", "keep-alive"
xmlReq.setRequestHeader "Referer", "https://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)


End Sub


Private Sub Ngx_Login(sess As Ngx_Session, usrName As String, pwd As String)
Dim xmlReq As ServerXMLHTTP60
Dim postBody As String
Dim SessionID 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", "Excel 2002 :)"
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%3D8A50ADC364C66CD4CF0CADAA82C79866"
xmlReq.setRequestHeader "Cookie", "JSESSIONID=" & sess.SessionID
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

 

[arkusM]

Thanks for your effort. I'll have to try this opn Monday a this point. But I will definetly let you know, thank you so much..

 

[poolhall]

no problem

 

[arkusM]

Ok So things freeded up and I gave it a go..

I get this Run-time error message:
-2147012867 (80072efd) a connection with the server could not be established.
at this line:

 Private Sub Ngx_EstSession(sess As Ngx_Session)
    .....
    xmlReq.send

Could this be a internal network issue?

 

[poolhall]

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?

​